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 NOT NULL,
[FileName] nvarchar NULL,
[VendorAtt] varbinary NULL,
CONSTRAINT [PK_VendorFIles] PRIMARY KEY CLUSTERED