When to do an ADO rollback

[Colin Steadman]

While scouting around this group, I've stumbled upon this ASP script
which deals with rolling back transactions -

'-- Error Handler
if oConn.Errors.Count > 0 then
oConn.RollbackTrans
Response.Write("FAIL")
Response.End
Response.write(err.description)
end if


Which has given me pause to wonder if what I am doing is correct. I'm
basing my rollback on the err.number not being 0 (sample code below).
Is this ok, or should I be using the conn.errors.count property
instead?

TIA,

Colin


If Not UpdateDealerPeopleInfoPending Then
conn.RollbackTrans
response.redirect("msg_employment_status_update.asp?error=2")
End If

Function UpdateDealerActionsSummited()

On Error Resume Next

sql = "random sql update statement"
conn.execute sql, , &H00000080
Set sql = Nothing

'Errors?
If err.number <> 0 Then
UpdateDealerActionsSummited = False
Else
UpdateDealerActionsSummited = True
End If

End Function

 

[Aaron Bertrand [MVP]]

Response.End

Response.write(err.description)

This response.write will never happen.

Which has given me pause to wonder if what I am doing is correct. I'm
basing my rollback on the err.number not being 0 (sample code below).
Is this ok, or should I be using the conn.errors.count property
instead?

Well, the err.number could be <> 0 for simple warnings that can't be
suppressed (e.g. if your procedure does an sp_rename). So in some cases,
this isn't enough...

However, if you are using SQL Server, it would be much more reliable to
handle your error errors and transactions within a stored procedure.

http://www.algonet.se/~sommar/error-handling-I.html
http://www.algonet.se/~sommar/error-handling-II.html