Adding SQL Server transactional queries to an old ASP app

[xangelusx]

I wrote an app several years ago that has only just begun intermittently failing on a particular stage in a series of sql queries. When I wrote it I wasn't familiar with transactions and so didn't bother implimenting them. Since then I've moved from ASP to PHP and have done several applications that used the ADOdb library's transactional features. Now going back to ASP I'm having a hard time finding any information on how to actually impliment a transaction from an ASP page for a set of queries that are not part of any stored procedures.

For instance, how am I to know when an error occurs and that I need to roll back? Do I need to set OnErrorGoTo = 0, then check for an error at the end of the sequence and roll back if one occurred?

If anyone could post a small example of ASP code that includes a few queries executed with an adodb connection object, wrapped in a SQL transaction, that would be most beneficial.

I've searched the Internerd a few times and, surprisingly, have not found a good example of this otherwise...

Thanks!
Chris Bloom

PS: The app is written in classic ASP, not that new fangled .NET stuff.

[jimfraser]

Very simply:

On Error Resume Next

Set db = Server.CreateObject("ADODB.Connection")
db.Open myConnectString

bAllOK = True
db.BeginTrans

db.Execute "INSERT INTO someTable(someField) VALUES('thing')"
If Err <> 0 Then
	bAllOK = False
End If

If bAllOK Then
	db.Execute "UPDATE someTable SET someField='anotherThing' WHERE Id=1"
	If Err <> 0 Then
		bAllOK = False
	End If
End If

If bAllOK Then
	db.CommitTrans
Else
	db.RollbackTrans
End If

[xangelusx]

Thanks so much! That was just what I needed. I just finished testing the new transactional support in my DB class and everything appears to be working great