August 19, 2008 at 9:40 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 20, 2008 at 5:05 am
It looks like your parameter mapping in the call top the stored procedure is wrong and it is trying to put the text into a parameter defined as Int - can you post the stored procedure and the code you are using to call it?
If you are instantiating and ADODB.Command object, then appending parameters to the object, these are passed by position, and not by name:
Here is an example:
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "spUPCalendar"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@DateUpdated", adDate, adParamInput, , Format(Now(), "dd/mm/yyyy"))
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@UpdatedBy", adVarChar, adParamInput, 10, LoginID)
cmd.Parameters.Append prm
cmd.Execute
Set cmd = Nothing
Set prm = Nothing
CREATE PROCEDURE spUPCalendar
( @DateUpdated [datetime], @UpdatedBy [varchar](10))
AS UPDATE tblCalendar
SET
[DateUpdated] = @DateUpdated,
[UpdatedBy] = @UpdatedBy
GO
If you create the parameters in a different order, it doesn't matter what the name is - the first defined parameter object gets passed to the first parameter in the SP defiinition, and subsequent ones to the ones in the equivalent position - eg:
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "spUPCalendar"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@UpdatedBy", adVarChar, adParamInput, 10, LoginID)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@DateUpdated", adDate, adParamInput, , Format(Now(), "dd/mm/yyyy"))
cmd.Parameters.Append prm
cmd.Execute
Set cmd = Nothing
Set prm = Nothing
Here DateUpdated will be passed to the UpdatedBy parameter, and UpdatedBy will be passed to the DateUpdated parameter.
If you change your code to the following, it is passed by name, and not by position - the Parameters.Refresh brings back the parameters from the server and you can then refer to them by name, and you don't have to specify the data type etc :
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "spUPCalendar"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("@UpdatedBy")= LoginID
cmd.Parameters("@DateUpdated") = Format(Now(), "dd/mm/yyyy"))
cmd.Execute
Set cmd = Nothing
August 20, 2008 at 8:45 am
Many thanks for you reply.
I have just tracked down the source of the error - and it turned out to be another stored procedure, which was processing the string. The second sp uses CHARINDEX to parse the string - and it was failing as soon as the string was > 8000. (BOL doesn't appear to mention this limitation!)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply