PRB: ODBC Syntax Required for Retrieving Parameter Information

CAUSE

Executing the following Visual Basic code on the stored procedure below produces the following error: The error occurs because the type of the parameter retrieved by the Refresh method is adChar instead of adLongVarchar.

The wording of the error may vary slightly with newer versions of Microsoft Data Access Components.

Visual Basic Code

Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

   Dim cmd As New ADODB.Command
   Dim rs As New ADODB.Recordset   
   Dim param1 As Parameter
   Dim strValue As String
   cmd.ActiveConnection = "DSN=SQLServer;Username=<username>;PWD=<strong password>;Database=pubs"
   cmd.CommandText = "sp_procedure1"
   cmd.CommandType = adCmdStoredProc

   ' Calling Refresh method to retrieve the parameters information.
   cmd.Parameters.Refresh

   ' Setting the value of the parameter to a string > 255 characters.
   strValue = String(260, "A")
   ' Set the parameter value to be passed.
   cmd.Parameters(1).Value = strValue
   Set RS = cmd.Execute
				

SQL Server Stored Procedure

   CREATE TABLE Table1(field1 Text)

   CREATE PROC sp_procedure1
   @param1 TEXT
   as
   INSERT INTO Table1 VALUES(@param1)
				

   Dim cmd As New ADODB.Command
   Dim rs As New ADODB.Recordset   
   Dim param1 As Parameter
   Dim strValue As String

   cmd.ActiveConnection = "DSN=SQLServer;Username=<username>;PWD=<strong password>;Database=Pubs"
   cmd.CommandText = "{call sp_procedure1 (?)}"
   cmd.CommandType = adCmdText

   ' Calling Refresh method to retrieve the parameters information.
   cmd.Parameters.Refresh

   strValue = String(260, "A")
   ' Set the parameter value to be passed.
   cmd.Parameters(0).Value = strValue
   Set RS = cmd.Execute