DAO Recordset: Binding Records Dynamically

  • Article

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

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

  • The Standard Case: Using a Derived Recordset Class

  • Binding records dynamically instead

  • Dynamically setting and getting parameter values

As the MFC Database sample 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 -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 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 class. Instead, you use CDaoRecordset directly. Here's the general process:

  1. Construct a CDaoRecordset object.

  2. Call its 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 .

  4. Call the recordset's member function to retrieve, immediately, the value of a specified field in the record. Or call , then , then 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 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 , 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 object.

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

// 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:

  • The direct use of rather than a class derived from CDaoRecordset. The example therefore doesn’t use the DAO record field exchange (DFX) mechanism.

  • The call to , which returns a value of type for a specified field in the current record. The field is specified as the index of the field in the recordset object's Fields collection.

Also of interest are:

  • The user-defined class CCrack, which has members for extracting the actual data type from a object. See the files CRACK.H and CRACK.CPP in the MFC Database sample . CCrack is not an official MFC class.

  • The use of an exception handler around the call and the other recordset operations. Using a try/catch block is recommended, if only to catch security violations when you try to open a table.

Note   In addition to binding recordset fields dynamically, you can also bind query parameters dynamically. If you base your CDaoRecordset on a object that has parameters defined, you can get or set the values of the parameters by calling or . 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 , and see the MFC Database sample , 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 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 .

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