How To Utilize Database Transactions Within a VB Component From an ASP Page, Part 2

By Doug Dean

In Part 2 we looked at the details of the database operation, such as setting up the variables, opening the database connections, and executing a database action. In this part we'll examine how to react to the results of a failed or (hopefully) successful database action!

- continued -

Committing the Transaction
Our database work is done. The variable acquired in the HTML form has been saved, the record count obtained, increased by one, and also saved. If no errors have occurred, the programming control has arrived safely to this point. It's time to commit out database work for good. This completion is accomplished via the CommitTrans method.

'~~~~~~ Commit the transaction and close the database connection objConn.CommitTrans objConn.Close

After the transaction is committed, the database connection is closed. Setting the objects to Nothing comes next.

'~~~~~~~ Set to Nothing If IsObject(Rs) Then Set Rs = Nothing If IsObject(objCmd) Then Set objCmd = Nothing If IsObject(objConn) Then Set objConn = Nothing

And then we return our new record count to our asp file code and exit the function with the Exit Function statement.

'~~~~~ Return current count value MethodName = lngCountID Exit Function

Rollback if Errors
Should an error interrupt the flow of database processing, a Rollback must be issued so that sections of the database won't be completed piecemeal. Since all errors have been determined to arrive at the ErrorCode: statement, we'll use this as our cleanup area by rolling back the database commands, closing any open database objects, and raising the error so it will be passed on to the calling code.

ErrorCode: '~~~~~ Rollback transactions and close objects objConn.RollbackTrans If IsObject(Rs) Then Set Rs = Nothing If IsObject(objCmd) Then Set objCmd = Nothing If IsObject(objConn) Then Set objConn = Nothing '~~~~~ Raise errors for asp page Err.Raise Err.Number, Err.Source, Err.Description End Function

THE ASP FILE - How2Project2.asp

Instantiate The VB Object
The ASP page that instantiates our VB object serves double duty by also displaying the HTML form for entering the string value that's placed in our database. The VB object will only be used if there is an incoming string variable issued from our form. Since the VB object and the HTML form are both contained in the same ASP page, a simple If .. Then statement makes the VB object contingent on whether the asp file called itself with, or without, a form string value.

The first statement lets the VB object method only be called if there is a form string present for saving to the database.

<% '============================================================= ' ADD A TABLE RECORD AND UPDATE COUNT IF FORM VALUE IS PRESENT '============================================================= If Not Request.Form("FormValue") = "" Then '~~~~~~~~~~~~~~~~~~~~~~ Set Object ~~~~~~~~~~~~~~~~~~~~~ Set oClass = Server.CreateObject("How2Project2.ClassName") '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '~~~~~~~~~~~~~~~~~~ Get Detached RecordSet ~~~~~~~~~~~~~~ lngCount = oClass.MethodName(Request.Form("FormValue")) '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '~~~~~~~~~~~~~~~~~ Set Objects to Nothing ~~~~~~~~~~~~~~~ Set oClass = Nothing '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ End If '============================================================= %>

The HTML FORM
Either way, the HTML code will display the form and return to this same asp page when the summit button is selected. It returns to this page because the ACTION parameter in the FORM tag is set to this asp file's name (How2Project2.asp).

 <HTML><HEAD><TITLE>How2 EXAMPLE</TITLE></HEAD><BODY>  <FORM ACTION="How2Project2.asp" METHOD="post"> Enter a value to add to the database: <INPUT TYPE="text" NAME="FormValue" SIZE="35" MAXLENGTH="254"> <INPUT TYPE="submit" NAME="Submit" VALUE="Submit"> </FORM>

If the VB object method returns the record count, we'll display that too.

 <% If lngCount > 1 Then Response.Write("Current record count = " & lngCount) %>  </BODY></HTML>

Code complete! Happy Programming!

By Doug Dean

Attachments:

Download the source for the VB project and ASP pages (in ZIP format)