Rollback my transaction

[charonlee]

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!

[daisho]

Transactions are not a feature of ASP but rather a feature of your DBMS.

Confirm that you DB has transaction support since your calls will quitely fail if you don't to allow for compatibility with all DBMS'

The transaction order you will want is:

BEGIN TRANS
SQL1
SQL2

IF [BAD] THEN ROLLBACK ELSE COMMIT

Obviously this isn't working code but that's the type of order you will need. You can have many SQLs as part of a transaction but no DBMS that I know of will let you NEST transactions nore can they rollback once commited.

daisho

[charonlee]

I use access database.
1.)I use ODBC instead if OLEDB, and I failed to create procedure, does it because ODBC can't support this feature?

2.) The beginTrans seems like only work in ODBC.

Pls advice

[daisho]

What DBMS are you using?

daisho

[charonlee]

as I mentioned, I use microsoft access.

[charonlee]

hi,
I just wondering why my error message won't be displayed, instead i get error from server:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Microsoft Access Driver] Could not find output table 'deatilList.

Below is my code:

cSQL = "CREATE TABLE MyData (Name VARCHAR(20), Sex VARCHAR(20), Application VARCHAR(20), Status VARCHAR(20))"
cSQL2= "INSERT INTO detailList (DID, JobID, CourseID) VALUES ('" & DID & "','" & JOBID & "','" & COURSEID & "')"
objDC.BeginTrans
objDC.Execute(cSQL)
objDC.Execute(cSQL2)
If objDC.Errors.Count > 0 Then
objDC.RollbackTrans
For each myError in myError.Errors
If myError.Number <> 0 Then
eStr = "<table border=1 width=""90%"" align=""center""" & _
" bordercolor=""#E2EAEE""" & _
" style=""font-family:verdana; font-size:8pt;"" cellpadding=3>" & _
"<tr><td width=100>Error Property</td><td>Contents</td>" & _
"</tr><tr><td>Number</td><td>" & myError.Number & _
"</td></tr><tr><td>Native Error</td><td>" & _
myError.NativeError & "</td></tr>" & _
"<tr><td>SQLState</td><td>" & myError.SQLState & _
"</td></tr><tr><td>Source</td><td>" & _
myError.Source & "</td></tr>" & _
"<tr><td valign=""top"">Description</td><td>" & _
myError.Description & "</td></tr>" & _
"<tr><td>Page</td><td>" & Request.ServerVariables("SCRIPT_NAME") & _
"</td></tr><tr><td>Date & Time</td><td>" & _
FormatDateTime(Date, 1) & " " & Time & _
"</td></tr></table><br>"
End If
Next

Else
objDC.CommitTrans
msg = "The table for " tablename & " has been created"
End If

I purposely rename the detailList table to DetailList2, generate error, force my error message to display. But cannot, instead error from server as mentioned aboved.

Pls advice!

[psacake]

<Quote>Transactions are not a feature of ASP but rather a feature of your DBMS. </Quote>

Uh.. ASP Indeed supports transactions.

Check [asp101.com...] for a good tutorial.

[daisho]

Interesting. Though this seems something different the DBMS transactions.

daisho.

[charonlee]

I read this article before, but the ASP transaction is different with the database transaction.
what I want now is i want to display a feasable error message whenever there has connection error.