Using more than one database table for a single transaction is necessary in many cases. In these cases, we should make sure that either all of the tables involved in the transaction should get updated or none of the tables should get updated. We should not allow any chance to update the database partially.

Data Access component of ASP supports transaction processing, that helps the programmer to avoid partial updates to the database.

For example, we need to insert a row into a table called "userInfo" and update a table called "siteStat" whenever a new user signs up with a web site.

The following code do this task:

<%

    ' Connect to the Database

    MyDSN = "DSN=inet;UID=sa;PWD=qis"
    Set MyConn = Server.CreateObject("ADODB.Connection")
    MyConn.Open MyDSN

    ' Insert row into userInfo table

    sqlStr = "INSERT INTO userInfo(userId, userName, email) " & _
        "VALUES('" & Request.Form(userId) & _
        "', '" & Request.Form(userName) & _
        "', '" & Request.Form(email) & "')"
    MyConn.Execute sqlStr

    ' Update siteStat table

    sqlStr = "UPDATE siteStat set totalUsers = totalUsers + 1"
    MyConn.Execute sqlStr

    ' Close the Database Connection

    loginRs.Close
    MyConn.Close

%>

What will happen if the database server fails after inserting a row into userInfo? siteStat table will show one user lesser than the actual number of users. Imagine this situation where money transactions are involved, where it is impossible afford partial updates.

Look at the following code, that will either update both the tables or not update any table.

<%

    ' Connect to the Database

    MyDSN = "DSN=inet;UID=sa;PWD=qis"
    Set MyConn = Server.CreateObject("ADODB.Connection")
    MyConn.Open MyDSN

    ' Instruct the Err object to reset
    ' and ignore errors, if any

      On Error Resume Next

    ' Clear if there are any errors in the Errors collection of the Database Connection

    MyConn.Errors.Clear

    ' Start database transaction

    MyConn.BeginTrans

    ' Insert row into userInfo table

    sqlStr = "INSERT INTO userInfo(userId, userName, email) " & _
        "VALUES('" & Request.Form(userId) & _
        "', "' & Request.Form(userName) & _
        "', "' & Request.Form(email) & "')"
    MyConn.Execute sqlStr

    ' Update siteStat table

    sqlStr = "UPDATE siteStat set totalUsers = totalUsers + 1"
    MyConn.Execute sqlStr

    ' Check if there are any errors in the Errors collection of the Database Connection

    IF MyConn.Errors.Count = 0 THEN 

        ' Save both Insert and Update to the database
        ' if there are no errors

         MyConn.CommitTrans

    ELSE 

        ' Undo both Insert and Update from the database
        ' if there is any error

        MyConn.RollbackTrans

    END IF

    ' Close the Database Connection

    loginRs.Close
    MyConn.Close

%>

Hope u are clear. If not, mail me at: pkvnm@yahoo.com