Executing All or None queries : ASP Transactions
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 DatabaseCreate a new Access database and save it as db.mdb. Now create a new table in 'design view' as follows :
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 DatabaseCreate 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 FunctionWe 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 = "" & _ "
"
ErrorsFound = True
ElseIf mycon.Errors.Count > 0 Then For Each myError in mycon.Errors
If myError.Number <> 0 Then eStr = "" & _ "
"
ErrorsFound = True End If Next Else ErrorsFound = False End If
End FunctionThis 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.
ExplanationI will only explain the important parts of the Function above.
If mycon.State <> 1 ThenConnection.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 ThenNext 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 pageCreate 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 :
ErrorsFound FunctionWe 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 = "
| Error | Database not found. |
| Page | " & Request.ServerVariables("SCRIPT_NAME") & _ " |
| Date & Time | " & FormatDateTime(Date, 1) & _ " " & Time & " |
"
ErrorsFound = True
ElseIf mycon.Errors.Count > 0 Then For Each myError in mycon.Errors
If myError.Number <> 0 Then eStr = "
| Error Property | Contents | " & _ "
| Number | " & myError.Number & _ " |
| Native Error | " & _ myError.NativeError & " |
| SQLState | " & myError.SQLState & _ " |
| Source | " & _ myError.Source & " |
| Description | " & _ myError.Description & " |
| Page | " & _ Request.ServerVariables("SCRIPT_NAME") & _ " |
| Date & Time | " & FormatDateTime(Date, 1) & _ " " & Time & _ " |
"
ErrorsFound = True End If Next Else ErrorsFound = False End If
End FunctionThis 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.
ExplanationI will only explain the important parts of the Function above.
If mycon.State <> 1 ThenConnection.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 ThenNext 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 pageCreate 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 :
<% 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 = "" & _ "
" ErrorsFound = True ElseIf mycon.Errors.Count > 0 Then For Each myError in mycon.Errors If myError.Number <> 0 Then eStr = "" & _ "
" ErrorsFound = True End If Next Else ErrorsFound = False End If End Function %><% Dim con Set con = Server.CreateObject("ADODB.Connection") con.Open connStr Response.Write "Opening Connection...
" con.BeginTrans Response.Write "BeginTrans Called...
" con.Execute("insert into TestTable(name) values ('Salim Elahi')") Response.Write "Trying to insert records .no1...
" con.Execute("insert into TestTable(name) values ('Arshad Khan')") Response.Write "Trying to insert records .no2...
" If ErrorsFound(con) = False Then con.CommitTrans Response.Write "Committing Transaction...
" Response.Write "Records added successfully...
" Else con.RollbackTrans Response.Write "Rolling back transaction...
" Response.Write "Records were not added...
" End If con.Close Response.Write "Closing Connection...
" Set con = Nothing Response.Write "Setting Con = Nothing...
" 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 "" & vbcrlf Response.Write "
" Set ds = Nothing End If %>
ExplanationAlthough 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. con.BeginTrans
Response.Write "BeginTrans Called...
"
After opening the connection to the database, we execute the Connection.BeginTrans method to start the transaction.con.Execute("insert into TestTable(name) values ('Salim Elahi')")
Response.Write "Trying to insert records .no1...
"
con.Execute("insert into TestTable(name) values ('Arshad Khan')")
Response.Write "Trying to insert records .no2...
"
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.If ErrorsFound(con) = False Then
con.CommitTrans
Response.Write "Committing Transaction...
"
Response.Write "Records added successfully...
"
Else
con.RollbackTrans
Response.Write "Rolling back transaction...
"
Response.Write "Records were not added...
"
End If
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 pageYou 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.
| Error | Database not found. |
| Page | " & Request.ServerVariables("SCRIPT_NAME") & _ " |
| Date & Time | " & _ FormatDateTime(Date, 1) & " " & Time & _ " |
" ErrorsFound = True ElseIf mycon.Errors.Count > 0 Then For Each myError in mycon.Errors If myError.Number <> 0 Then eStr = "
| Error Property | Contents | " & _ "
| Number | " & myError.Number & _ " |
| Native Error | " & _ myError.NativeError & " |
| SQLState | " & myError.SQLState & _ " |
| Source | " & _ myError.Source & " |
| Description | " & _ myError.Description & " |
| Page | " & Request.ServerVariables("SCRIPT_NAME") & _ " |
| Date & Time | " & _ FormatDateTime(Date, 1) & " " & Time & _ " |
" ErrorsFound = True End If Next Else ErrorsFound = False End If End Function %><% Dim con Set con = Server.CreateObject("ADODB.Connection") con.Open connStr Response.Write "Opening Connection...
" con.BeginTrans Response.Write "BeginTrans Called...
" con.Execute("insert into TestTable(name) values ('Salim Elahi')") Response.Write "Trying to insert records .no1...
" con.Execute("insert into TestTable(name) values ('Arshad Khan')") Response.Write "Trying to insert records .no2...
" If ErrorsFound(con) = False Then con.CommitTrans Response.Write "Committing Transaction...
" Response.Write "Records added successfully...
" Else con.RollbackTrans Response.Write "Rolling back transaction...
" Response.Write "Records were not added...
" End If con.Close Response.Write "Closing Connection...
" Set con = Nothing Response.Write "Setting Con = Nothing...
" 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 "
| " & Item.Name & " | " Next Response.Write "||
| " ElseIf ds(j, i) = True Then Response.Write " | green;font-weight:bold;"">" Else Response.Write " | " End If Response.Write ds(j, i) Response.Write " | " Next Response.Write "
ExplanationAlthough 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. con.BeginTrans
Response.Write "BeginTrans Called...
"
After opening the connection to the database, we execute the Connection.BeginTrans method to start the transaction.con.Execute("insert into TestTable(name) values ('Salim Elahi')")
Response.Write "Trying to insert records .no1...
"
con.Execute("insert into TestTable(name) values ('Arshad Khan')")
Response.Write "Trying to insert records .no2...
"
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.If ErrorsFound(con) = False Then
con.CommitTrans
Response.Write "Committing Transaction...
"
Response.Write "Records added successfully...
"
Else
con.RollbackTrans
Response.Write "Rolling back transaction...
"
Response.Write "Records were not added...
"
End If
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 pageYou 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.
