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
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.