Hi, I have a little question regarding
SQLServer2000 vs ADO of Visual Basic 6
In a code snipplet a have something like:
Set rec_tmp = Conn1.Execute("SELECT * FROM IDENTIFICATIONS;")
Conn1.Execute "DELETE FROM CARS WHERE CAR_ID = 8;"
and whe VB tries to execute the line with the
DELETE sentence the Err object says something like:
"cannot create a new connection because you are in
distributed or manual transaction mode"
However, if I remove the line that executes the sentence
SELECT * FROM IDENTIFICATIONS the error never shows up.
Seems like I can´t execute a SELECT before a DELETE/UPDATE
even if I execute the SELECT in some other table.
I´ve tried changing the IsolationLevel, Mode and Cursor
Location properties and the error always occurs.
Furthermore, I tried putting the SELECT out of the
transaction and the error continues showing up.
Why does this occurs? Is there a way to solve it?
NOTE: I need forcely to execute that SELECT before the DELETE.
The problem is that the command object that contains the SELECT statement is still active when you attempt to re-use its connection object for the DELETE. If you change your ADO cursortype to ADStatic, you should be fine.