I have below code, wherein I am getting error "String Data, right truncation" at line adoCmd.Execute. I have also table datatype as attached. Actually I am trying to move attachment from local drive to sql in binary format.
Can anyone please guide what is wrong here. the destination file is .PNG type. Attached is table design in sql 2014
Can anyone please guide what is wrong here. the destination file is .PNG type. Attached is table design in sql 2014
Code:
Sub CreateTableRecord(keyaudit As String, Attachmentfound As Boolean, vendorname As String, VndNO As String)
Dim adoStream As Variant
Dim adoCmd As Variant
Dim strFilePath As String
Dim adoCon As Variant
Dim para As Variant
Const adTypeBinary = 1
Const adCmdText = 1
' Const strDB As String = "SQLDB" 'Database name
' Const strServerName As String = "DESKTOP-LEJN6P1" 'Server Name
Dim strDB As String
Dim strServerName As String
strServerName = "DESKTOP-LEJN6P1"
strDB ="SQLDB"
Dim strDir As String
Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adoCmd = CreateObject("ADODB.Command")
Set para=CreateObject("ADODB.Parameter")
'--Open Connection to SQL server
adoCon.CursorLocation = 3
adoCon.open "SQLLocalConnection", "USer1", ""
'----
If Attachmentfound Then
strDir = "C:\files\AttachmentExtracted\"
Dim file As Variant
If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
file = Dir(strDir)
While (file <> "")
With adoCmd
'.ActiveConnection = adoCon
adoStream.Type = adTypeBinary
adoStream.Open
adoStream.LoadFromFile strDir + file
MsgBox strDir + file 'It fails if file is open
.CommandText = "INSERT INTO VendorFIles VALUES (?,?,?)" ' Query
.CommandType = adCmdText
'---adding parameters
.Parameters.Append .CreateParameter("UniqueKey", 200, 1, 20, keyaudit)
' Set uniquekeyval.Value = Keyaudit
'MsgBox adoStream.Size
'MsgBox adoStream.Read
.Parameters.Append .CreateParameter("FileName", 200, 1, 200, file)
.Parameters.Append .CreateParameter("VendorAtt", 204, 1, adoStream.Size, adoStream.Read)
'---
End With
Kill strDir + file
'do not change below this line
file = Dir
Wend
Set adoCmd.ActiveConnection = adoCon
call adoCmd.Execute
adoCon.Close
End If
Call updateVendorData(keyaudit, vendorname, VndNO)
end sub