Rollback my transaction

[charon]

hi,
I know that in ASP, there has "Transaction" which allow us to rollback our transaction, I'm here would like to know if in this case, how could I start my transaction and end.

Connecttio database has been establish
cSQL = "Select * from mydata"
Set objRS = RsOpen(cSQL, objDC)
objRS.AddNew
add data
objRS.Update
cSQL2 = "Select * from myjob where Job = "Clerk"
Set objRS = RsOpen(cSQL, objDC2)
objRS.AddNew
add data
objRS.Update

How could I rollback both transaction when the first SQL is not succeeds or visa versa.

Please help

[raf]

Absolutely no expert in transactions. Hardly use/know them (most often, i just check for number of updated, inserted of deleted records) but i read one of your previous posts and i've done some looking around cause i'm currently writing an extension to an existing app (comparing prices for an order if some features of the workpieces are changes and sometimes updating the existing order wwith the values from a new alternative), which involves copying record to a table, updating them with new feachers and then computing the price and sometimes replacing the original features with new ones. I've got my regular checks and if the final check isn't succesful, the complete alternatief is removed from the table. Buth i'm concidering using a multi-page transaction to break of the proces and roll back in case of a db-error. (since, when the final update is unsuccesfull, the original features must be restored)

Anyway, this holds the answers you need.

http://www.asp101.com/articles/chris/asptransactions/default.asp

But i wouldn't over-use transaction. It has to have an inpact on performance + it only does anything when it encounters an error. I think most coders cause problems because there sql-statement contain errors (so no action is performed) or there conditions contain problems (so no records, or to many records are altered) but this doesn't cause a rollback.

[kalijunfan]

I was about to explain how to make transactions in asp when i found one GREAT tutorial on that issue, and it even teaches how to use the Errors collection of the ADODB.Connection object.
This is definitely a must read for any asp developer out there, you can access it here:

Executing All or None queries : ASP Transactions (http://www.stardeveloper.com/articles/display.html?article=2000082801&page=1)

I hope it is usefull for all of you.

Bye

[charon]

Originally posted by charon
ya, kalijuntan,

Thank for ur recommendation, I like the article very much.....
ya, I found that the database transaction not only allow us to track the transaction for connection's SQL command (con.Execute (SQL) but, also work in Recordset's method.
For instance: I use Con.Execute to Update the first transaction


Con.BeginTrans
Con.Execute "Update [Names] Set Name = 'Janny' Where ID = 58", RecordsEffected

and I use Recordset.Update for execute the second transaction
SQL = "SELECT * FROM Job WHERE ID = '123'"
Rs.Open SQL, Con
If Not Rs.EOF Then
Rs("Status") = "Executive"
Rs.Update
End If
If Con.Errors.Count <> 0 Then
Con.RollbackTrans
Else
Con.CommitTrans
End if

1.) If the first transaction has error, the second transaction will rollback as well, and visa versa
2.) Does it mean that no matter what method we are using (connection/Recordset), as long as we use the BeginTrans and CommitTrans/Rollback, any transaction (may be 4 transactions which are related) which we define within the BeginTrans and rollback/commitTrans BLOCK, will either succeed or fail as "DO ALL or UNDO ALL" CORRECT????????

3.)I found that the Database transaction not work for SELECT statement. For instance:

con.Execute "Update Costum Set NumOfColor = NumOfColor + 1"

Set Rs = Con. Execute "SELECT * FROM Shirt WHERE Color = 'blue'
SQL = "SELECT * FROM Dresst Where Color='blue' " for second transaction, but with error as DressT is not a valid table
Rs.Open SQL, Con
If Not Rs.EOF Then
Rs2.Open SQL, Con
Rs("Status") = "Executive"
Rs.Update second transaction
End If
If Con.Errors.Count <> 0 Then
Con.RollbackTrans
Else
Con.CommitTrans
End if


ok, from these SQL statement, just assume that When the first transaction is executed, the second transaction must be executed as well, else rollback all. But from my example above, i found that the first transaction is executed, but the second transaction are not .

I want to Confirm, the SELECT statement (SELECT QUERY) will not affect the transaction right and HOW TO SOLVE IT????

[charon]

hi,

is there anybody can help on this problem??

[Roelf]

why does a select statement need a rollback, nothing has changed.
Transactions only undo changes executed since the beginning of the transaction when the rollback method is called.
A select statement only creates a recordset (or not) but the database is not altered. So a recordset created is not destroyed with the rollback

as far as i know