To read the article online, visit http://www.4GuysFromRolla.com/webtech/081800-1.2.shtml

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

By Doug Dean


  • Read Part 1

  • 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 our program.

    '~~~~~~~~ ERROR CODE ~~~~~~~~~~
    On Error GoTo ErrorCode

    We'll write code to rollback (negate all the database commands within our transaction) right after our ErrorCode: statement. More on this later.

    Variable Declarations
    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.

    '~~~ Declare DB connectivity and member variables ~~~
    Dim Rs As New ADODB.Recordset
    Dim objCmd As New ADODB.Command
    Dim objConn As New ADODB.Connection
    Dim sql As String
    Dim lngCountID As Long
    

    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.

    '~~~~~ Open database and begin transaction ~~~~~~~
    objConn.Open "strDSN2"
    objConn.BeginTrans
    

    Once the transaction begins, it will need to be either rolled back with the RollbackTrans method (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

    Database Commands
    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 ExampleTable1 table.

    '~~~ Insert the String and Integer agruments
    '    into the first database 
    sql = "INSERT INTO ExampleTable1 " & _
          "(ExampleField1) VALUES ('" & strArgumentIn & "')"
    objCmd.CommandText = sql
    objCmd.CommandType = adCmdText
    Set objCmd.ActiveConnection = objConn
    objCmd.Execute
    

    Now that our record is safely stored away in the ExampleTable1 table, we'll grab the record count from the ExampleTable2 table, which can be found in the Count field.

    '~~ Get the record count from the second database table
     sql = "Select Count FROM ExampleTable2"
     objCmd.CommandText = sql
     objCmd.CommandType = adCmdText
     Set objCmd.ActiveConnection = objConn
    
     Rs.Open objCmd
    
      '----- Get record count
      lngCountID = Rs.Fields("Count")
    
    Rs.Close
    

    Now that we have our record count tucked safely away in the lngCountID variable, we'll increase its value by one.

    '~~~~~ Increase count varaible
    lngCountID = lngCountID + 1

    Once the 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.

    '~~~~~~ Save the increased count variable by one
    sql = "UPDATE ExampleTable2 SET Count = " & lngCountID
    objCmd.CommandText = sql
    objCmd.CommandType = adCmdText
    Set objCmd.ActiveConnection = objConn
    objCmd.Execute
    

    In Part 3 we'll look at how to respond to a successful or failed database action!

  • Read Part 3


  • Article Information
    Article Title: How To Utilize Database Transactions Within a VB Component From an ASP Page, Part 2
    Article Author: Doug Dean
    Published Date: Friday, August 18, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/081800-1.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers