Executing All or None queries : ASP Transactions

Explanation
Although the code that I provided in the last page looks a lot, but if you take a closer look it is very simple and most of the things you will already be able to understand.

After opening the connection to the database, we execute the Connection.BeginTrans method to start the transaction.

Then we execute two SQL queries to enter two names in the database. Note that the database field 'name' does not allow two identical names.

Next we use the ErrorsFound Function that we created earlier to check if any errors occured, if not then commit the transaction, otherwise do rollback.

Then we close the connection and show the error message ( if any ) and then the records.

The point to note is that the two queries we ran as a transaction will only be executed if no database errors occur, if they do then the queries will be rolledback and no changes will be produced in the database.

Running the ASP page
You should place both the db.mdb and trans.asp files in the same directory. Assuming that you placed both of them under /trans/ directory under your virtual directory, you should use http://127.0.0.1/trans/trans.asp URL to see your ASP page on your local computer.

Notice the database error and how it is displayed. At the bottom, all the records inserted so far are displayed in a tabular fashion.

What we learned ?
We built an ASP page which uses ASP-Database transactions to either execute all of the queries or none at at all depending on the condition that any database errors are produced or not.

We also built a very useful Function which you can use to track database errors and display them in a feasable way to the user. Not only this function allows to show database errors if you want, you can use it transparently without showing any erros to check if any database errors occured or not and then committing and rolling back the transactions accordingly.

 <%
  Option Explicit
  Response.Buffer = True

 On Error Resume Next

 Dim connStr, eStr
  connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
      Server.MapPath("db.mdb")

 Function ErrorsFound(mycon)
  Dim myError

  If mycon.State <> 1 Then
   eStr = "<table border=1 width=""90%"" align=""center""" & _
   " bordercolor=""#E2EAEE""" & _
   " style=""font-family:verdana; font-size:8pt;""  cellpadding=3>" & _
   "<tr><td>Error</td><td>Database not found.</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>"

  ErrorsFound = True

  ElseIf mycon.Errors.Count > 0 Then
   For Each myError in mycon.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>"

   ErrorsFound = True
  End If
   Next
  Else
   ErrorsFound = False
  End If
 End Function
 %>
 <html>
 <head>
  <style>body, p, td { font-family : Verdana; font-size : 10pt; }</style>
 </head>
 <body>
 <%
  Dim con
   Set con = Server.CreateObject("ADODB.Connection")

   con.Open connStr
    Response.Write "Opening Connection...<br><br>"

   con.BeginTrans
    Response.Write "BeginTrans Called...<br>"

   con.Execute("insert into TestTable(name) values ('Salim Elahi')")
    Response.Write "Trying to insert records .no1...<br>"

   con.Execute("insert into TestTable(name) values ('Arshad Khan')")
    Response.Write "Trying to insert records .no2...<br>"

   If ErrorsFound(con) = False Then
    con.CommitTrans
    Response.Write "Committing Transaction...<br>"
    Response.Write "Records added successfully...<br><br>"

   Else
    con.RollbackTrans
    Response.Write "Rolling back transaction...<br>"
    Response.Write "Records were not added...<br><br>"

   End If

  con.Close
   Response.Write "Closing Connection...<br>"

  Set con = Nothing
   Response.Write "Setting Con = Nothing...<br><br><br>"

  If Len(eStr) Then
   Response.Write eStr
  End If

  ' ADO Constants
  Const adCmdText = &H0001
  Const adCmdTableDirect = &H0200

  ' Recordset Object
  Dim rs, query
   query = "TestTable"
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.Open query, connStr, , , adCmdTableDirect

   If Not rs.EOF Then
  ' Creating the table
  Dim i, j
   Response.Write "<table align=""center"" border=""1"""
   Response.Write " width=""90%"" cellspacing=""1"" cellpadding=""3""" & _
   Response.Write " bordercolor=""silver"">" & vbcrlf
   Response.Write "<tr>"
  Dim Item
   For Each Item in rs.Fields
    Response.Write "<td bgcolor=""silver"">" & Item.Name & "</td>"
   Next
   Response.Write "</tr>"

 Dim ds
  ds = rs.GetRows

  End If

  rs.Close
  Set rs = Nothing

 If IsArray(ds) Then
  ' Showing Each Row
  For i = 0 To UBound(ds, 2)
   Response.Write "<tr>" & vbcrlf
   ' Showing Each Column
    For j = 0 To UBound(ds, 1)
     If ds(j, i) = False Then
     Response.Write "<td style=""color:red;font-size:7pt;"">"
     ElseIf ds(j, i) = True Then
     Response.Write "<td style=""color:green;font-weight:bold;"">"
     Else
     Response.Write "<td>"
     End If
     Response.Write ds(j, i)
     Response.Write "</td>"
    Next
   Response.Write "</tr>" & vbcrlf
  Next

  Response.Write "</table>"

  Set ds = Nothing
 End If

 %>
 </body>
 </html>