How To Utilize Database Transactions Within a VB Component From an ASP Page, Part 2By Doug Dean
In Part 1 we looked at an introduction to transactions and a high-level
overview of the VB component. In this part, we will examine the VB component in more detail, starting with
the error statement!
The Error Statement
Since database command transactions are either 'committed' or 'rolled back', we'll need to catch any errors that occur in the process of our database work. The following statement, placed at the start of our program, will send the program flow to the line of code named
ErrorCode:, which can be found at the end of
We'll write code to rollback (negate all the database commands within our transaction) right after our
ErrorCode: statement. More on this later.
RecordSet, Command, and Connection objects are instantiated along with two variables. The string and an Integer variables are used for storing the SQL statements and temporarily holding the record count.
Begin the Transaction
Using the Connection object, we'll first open a database connection and then begin our Transaction with the
BeginTrans method. Notice that the database is opened with the
strDSN2 Data Source
Name (DSN) string. See an introductory VB component article on how to do this if you are unfamiliar with ODBC.
Once the transaction begins, it will need to be either rolled back with the
(if an error occurs) or committed with the
CommitTrans method (if all goes well). The basic
structure is as follows:
1.) Open the database connection
2.) Begin the transaction
3.) Do all the database work
4.) Commit or Rollback the transaction
5.) Close the database connection
Now that we have our database connection and transaction established, we'll quickly walk through the database work. First the
strArgumentIn variable value, which we receive as a method parameter, will be
saved in the
Now that our record is safely stored away in the
ExampleTable1 table, we'll grab the record count
ExampleTable2 table, which can be found in the Count field.
Now that we have our record count tucked safely away in the
lngCountID variable, we'll increase
its value by one.
lngCountID variable accurately reflects the number of records in the
ExampleTable1 table, it's value will be used to update the value in the database.
In Part 3 we'll look at how to respond to a successful or failed database action!