Microsoft KB Archive/240205

= PRB: ADO Parameters.Refresh Fails with MSDAORA Provider and Parameterized Query =

Article ID: 240205

Article Last Modified on 9/30/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0

-



This article was previously published under Q240205



SYMPTOMS
When you use ActiveX Data Objects (ADO), you can call the Parameters.Refresh method for a parameterized SELECT statement. When you are using the Microsoft OLE DB Provider for Oracle (MSDAORA), this may return the following error:

The Provider cannot derive parameter info and SetParameterInfo has not been called.

The HRESULT error code is DB_E_PARAMUNAVAILABLE (0x80040e51).



CAUSE
The Microsoft OLE DB Provider for Oracle does not derive parameter information.



RESOLUTION
If you need this functionality, use the OLE DB Provider for ODBC and the Microsoft Oracle ODBC driver.



Steps to Reproduce Behavior
 In Microsoft Visual Basic 6.0, create a new project. Add a reference to the ADO library.  Add two buttons to run the two routines provided below. One routine calls to the OLE DB Provider for Oracle, and the other calls to the ODBC driver by way of the ODBC OLE DB Provider. The OLE DB routine returns the error shown in the "Symptoms" section, and the ODBC routine works correctly. Private Sub cmdUseOLEDB_Click Dim cnDatabase As ADODB.Connection Dim qryParameterized As ADODB.Command Dim strConn As String Dim strSQL As String strConn = "Provider=MSDAORA;Data Source=myOracleSrv;" & _ "User ID=demo;Password=demo;" strSQL = "SELECT * FROM Customer WHERE CUSTID = ?" Set cnDatabase = New ADODB.Connection cnDatabase.Open strConn Set qryParameterized = New ADODB.Command Set qryParameterized.ActiveConnection = cnDatabase qryParameterized.CommandText = strSQL qryParameterized.CommandType = adCmdText qryParameterized.Parameters.Refresh MsgBox qryParameterized.Parameters.Count Set qryParameterized = Nothing cnDatabase.Close Set cnDatabase = Nothing End Sub

Private Sub cmdUseODBC_Click Dim cnDatabase As ADODB.Connection Dim qryParameterized As ADODB.Command Dim strConn As String Dim strSQL As String strConn = "Provider=MSDASQL;Driver={Microsoft ODBC for Oracle};" & _ "Server=myOracleSrv;UID=demo;PWD=demo;" strSQL = "SELECT * FROM Customer WHERE CUSTID = ?" Set cnDatabase = New ADODB.Connection cnDatabase.Open strConn Set qryParameterized = New ADODB.Command Set qryParameterized.ActiveConnection = cnDatabase qryParameterized.CommandText = strSQL qryParameterized.CommandType = adCmdText qryParameterized.Parameters.Refresh MsgBox qryParameterized.Parameters.Count Set qryParameterized = Nothing cnDatabase.Close Set cnDatabase = Nothing End Sub  Substitute your Oracle server database alias for the one that is used in the sample (Server=myOracleSrv and Data Source = myOracleSrv).

Keywords: kboracle kbprovider kbdatabase kbprb KB240205

-

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

© Microsoft Corporation. All rights reserved.