ado, asp and transaction problems

[cglisc]

Hello,

I have the following code on my asp page:
(Assume ADOCONN to be an ADO connection to an
SQL Server 7 database)
<%
ADOCONN.execute (&quot;BEGIN TRAN&quot
...some update statements here

ADOCONN.execute (&quot;COMMIT TRAN&quot
%>

The above code fails on the line where I commit.
The message says thar there is no corresponding BEGIN TRAN
statements. It is as if each ADOCONN.execute is executed
in its own SQL session (hence it cannot see the previous
BEGIN TRAN). Am i correct in this assumption? And also,
what is the work around, if any?

Thanks,
Chris.

 

[ehsant]

Hi There ..
as you have noticed yes,each ADOCONN.execute is executed
in its own SQL session. so you have two ways:
1- write all the Begin Trans and UPDATES And Commit Trans
In One string and then Execute it,
2- Use Stored Procedure.
thats's It
TNX
E.T.

 

[denoxis]

I think your assumption is true. Did you try to send the whole thing in a string that starting with BEGIN TRANS and ending with COMMIT TRANS?

 

[cglisc]

Yes, I tried construting a string and submit it as
a batch and it worked. This is however a puzzling
behavior.

Another problem with ADO transactions:
When you submit an update/insert statement, ADO
seems to start a transaction in the database. If
a programmer is not aware of this (I wasn't ),
s/he can lock up the database.

Oh well....

 

[Funka]

look into using stored procedures -- there are several benefits to doing this, the very least is being able to control your transaction more tightly.