August 19, 2008 at 9:50 am
I am trying to pass a long text string in ado to a text parameter in a usp.
When executing the command object, regardless of what ado data type I use, I get the same error 'Syntax error converting the varchar value' followed by all characters in the string - over the 8000th 'to a data type of int'.
The use of 'varchar' in the error message suggests that, no matter what ado data type and size is used, the string is being treated as a varchar - hence the 8000 limit?
A solution would be great, but an explanation (if there is no solution) would be almost as good!
August 19, 2008 at 12:25 pm
Might I suggest looking at Books On Line (BOL) subject:
ADO and SQL Server
Managing Long Data Types.
Rather a good discussion / howto guide, includes creating a sample table based on the Pubs database as well as sample code.
Basically the data must be read/written in chunks
One example of ADO code to do this is:
Public Sub AppendChunkX()
Dim cn As ADODB.Connection
Dim rstPubInfo As ADODB.Recordset
Dim strCn As String
Dim strPubID As String
Dim strPRInfo As String
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varLogo As Variant
Dim varChunk As Variant
Const conChunkSize = 100
' Open a connection.
Set cn = New ADODB.Connection
strCn = "Server=srv;Database=pubs;Trusted_Connection=yes;"
cn.Provider = "sqloledb"
cn.Open strCn
'Open the pub_info_x table.
Set rstPubInfo = New ADODB.Recordset
rstPubInfo.CursorType = adOpenDynamic
rstPubInfo.LockType = adLockOptimistic
rstPubInfo.Open "pub_info_x", cn, , , adCmdTable
'Prompt for a logo to copy.
strMsg = "Available logos are : " & vbCr & vbCr
Do While Not rstPubInfo.EOF
strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
Left(rstPubInfo!pr_info,
InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr
rstPubInfo.MoveNext
Loop
strMsg = strMsg & "Enter the ID of a logo to copy:"
strPubID = InputBox(strMsg)
' Copy the logo to a variable in chunks.
rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
lngLogoSize = rstPubInfo!logo.ActualSize
Do While lngOffset < lngLogoSize
varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
varLogo = varLogo & varChunk
lngOffset = lngOffset + conChunkSize
Loop
' Get data from the user.
strPubID = Trim(InputBox("Enter a new pub ID:"))
strPRInfo = Trim(InputBox("Enter descriptive text:"))
' Add a new record, copying the logo in chunks.
rstPubInfo.AddNew
rstPubInfo!pub_id = strPubID
rstPubInfo!pr_info = strPRInfo
lngOffset = 0 ' Reset offset.
Do While lngOffset < lngLogoSize
varChunk = LeftB(RightB(varLogo, lngLogoSize - _
lngOffset),conChunkSize)
rstPubInfo!logo.AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop
rstPubInfo.Update
' Show the newly added data.
MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
"Description: " & rstPubInfo!pr_info & vbCr & _
"Logo size: " & rstPubInfo!logo.ActualSize
rstPubInfo.Close
cn.Close
Hope this points you in the right direction.
August 20, 2008 at 4:21 am
Noted. Will investigate.
Many thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply