Getting ADO Error - String Truncation
The following code is working for me...
-- SQL Table, SP
CREATE TABLE [dbo].[photo] (
[image_id] [float] NOT NULL ,
[veh_id] [float] NOT NULL ,
[comment] [varchar] (50) NULL ,
[photo] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE PROCEDURE writePhoto
AS
INSERT INTO photo (image_id, veh_id, comment, photo)
' VB CODE
Public Function writePhoto(imageID As Long, _
vehID As Double, _
notes As String, _
imageSource As String) As Long
'Declare err variable and objects
Dim adoErr As ADODB.Error
Dim cmdWritePhoto As ADODB.Command
Dim param As ADODB.Parameter
Dim lngRVal As Long
Dim intFile As Integer
Dim ImgBuff() As Byte
Dim ImgLen As Long
' Declare and instantiate object variables
On Error GoTo Err_Handler
Set cmdWritePhoto = New ADODB.Command
lngRVal = 0
'Set Command object Properties
With cmdWritePhoto
.CommandType = adCmdStoredProc
.CommandText = "writePhoto"
.ActiveConnection = adoConn
'Set parameters for stored proc call
Set param = .CreateParameter("image_id", adDouble,
adParamInput, 0, imageID)
.Parameters.Append param
Set param = .CreateParameter("veh_id", adDouble, adParamInput,
0, vehID)
.Parameters.Append param
Set param = .CreateParameter("comment", adVarChar,
adParamInput, 50, notes)
.Parameters.Append param
'Read/Store GIF file in ByteArray
intFile = FreeFile
Open imageSource For Binary As #intFile
ImgLen = LOF(intFile)
ReDim ImgBuff(ImgLen) As Byte
Get #intFile, , ImgBuff()
Close #intFile
Set param = .CreateParameter("photo", adLongVarBinary,
adParamInput, (ImgLen + 1))
.Parameters.Append param
'Set the Value of the parameter with the AppendChunk method.
.Parameters("photo").AppendChunk ImgBuff()
.Execute
End With
'Exit Function
GoTo exit_Function
Err_Handler:
' Error handling here
exit_Function:
Set cmdWritePhoto = Nothing
Set param = Nothing
writePhoto = lngRVal
End Function
Quote:
> > The length of a text column is 16. Is that long enough?
> > > Hiya,
> > > I have a command (stored procedure - MS SQL Server 7) which has a
> > > parameter which writes to a TEXT (long data, like memo) field, and
> > > uses the Appendchunk method to append all the data. My code
> > > calculates the exact data length,and appends that. But when I say
> > > command.Execute I get the following error:
> > > Number = -2147217887
> > > Description = [Microsoft][ODBC SQL Server Driver]String data, right
> > > truncation
> > > Source = Microsoft OLE DB Provider for ODBC Drivers
> > > My Getchunk does not cause any problems, but that actually uses a
> > > recordset object, not a command.
> > > Anyone can advise as to what's wrong?
> > > Thanks,
> > > Sarah
> Hiya!
> The length of the Text Pointer is 16, the field is actually variable
> length up to 2GB. Nope, that ain't the problem. Anything else?
> Thanks,
> Sarah
