How To Utilize Database Transactions Within a VB Component From an ASP PageBy Doug Dean
Do you have comments or questions about this article? Join an on-line
discussion forum to share
your thoughts, views, and questions!|
[Visit the Forum]
This article serves as a quick "How To" example for utilizing database transactions within a VB component. The example asp code calls a VB method that encloses
SQL statements within a transaction. Two database tables will be used, one for adding a single string to a
record field and the other for keeping track of the number of the saved records. By enclosing the database commands
within a transaction, the count will remain accurate if an error should occur between saving the form variable
and increasing the record count.
Why Use Transactions?
In having database commands 'grouped' together as one, so to speak, database values that are related to each other can be kept in balance. Keep in mind that the sample code in this article is not the best real-world example of transactions. The stereotypic real-world example for transactions involves the transfer of money from one account to another. If the process wasn't issued as a single transaction, money taken out of one account could be lost if an error occurred before it was placed in another account.
I rely on transactions in the database related methods built into my forum applications EZsite Forum 3 and EZsite Forum DEV to ensure that deleting posts will not corrupt the complex thread structure of the forums, as well as many other forum functions. Using database transactions aren't only necessary for ensuring that database commands will be completed as one unit, they also speed up the database process itself.
The supporting database file, text, and code, can be downloaded here. If you are unfamiliar with writing a server-side VB dll, you can read some introductory articles at my site, http://www.dougdean.com.
The VB Component
The Function Definition
One parameter will be passed to our component; a string (
strArgumentIn), which will be saved in
the first database table (
ExampleTable1). The string value will be acquired from our asp file
How2Project2.asp) via a HTML form.
Our method returns an Integer that will hold the current count of the number of records saved in
ExampleTable1. So after Inserting our
strArgumentIn string value in the
ExampleTable1 table, we'll
SELECT the count from the
table, increase the count by one, and then
UPDATE the count in the
to reflect the newly save record.
The Database Tables
ExampleTable1 holds the records we'll be adding to via the HTML form.
holds the count of the records in
In Part 2 we'll look at the details of the VB component!