DAO Recordset: Binding Records Dynamically

HomeOverviewHow Do IFAQSampleTutorialODBC Driver List

This article explains how to use an object of class CDaoRecordset directly, without deriving your own recordset class. Topics covered include:

As the MFC Database sample DAOVIEW shows, you can use dynamic binding to work with database schema information not known at design time. For related information on examining a database schema at run time, see the article DAO Tabledef: Examining a Database Schema at Run Time.

The Standard Case: Using a Derived Recordset Class

For many applications, you will prefer to create, at design time, a CDaoRecordset-derived class. Using AppWizard or ClassWizard, you can design a class that represents your table or query. You specify the database, the table, and the columns (fields). This information is then encapsulated in the class's connection information, its SQL string, and its data members. Records are statically bound to the recordset at run time via the DoFieldExchange mechanism. For more information, see the article DAO Recordset: Creating Recordsets.

The point is that to operate this way, you must know the database schema at design time so you can specify which table to use and which fields to use from that table. In many applications, this works well. If your database schema is relatively static and users are not constantly adding or deleting tables and table fields, you can design in this way.

Binding Records Dynamically Instead

If your database schema is relatively dynamic, or if you face a situation in which the schema is unknown at design time, dynamic binding could be the answer.

For dynamic binding, you don't need a derived CDaoRecordset class. Instead, you use CDaoRecordset directly. Here's the general process:

  1. Construct a CDaoRecordset object.

  2. Call its Open member function to connect to a specified database and run a query.

  3. Navigate through the records, using the recordset's navigation member functions, such as Move.

  4. Call the recordset's GetFieldValue member function to retrieve, immediately, the value of a specified field in the record. Or call Edit, then SetFieldValue, then Update to set the field in the database.

Binding dynamically in this way is flexible. You don't have to know the database schema at design time, and you can keep up with a changing schema. This mechanism doesn't use the DoFieldExchange mechanism.

You may get better performance with dynamic binding than with static binding via DAO record field exchange (DFX) if you don’t need every field bound for every record retrieved. However, for applications in which the database schema is reasonably unchanging, binding via DFX is a good choice because DFX manages all of the recordset’s fields for you, reducing the amount of code you must write to bind fields.

The following example, borrowed from the MFC Database sample DAOVIEW, illustrates dynamic binding. The code creates a table-type recordset, which is used to scroll through all records in a table, getting the values of fields in the current record and adding them to an MFC CListCtrl object.

Note   For more information on the CCrack or CListCtrlEx classes used in the following example, see the MFC Database sample DAOVIEW.

// db is a pointer to a CDaoDatabase object.
// dbOpenTable specifies a table-type recordset.
// CCrack is a custom class used to get the actual 
// type from a COleVariant object.
// nRecord is used for positioning in the list control.
 // the list control used here is an extended version 
 // implemented in DAOView.  The CListCtrl class does not
 // provide an AddItem() member function as does the
 // extended version.
// m_ctlList is a CListCtrlEx object; this class
// implements an AddItem method for the list control.

CDaoRecordset rs( &db );
int nRecord = 0;

// Open MFC DAO objects in a try block to catch 
// security violations when opening tables
try
{
    // Open the recordset, passing a table name 
    // for the SQL
    rs.Open( dbOpenTable, strTableName );

    // Move through records
    while( !rs.IsEOF( ) )
    {
        COleVariant var;
        // Move through fields in current record
        int nFields = rs.GetFieldCount( );
        for ( int i=0; i < nFields; i++ )
        {
            var = rs.GetFieldValue( i );
            // Add field value to list control
            m_ctlList.AddItem( nRecord,i,
                              CCrack::strVARIANT( var ) );
        }
        nRecord++;
        rs.MoveNext( );
    }
}
catch( CDaoException* e )
{
    // Do nothing--used for security violations 
    // when opening tables
    e->Delete( );
}

The key features in this example are:

Also of interest are:

Note   In addition to binding recordset fields dynamically, you can also bind query parameters dynamically. If you base your CDaoRecordset on a CDaoQueryDef object that has parameters defined, you can get or set the values of the parameters by calling CDaoQueryDef::GetParamValue or CDaoQueryDef::SetParamValue. Set parameter values for the querydef, then open a recordset based on the querydef. This mechanism doesn’t use DFX.

For other examples of dynamic binding, see the LISTVIEW.CPP file in the MFC Database sample DAOVIEW, and see the MFC Database sample DAOCTL, which illustrates a data-bound ActiveX control.

Dynamically Setting and Getting Parameter Values

If you create recordsets based on a querydef object, you can parameterize the querydef, then use it to create a recordset:

  1. Use the PARAMETERS clause in the querydef’s SQL statement to establish the parameters. For information, see the topics "PARAMETERS Declaration (SQL)" and "Creating Parameter Queries with DAO" in DAO Help. See also the article DAO Queries: Filtering and Parameterizing Queries.

  2. Create the querydef based on that SQL statement. See the article DAO Querydef: Using Querydefs.

  3. Set the values of the parameters by calling CDaoQueryDef::SetParamValue for each parameter.

  4. Create and open a recordset based on the querydef. See the article DAO Recordset: Creating Recordsets.

If you want to examine the value of a querydef’s parameter, call CDaoQueryDef::GetParamValue.

See Also   DAO: Where Is..., DAO Recordset, DAO Tabledef: Examining a Database Schema at Run Time