BUG: ADO Parameters Refresh May Cause Failure

• ActiveX Data Objects (ADO), versions 1.0, 1.5

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.

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 1.5 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 the error is generated. If you uncomment the code specifying the direction, the code executes correctly.

Additional query words:

Keywords          : kbADO kbADO100bug kbADO150bug 
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: May 24, 1999