BUG: ADO Parameters Refresh May Cause Failure

• ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1 SP2

SYMPTOMS

Invoking Parameters.Refresh may incorrectly return the wrong direction value for some parameters in the Parameters collection. When attempting to execute the Command Object for a parameterized query you may encounter thefollowing error message:

0x80040005 (or -2147467259). Unable to determine parameter type for at least one variant parameter.

CAUSE

This is due to a bug in ActiveX Data Objects (ADO) 1.x.

RESOLUTION

In the case shown in the MORE INFORMATION section, explicitly setting the direction of the parameter object will correct the problem.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

This error will not occur when using ADO 2.x. The command object executes the stored procedure without error. However, the cmd(0).Direction is still returned incorrectly as adParamOutPut(2) and not adParamReturnValue(4).

MORE INFORMATION

The sample code in step 3, while written for Visual Basic Applications (VBA), applies to ADO in any language/environment (VBScript, VBA, C++, Java, and so forth).

Steps to Reproduce Behavior

1. Create a Visual Basic Project.



2. From the Projects menu, click References and select the ADO Type Library.



3. Place the following code in the form's load event. This example assumes that the backend database is SQL Server. You will have to change the data source name (DSN) to one available on your computer:




   Dim con As New Connection
   Dim cmd As New Command

   con.Open "DSN=YourDSN;database=YourDatabase;uid=sa;pwd=;"

   ' Drop stored procedure, but ignore error if it doesn't exist.
   On Error Resume Next
   con.Execute "drop procedure proctest"

   ' Restore error handling.
   On Error GoTo 0

   ' Create stored procedure that has return and input parameter.
   con.Execute "create procedure proctest(@in text) as return 1"
   Set cmd.ActiveConnection = con
   cmd.CommandText = "{? = call proctest(?)}"

   cmd.Parameters.Refresh

   ' At this point parameter 0's direction is indicated to be Output,
   ' when in fact it is return.  Parameter 1's direction is correctly
   ' set to be an input parameter.  Even so, this code executes
   ' as is at this point.

   ' Uncommenting this line will make the test work fine.
   ' cmd(0).Direction = adParamReturnValue
   cmd(1).Type = adLongVarChar     ' Bug only occurs with this line
   cmd(1).Value = "Some String"
   cmd.Execute    '<<error here 

4. Run the code and note that the error is generated. If you uncomment the code specifying the direction, the code executes correctly.

Additional query words: