Hi guys,

I'm having troubles with transaction control.

I recently decided to add transaction control to some operations on an
app I am developing (Dreamweaver MX 2004, ASP, VBScript, ADO and
SQLServer 2000).

I started by doing a test web page with an update of up to 3 different
tables driven by simple pushbuttons, and more buttonds for BEGIN TRAN,
COMMIT and ROLLBACK.

Worked perfectly. It did exactly what I wanted, and even though I was
using separate record set variable for each of the commands and tables,
and closing my connection after every BEGIN TRAN.

So I decided to build that same kind of logic in my app, and test it. I
built in an error in a chain of inserts and updates I am doing, and I
put an error trap around each of the possible error-producing actions.
When an error occur, I check if a transaction has been started (which is
indicated by a flag set by my code when calling up the BEGIN TRAN), and
if yes, I rollback.

The results are very unimpressive: it doesn't work. The error that I
built in is to insert a duplicate row, but before that, I have inserted
proper rows. The code detects the error, executes the roll back, but the
rows inserted after the BEGIN TRAN and before the error are in the DB !
I.e. it looks like the BEGIN TRAN does not work. How can I check this,
and what could be the reasons that my test app works fine, and my real
app doesn't ? In the real app, the error is happening on a stored
procedure call, which is the one inserting the rows. Is the behaviour of
an SP different than a normal SQL statement sent over ADO ?

I have attached my test page.

Can you tell me:
- when should I issue the BEGIN TRAN ? Before the very first INSERT /
UPDATE, or can it be even before that (I do quite a bit of reading
(SELECT) before doing the first INSERT)
- on which connection
- should I have all the updating / inserting SQL statements going on one
and the same connection, or can I use different connections ?
- somebody told me there was a difference if I was using the
ActiveConnection object as opposed to just a connection object.
- I changed my routine that does the BEGIN / COMMIT / ROLLBACK to this
in my real app:

sub TransControl(action)
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_SDS_STRING
select case action
case "BEGIN"
sql = "BEGIN TRANSACTION"
Response.Write("Starting transaction <br>")
case "COMMIT"
sql = "COMMIT TRANSACTION"
Response.Write("Committing transaction <br>")
case "ROLLBACK"
sql = "ROLLBACK TRANSACTION"
Response.Write("Rolling back transaction <br>")
case else
end select
Set rs = conn.Execute(sql)
conn.Close
set conn = nothing
set rs = nothing
end sub

Does this make any difference to my DoIt routine in the test page ?

- and finally: I just noticed that I use the follwing 2 syntaxes:
conn.Open <connection string>
and also:
conn.Open = <connection string>
What's the right syntax ? Starngely, both seem to work

I am a bit lost, and would appreciate a lot if you could point me in the
right direction.

Thanks

[quoted text, click to view]

The transaction must always be on the same connection. You want a
transaction to run as quickly as possible, so issue if you are executing
multiple procedures and they must be a part of the same transaction,
issue the begin tran, execute the procedures, and then rollback or
commit the transaction.

For example:

create table tran_test( col1 int)
go

create proc dbo.tran_test_insert (@i int)
as
Insert into dbo.tran_test (col1) values (@i)
go

-- test 1
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Rollback
Select * from dbo.tran_test
go

-- test 2
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Commit Tran
Select * from dbo.tran_test
go

drop proc dbo.tran_test_insert
go

drop table dbo.tran_test
go



--
David Gugick
Imceda Software
www.imceda.com

Hi David,

Thanks for answer. I'm aware of the need to have quick transactions. But
I'm not working directly at the DB level, i.e. I'm not only using stored
procedures, I'm mostly building my SQL statements and executing them
through ADO. So your example is clear, but I can't apply it directly.
For one, there is no explicit connection in stored procedures. So that
makes it harder for me, as I am using the typical VB-like ASP ADO
commands. And Dreamweaver, when you let it generate all the data access
stuff, builds by default a new connection for every access, and closes
it after the access, plus it destroys the connection object. Although I
am not using Dreamweaver-generating capabilities, I have "inherited"
that usage concept of - constructing the access object; - accessing the
data; - closing the connection and destroying the access object, just to
keep memory clean.

And, what IS a connection ? Is it that which is established by saying:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open <connection string>
Or the alternative:
Set rs_T = Server.CreateObject("ADODB.Command")
rs.ActiveConnection = <connection string>
?
Is there any difference between the 2 possibilities ?
What's the difference between the "conn" connection object in the 1st
possibility and the "rs" command object (also called record set) in the
2nd possibility ?
All this is not clear to me, and I'd be VERY thankful for some answers.

Regards
Bernard

[quoted text, click to view]