VB, ADO, Refresh on Paramters fails after upgrade from ADO1.5c to ADO2.1

From: <alok_lal_at_hotmail.com>
Date: Tue, 07 Dec 1999 07:42:04 GMT
Message-ID: <82idoc$pei$1@nnrp1.deja.com>

The application I work on uses ADO 1.5c to access a bunch of stored procedures in an Oracle 7.3.4 database. We use "Microsoft ODBC for Oracle" ODBC driver which talks to Oracle using a SQL*Net alias. This ODBC driver was part of the MSDAC 1.5c distribution.

Now we are looking to upgrade to ADO 2.1.

However, I notice that the refresh call on parameters collection of a command object is failing. It no longer retrieves the provider side information about the stored procedure.

To illustrate the problem, here is a sample code that works with ADO 1.5c but does not work now:

' ---------------- start of code ----------------

    Dim myConnection As ADODB.Connection     Set myConnection = New ADODB.Connection     myConnection.ConnectionString = "DSN=tmc;UID=tmc;PWD=tmc"     myConnection.Open

    Dim myCommand As ADODB.Command
    Set myCommand = New ADODB.Command
    myCommand.ActiveConnection = myConnection

    myCommand.CommandText = "myStoredProc1" ' stored procedure name     myCommand.CommandType = adCmdStoredProc

    myCommand.Parameters.Refresh
    Dim parameterCount As Long
    parameterCount = myCommand.Parameters.Count

    MsgBox "Count of parameters is " & parameterCount

    Dim myParameter As ADODB.Parameter

    For Each myParameter In myCommand.Parameters

        MsgBox "First parameter is " & myParameter.Name & _
               "With a type of " & myParameter.Type

    Next myParameter

'----------------- end of code --------------

Earlier with ADO 1.5c the parameterCount used to have correct value but since we upgraded to ADO 2.1 the parameter count is always 0.

In the current ADO 2.1 documentation I find that : "Using the Refresh method on a Command object's Parameters collection retrieves provider-side parameter information for the stored procedure or parameterized query specified in the Command object. The collection will be empty for providers that do not support stored procedure calls or parameterized queries."

What could have changed on moving from ADO 1.5c to ADO 2.1 that the refresh has stopped retrieving the parameter info for a stored procedure from Oracle database? Is there any other way to get provider side stored procedure information?

Any help would be greatly appreciated.

Thanks for your time,

Alok.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 07 1999 - 01:42:04 CST