Microsoft KB Archive/183008

= BUG: ADO Parameters Refresh May Cause Failure =

Article ID: 183008

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Data Access Components 2.8

-



This article was previously published under Q183008



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 that this is a bug in the Microsoft products that are 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
In MDAC 2.7, the parameters direction is still incorrect but the code will work.

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

Steps to Reproduce Behavior
 Create a Visual Basic Project. From the Projects menu, click References and select the ADO Type Library.  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:

Note You must change Username= and PWD= 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 con As New Connection Dim cmd As New Command

con.Open "DSN=YourDSN;database=YourDatabase;Username= ;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 (adParamOutput, 2), ' when in fact it is return (adParamReturnValue, 4). ' Parameter 1's direction is correctly ' set to be an input parameter (1). Even so, this code executes ' as is at this point. Msgbox "Cmd(0).Direction: " & cmd(0).Direction Msgbox "Cmd(1).Direction: " & cmd(1).Direction

' 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   '< Run the code and note that the error is generated. If you uncomment the code specifying the direction, the code executes correctly.

Keywords: kbbug kbfix kbmdac270fix kbstoredproc kbado270fix kbdatabase KB183008

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.