Share via

Transaction: Performing a Transaction in a Recordset (ODBC)

  • Article

|  Overview |  How Do I |  FAQ |  Sample |  |  ODBC Driver List

This article explains how to perform a transaction in a recordset.

****Note   ****Only one level of transactions is supported; you cannot nest transactions.

To perform a transaction in a recordset

  1. Call the CDatabase object’s BeginTrans member function.

  2. If you have not implemented bulk row fetching, call the AddNew/Update, Edit/Update, and Delete member functions of one or more recordset objects of the same database as many times as needed. See the article Recordset: Adding, Updating, and Deleting Records (ODBC). If you have implemented bulk row fetching, you must write your own functions to update the data source. For an example of how to do this, see the sample .

  3. Finally, call the CDatabase object’s CommitTrans member function. Or if an error occurs in one of the updates, or you decide to cancel the changes, call its Rollback member function.

The following example uses two recordsets to delete a student’s enrollment from a school registration database, removing the student from all classes in which the student is enrolled. The Delete calls in both recordsets must succeed, so a transaction is required. The example assumes the existence of m_dbStudentReg, a member variable of type CDatabase already connected to the data source, and the recordset classes CEnrollmentSet and CStudentSet. The strStudentID variable contains a value obtained from the user.

BOOL CEnrollDoc::RemoveStudent( CString strStudentID )
{
    // remove student from all the classes
    // the student is enrolled in

    if ( !m_dbStudentReg.BeginTrans( ) )
        return FALSE;

    CEnrollmentSet rsEnrollmentSet(&m_dbStudentReg);
    rsEnrollmentSet.m_strFilter = "StudentID = " + strStudentID;

    if ( !rsEnrollmentSet.Open(CRecordset::dynaset) )
        return FALSE;

    CStudentSet rsStudentSet(&m_dbStudentReg);
    rsStudentSet.m_strFilter = "StudentID = " + strStudentID;

    if ( !rsStudentSet.Open(CRecordset::dynaset) )
        return FALSE;

    TRY
    {
        while ( !rsEnrollmentSet.IsEOF( ) )
        {
            rsEnrollmentSet.Delete( );
            rsEnrollmentSet.MoveNext( );
        }

        // delete the student record
        rsStudentSet.Delete( );

        m_dbStudentReg.CommitTrans( );
    }

    CATCH_ALL(e)
    {
        m_dbStudentReg.Rollback( );
        return FALSE;
    }
    END_CATCH_ALL

    rsEnrollmentSet.Close( );
    rsStudentSet.Close( );

    return TRUE;

}

****Warning   ****Calling BeginTrans again without calling CommitTrans or Rollback is an error.

See Also   Transaction: How Transactions Affect Updates (ODBC), ,