Steps to Reproduce Behavior

1. In Microsoft Visual Basic 6.0, create a new project.
2. Add a reference to the ADO library.
3. 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
   					

4. Substitute your Oracle server database alias for the one that is used in the sample (Server=myOracleSrv and Data Source = myOracleSrv).

Back to the top