I have below code, wherein I am getting error “String Data, right truncation” at line adoCmd.Execute. I have also table datatype as end of the paragraph here in this message
Can anyone please guide what is wrong here.
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:\Lotus Notes work\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
“table code in sql query editor” ( the table is already been created, just for your reference I copied it for datatype and length reference.
CREATE TABLE [dbo].[VendorFIles](
[UniqueKey] [nvarchar](20) NOT NULL,
[FileName] [nvarchar](200) NULL,
[VendorAtt] [varbinary](max) NULL,
CONSTRAINT [PK_VendorFIles] PRIMARY KEY CLUSTERED