PRB: ODBC Syntax Required for Retrieving Parameter Information

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q183059

SYMPTOMS

The following information only applies to the OLEDB Provider for ODBC:

When using the Refresh method on a Command object's Parameters Collection for retrieving provider-side parameter information of a stored procedure, parameter information may not be correct. For example, if a stored procedure is specified in the CommandText property along with a parameter, and the parameter type is a SQL Server Text type, you will not get the correct ActiveX Data Objects (ADO) type returned for that parameter.

Back to the top

CAUSE

Executing the following Visual Basic code on the stored procedure below produces the following error:

[Microsoft][ODBC SQL Server Driver]String data, right truncation

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.

Back to the top

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

Back to the top

SQL Server Stored Procedure

   CREATE TABLE Table1(field1 Text)

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

Back to the top

RESOLUTION

Executing the same procedure with the following code resolves the problem. Note here that the CommandText uses the ODBC syntax for calling stored procedures. The CommandType is set to adCommandText.

Back to the top

Code Example

   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

Back to the top

STATUS

This problem applies to the versions of MDAC listed above in the applies to section. This problem does not occur in the 2.5 and later versions of MDAC.

Back to the top

REFERENCES

For additional informationabout refreshing ADO parameters for stored procedures, click the following article number to view the article in the Microsoft Knowledge Base:

174223 (http://support.microsoft.com/kb/174223/ ) HOWTO: Refresh ADO Parameters Collection for a Stored Procedure

Back to the top

APPLIES TO

Microsoft ActiveX Data Objects 1.5
Microsoft ActiveX Data Objects 2.0
Microsoft ActiveX Data Objects 2.1

Back to the top

kbarttypeinf kbdatabase kbmdac250fix kbmdacnosweep kbprb kbstoredproc KB183059

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top