ADO/ODBC VB

Hiya

I have a problem with ADO, which works fine using ODBC

I am connected to an SqlServer2000 database and execute a stored procedure of which the primary function is to insert a record into a table, but there are several audit trail inserts too.

The stored procedure has a final argument of @ReturnResults int, which is set to 1, and if set, it selects the data to return to the caller.

When I execute this in Query analyser, I get the results I would expect, ie, 1 recordset with 1 row. When I execute this through VB and use ODBC, I get the results I expect. However, when I use ADO, I get a recordset back but the recordset is closed and so when I try to access it I get the error "Operation is not allowed when the object is closed".

I really want to use ADO, for the speed.

My code is here:

'Open the connection -- ADO
oConn.Open "Provider=SQLOLEDB.1;Password=********;Persist Security Info=True;User ID=*******;Initial Catalog=MyDB;Data Source=DS;Trusted_Connection=No"

'OPen the connection -- ODBC
oConn.Open "DSN=Test;Uid=******;pwd=********"

Execute the procedure

SQL string = "Exec stp_f_PurchaseOrdersFromSale 555567,0,1"

(The last argument indicates return to me a recordset of the new PurchaseOrder)

The execute code is:

Set O_CMD = New ADODB.Command
Set O_CMD.ActiveConnection = oConn
O_CMD.CommandType = adCmdText
O_CMD.CommandText = SQLStr
O_CMD.CommandTimeout = 0
Set o_RS = O_CMD.Execute


If I use ODBC and then say ? o_rs.recordcount, it returns 1
if I use ADO and then say ? o_rs.recordcount, it returns the error


I have a feeling the problem is my connection properties for the ADO connection.

Any help appreciated.





Comments

  • : Hiya
    :
    : I have a problem with ADO, which works fine using ODBC
    :
    : I am connected to an SqlServer2000 database and execute a stored
    : procedure of which the primary function is to insert a record into a
    : table, but there are several audit trail inserts too.
    :
    : The stored procedure has a final argument of @ReturnResults int,
    : which is set to 1, and if set, it selects the data to return to the
    : caller.
    :
    : When I execute this in Query analyser, I get the results I would
    : expect, ie, 1 recordset with 1 row. When I execute this through VB
    : and use ODBC, I get the results I expect. However, when I use ADO, I
    : get a recordset back but the recordset is closed and so when I try
    : to access it I get the error "Operation is not allowed when the
    : object is closed".
    :
    : I really want to use ADO, for the speed.
    :
    : My code is here:
    :
    : 'Open the connection -- ADO
    : oConn.Open "Provider=SQLOLEDB.1;Password=********;Persist Security
    : Info=True;User ID=*******;Initial Catalog=MyDB;Data
    : Source=DS;Trusted_Connection=No"
    :
    : 'OPen the connection -- ODBC
    : oConn.Open "DSN=Test;Uid=******;pwd=********"
    :
    : Execute the procedure
    :
    : SQL string = "Exec stp_f_PurchaseOrdersFromSale 555567,0,1"
    :
    : (The last argument indicates return to me a recordset of the new
    : PurchaseOrder)
    :
    : The execute code is:
    :
    : Set O_CMD = New ADODB.Command
    : Set O_CMD.ActiveConnection = oConn
    : O_CMD.CommandType = adCmdText
    : O_CMD.CommandText = SQLStr
    : O_CMD.CommandTimeout = 0
    : Set o_RS = O_CMD.Execute
    :
    :
    : If I use ODBC and then say ? o_rs.recordcount, it returns 1
    : if I use ADO and then say ? o_rs.recordcount, it returns the error
    :
    :
    : I have a feeling the problem is my connection properties for the ADO
    : connection.
    :
    : Any help appreciated.
    :
    :
    :
    :
    :
    :


    I have solve it..

    I split the connection string out of the OPEN function and put it into the connection string property (leaving the open statement with no arguments)

    Then, after opening, set the defaultdatabase Property

    Dunnow why it works by setting the connection string outside of the open statment, but it does and I am not going to lose any sleep over it...
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion