Using BeginTrans, Commit Trans 
Author Message
 Using BeginTrans, Commit Trans

Hello group,

I have a VB 6 application running against a SQL Server 2000 database. At the
beginning of the application, I open a connection to the database globally
and use that connection throughout the application. In one program, I have
to do updates to several tables for the entry of an order and creation of
accounting transactions. I want to be sure that this entire group of
transactions go into the database, so I am using a BeginTrans on the
connection before starting the updates, and then doing a CommitTrans when I
am finished. Whenever I try to run this, I get all sorts of strange errors
such as "too many transactions" or "connection is in a zombie state" (no
kidding, it really says "zombie state").

I am doing plain old updates to the affected recordsets using the .addnew
method and it works fine if I take out the begintrans, endtrans statements.

Does anyone have a clue what I might be doing wrong?

Thanks,
Kevin McElhiney



Mon, 10 Nov 2003 00:06:57 GMT  
 Using BeginTrans, Commit Trans

Quote:

> I am doing plain old updates to the affected recordsets using the .addnew
> method and it works fine if I take out the begintrans, endtrans statements.

Maybe SQL {*filter*} 2000 wants to see the Recordsets opened and closed either
inside or outside the transaction? In other words, it might barf on one or
the other of these:

ws.begintrans
set dst = db.openrecordset("select balance from account where id = 13")
set src = db.openrecordset("select balance from account where id = 69")
dst!balance = dst!balance + 666
src!balance = src!balance - 666
dst.update
src.update
dst.close: set dst = nothing
src.close: set src = nothing
ws.committrans

set dst = db.openrecordset("select balance from account where id = 13")
set src = db.openrecordset("select balance from account where id = 69")
ws.begintrans
dst!balance = dst!balance + 666
src!balance = src!balance - 666
dst.update
src.update
ws.committrans
dst.close: set dst = nothing
src.close: set src = nothing

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!



Mon, 10 Nov 2003 03:24:56 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. begin trans - commit trans

2. BEGIN,COMMIT Trans

3. Begin Trans..Commit with Client Access ?

4. ADO err with trans begin/commit ?

5. Begin and commit trans

6. Begintrans-Commit with SQL-Server7??

7. VB4 - Commit or Rollback without BeginTrans. (Error 3034)

8. BeginTrans Commit RollBack

9. BeginTrans, Commit, RollBack

10. Commit or Rollback without BeginTrans Error

11. Cost of Begintrans/Commit

12. VB4 - Commit or Rollback without BeginTrans. (Error 3034)

 

 
Powered by phpBB® Forum Software