Recordset: Adding, Updating, and Deleting Records (ODBC)

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

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

****Note   ****You can now add records in bulk more efficiently. For information, see the article Recordset: Adding Records in Bulk (ODBC).

****Note   ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you are using bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

Updatable snapshots and dynasets allow you to add, edit (update), and delete records. This article explains:

• How to determine whether your recordset is updatable.

• How to add a new record.

• How to edit an existing record.

• How to delete a record.

For more information about how updates are carried out and how your updates appear to other users, see the article Recordset: How Recordsets Update Records (ODBC). Normally, when you add, edit, or delete a record, the recordset changes the data source immediately. You can instead batch groups of related updates into transactions. If a transaction is in progress, the update doesn’t become final until you commit the transaction. This allows you to take back or roll back the changes. For information about transactions, see the article Transaction (ODBC).

The following table summarizes the options available for recordsets with different update characteristics.

Recordset Read/Update Options

Type	Read	Edit records	Delete records	Add new (append)
Read-only	Y	N	N	N
Append-only	Y	N	N	Y
Fully updatable	Y	Y	Y	Y

Determining Whether Your Recordset is Updatable

A recordset object is updatable if the data source is updatable, and you opened the recordset as updatable. Its updatability also depends on  the SQL statement you use, the capabilities of your ODBC driver, and whether the ODBC Cursor Library is in memory or not. You can’t update a read-only recordset or data source.

To determine whether your recordset is updatable

• Call the recordset object’s member function.

  CanUpdate returns a nonzero value if the recordset is updatable.

By default, recordsets are fully updatable (you can perform AddNew, Edit, and Delete operations). But you can also use the option to open updatable recordsets. A recordset opened this way allows only the addition of new records with AddNew. You can’t edit or delete existing records. You can test whether a recordset is open only for appending by calling the member function. CanAppend returns a nonzero value if the recordset is either fully updatable or open only for appending.

The following code shows how you might use CanUpdate for a recordset object called rsStudentSet:

if( !rsStudentSet.Open( ) )
    return FALSE;
if( !rsStudentSet.CanUpdate( ) )
{
    AfxMessageBox( "Unable to update the Student recordset." );
    return;
}

****Caution   ****When you prepare to update a recordset by calling Update, take care that your recordset includes all columns making up the primary key of the table (or all of the columns of any unique index on the table). In some cases, the framework can use only the columns selected in your recordset to identify which record in your table to update. Without all the necessary columns, multiple records may be updated in the table, possibly damaging the referential integrity of the table. In this case, the framework will throw exceptions when you call Update.

Adding a Record to a Recordset

You can add new records to a recordset if its member function returns a nonzero value.

To add a new record to a recordset

1. Make sure the recordset is appendable.

2. Call the recordset object’s member function.

   AddNew prepares the recordset to act as an edit buffer. All field data members are set to the special value Null and marked as unchanged so only changed (“dirty”) values will be written to the data source when you call .

3. Set the values of the new record’s field data members.

   Assign values to the field data members. Those you don’t assign will not be written to the data source.

4. Call the recordset object’s Update member function.

   Update completes the addition by writing the new record to the data source. For what happens if you fail to call Update, see the article Recordset: How Recordsets Update Records (ODBC).

For information about how adding records works and about when added records are visible in your recordset, see the article Recordset: How AddNew, Edit, and Delete Work (ODBC).

The following example shows how to add a new record:

if( !rsStudent.Open( ) )
    return FALSE;
if( !rsStudent.CanAppend( ) )
    return FALSE;                      // no field values were set
rsStudent.AddNew( );
rsStudent.m_strName = strName;
rsStudent.m_strCity = strCity;
rsStudent.m_strStreet = strStreet;
if( !rsStudent.Update( ) )
{
    AfxMessageBox( "Record not added; no field values were set." );
    return FALSE;
}

For additional information, see Adding a Record in the article Recordset: How AddNew, Edit, and Delete Work.

****Tip   ****To cancel an AddNew or Edit call, simply make another call to AddNew or Edit or call Move with the AFX_MOVE_REFRESH parameter. Data members will be reset to their previous values and you will still be in Edit or Add mode.

Editing a Record in a Recordset

You can edit existing records if your recordset’s member function returns a nonzero value.

To edit an existing record in a recordset

1. Make sure the recordset is updatable.

2. Scroll to the record you want to update.

3. Call the recordset object’s member function.

   Edit prepares the recordset to act as an edit buffer. All field data members are marked so that the recordset can tell later whether they were changed. The new values for changed field data members are written to the data source when you call .

4. Set the values of the new record’s field data members.

   Assign values to the field data members. Those you don’t assign values will remain unchanged.

5. Call the recordset object’s Update member function.

   Update completes the edit by writing the changed record to the data source. For what happens if you fail to call Update, see the article Recordset: How Recordsets Update Records (ODBC).

After you edit a record, the edited record remains the current record.

The following example shows an Edit operation. It assumes the user has moved to a record he or she wants to edit.

rsStudent.Edit( );
rsStudent.m_strStreet = strNewStreet;
rsStudent.m_strCity = strNewCity;
rsStudent.m_strState = strNewState;
rsStudent.m_strPostalCode = strNewPostalCode;
if( !rsStudent.Update( ) )
{
    AfxMessageBox( "Record not updated; no field values were set." );
    return FALSE;
}

For more information, see Editing an Existing Record in the article Recordset: How AddNew, Edit, and Delete Work.

****Tip   ****To cancel an AddNew or Edit call, simply make another call to AddNew or Edit or call Move with the AFX_MOVE_REFRESH parameter. Data members will be reset to their previous values and you will still be in Edit or Add mode.

Deleting a Record from a Recordset

You can delete records if your recordset’s member function returns a nonzero value.

To delete a record

1. Make sure the recordset is updatable.

2. Scroll to the record you want to update.

3. Call the recordset object’s member function.

   Delete immediately marks the record as deleted, both in the recordset and on the data source.

   Unlike AddNew and Edit, Delete has no corresponding Update call.

4. Scroll to another record.

   ****Important   ****When moving through the recordset, deleted records may not be skipped. See the member function for details.

The following example shows a Delete operation. It assumes the user has moved to a record he or she wants to delete. After Delete is called, it’s important to move to a new record.

rsStudent.Delete( );
rsStudent.MoveNext( );

For more information about the effects of the AddNew, Edit, and Delete member functions, see the article Recordset: How Recordsets Update Records (ODBC).

See Also   Recordset: Locking Records (ODBC)