Introduction
In this article we will learn on how to use database transactions in ASP. This will allow us to execute all database queries or none at all. How many times have you wanted to insert, update a lot of records collectively wanting that either all of them get executed or if there is an error then none is executed at all ? Well, if you haven't this need yet, you will have it in near future.

What are transactions ?
"A transaction is an atomic unit of work that either fails or succeeds as a whole." In a transaction there can be any number of things to do, like update one thing, send email, do that thing and so on. If transaction is successful, all of these things will be done or executed as the case may be, or if transaction fails then none of them will be executed.

When a transaction is successful it is said that this transaction has committed and all the tasks that it had to do will be done. If there is some error then the whole transaction will be brought back to it's original state and none of the task that it had to do will be completed, it is called rolling back of the transaction.

Transactions are very simple as you might have learned by now. They are a means to allow us to do many tasks or not to do any of them. There is no such thing in a transaction that one task is done and other is not. Transactions are always executed as a 'whole'.

In this article we will learn a part of these transactions, database transactions. Where we will be able to execute all of the SQL queries or if some error occurs then rollback all the actions and no query gets executed. We will also build a very useful Function to track database errors and show them to the user if some error occurs. We will use this Function to see if there have been any errors in executing any query, if not then commit the transaction otherwise rollback!

Our Access Database
Create a new Access database and save it as db.mdb. Now create a new table in 'design view' as follows :

Save this table as TestTable. After that populate this table as follows :

Now we are ready to build the ASP Database error tracking function we talked about and learn some more about database transactions.

ErrorsFound Function
We will now look at the ErrorsFound ASP Function that will track database errors and report it to the user. You don't need to create a new ASP page now, we will do that on the next page. For now just have a look at this Function and see how easy it is to track database errors.

  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

This Function will return True if an error is found and False if none. Note this Function can also detect the error if you remove or rename the database.

Explanation
I will only explain the important parts of the Function above.

	If mycon.State <> 1 Then

Connection.State property tells us that whether the connection to the database is open or not. If connection is open then it is equal to 1 and if closed then it is equal to 0. So in the above line we check this property to detect if database could be opened or not, if not then we show appropriate error message and exit the Function.

	ElseIf mycon.Errors.Count > 0 Then
		For Each myError in mycon.Errors
			If myError.Number <> 0 Then

Next we check to see if Connection.Errors.Count is greater than 0 or not. Note that if any errors occur then this error count will be greater than 0. So if there is a non-zero error count then iterate through the Connection.Errors collection and show all the information we have about the error.

'trans.asp' ASP page
Create a new ASP page and save it as trans.asp in the same directory where you kept the db.mdb database. Copy the following code into and save it again :