DAO Queries: Filtering and Parameterizing Queries

Article
09/15/2006

This article describes how to restrict the number of records that a query returns. Topics covered include:

Filtering recordsets
Parameterizing queries

One of the great keys to good database performance is to restrict how many records you select. In general, the more records you select, the greater the required memory and the slower the performance. DAO and MFC let you filter the records that a query selects, and you can specify filtering criteria at run time rather than design time. The mechanism works as follows:

Specify a filter for your query that restricts records using an SQL WHERE clause. For example:
```
WHERE [State] = "NY"
```
Parameterizing specifies named parameters in the filter to which you can assign values at run time, based on information you calculate or obtain from the end-user. For example, the filter shown above looks like this with a named parameter:
```
WHERE [State] = [State Code]
```
State Code is the parameter name.

Filtering DAO Recordsets

Filtering records by any of the approaches described below relies on the SQL WHERE clause. You can also use the HAVING clause if you are using GROUP BY. For information about these keywords, see the following topics in DAO Help:

WHERE Clause (SQL)
HAVING Clause (SQL)
GROUP BY Clause (SQL)

And see the topic "SELECT Statement (SQL)" in DAO Help.

The MFC DAO classes let you filter a recordset in two ways:

You can specify an SQL statement for your recordset that lacks a WHERE clause, then supply a value at run time to the data member of your -derived class.
You can specify an SQL statement that includes a WHERE clause. In this case, you do not use m_strFilter.

****Tip ****These two approaches are equivalent in terms of performance. The only difference is whether you build the WHERE clause in the SQL string that you use to create the recordset or you let MFC build the clause using a value you’ve supplied for m_strFilter.

****Important ****You can’t use (or its companion , which specifies an SQL ORDER BY clause for sorting) if you create your recordset from a or object.

Example with m_strFilter

The following example shows filtering with m_strFilter (the first approach above):

// Filter records with m_strFilter but no parameter
// strStudentID is a value probably obtained from
// the user
rsEnrollmentSet.m_strFilter = "[Student ID] = " + strStudentID;
try
{
    // Open the recordset using the filtered string
    rsEnrollmentSet.Open( );
    // ...
}
// ...

MFC appends the value of to the recordset’s SQL as long as there is not a WHERE clause in the SQL string already.

Example with a Complete WHERE Clause

The following example shows filtering with a pre-specified WHERE clause (the second approach above):

// Filter records with the SQL keyword WHERE
CString strSQL = rsEnrollmentSet.GetDefaultSQL( ) +
             "WHERE [Student ID] = " + strStudentID;
try
{
    // Open the recordset using the filtered SQL string
    rsEnrollmentSet.Open( dbOpenDynaset, strSQL );
    // ...
}
// ...

The example calls to obtain the SQL string defined for the recordset's class at design time, using ClassWizard or AppWizard. Then it concatenates a WHERE clause, part of which is based on run-time information in strStudentID.

In either case, the result is a recordset that contains a smaller number of records because of the filtering.

****Note ****The filtering and sorting mechanisms described here are not available for table-type recordsets. To filter or sort records in a table-type recordset, you must call DAO directly. Set the Filter and Sort properties of the recordset. To specify which index (if any) is active for the recordset, call . For information about calling DAO directly, see .

Parameterizing DAO Queries

In situations where your application executes the same query repeatedly, it is more efficient to create a stored querydef object that contains the SQL statement. Queries stored in the database execute faster and can be used by anyone with access to the database.

If your application needs to alter WHERE clause arguments in a query, you can also add a PARAMETERS clause to your query that permits the Microsoft Jet database engine to substitute values into the query at run time. Before running parameter queries, your application must substitute values for each of the parameters as stored in the Parameters collection of the querydef.

In general, parameterizing queries improves performance. The parameterized SQL statement doesn't have to be recompiled each time you run the query.

To create a parameter query

Create a PARAMETERS clause string that includes a parameter name and data type for each parameter. Don't use the field name alone as the parameter name, because duplicating it may cause problems. You can include the field name within the parameter name, however. The example calls the parameter "Student Ident" rather than "Student ID", the name of the field.

If you are working with a database accessed by Microsoft Access, the parameter name is used as a prompt string. Keep this in mind if you expect Microsoft Access users to use this query.

Shown below is a typical PARAMETERS clause:
```
CString strParam = "PARAMETERS [Student Ident] TEXT; ";
```
The parameter name is enclosed in square brackets here because the name contains a space. Otherwise the brackets are unnecessary.
Create a SELECT statement that retrieves the needed fields and incorporates the named parameters into the WHERE clause. In the example below, the parameters are used to filter the query to return only selected students. Note that the parameter [Student Ident] is substituted by the database engine during execution of the query at run time.
```
strSQL = strParam + "SELECT * FROM Enrollment WHERE Enrollment.[Student ID] = [Student Ident]";
```

Create a named querydef ("Find Enrollments") with your SQL statement.

CDaoQueryDef qd( m_dbStudentReg );
qd.Create( "Find Enrollments", strSQL );
qd.Append( );

Set the querydef parameters.

First, you need to gain access to the querydef. You can either use the querydef object just created, or reference the stored querydef object from the QueryDefs collection. The example shows using the querydef just created.
```
COleVariant varParamValue( strStudentID );
qd.SetParamValue( "[Student ID]", varParamValue );
```
Execute the procedure.

Because this query returns records, you need to create a recordset to capture the result set.
```
CEnrollmentSet rsEnrollmentSet( &m_dbStudentReg );
rsEnrollmentSet.Open( &qd, dbOpenDynaset );
```

The parameter is defined as part of the SQL statement and becomes part of a PARAMETERS clause. You set the value of the parameter, at run time, by calling the querydef object's member function. This function takes:

A parameter name, which must match the name you specified in the SQL string ("Student Ident" in the example).
A object that contains the value. COleVariant makes it easy to use the data type from MFC for a variety of different actual types. In the example, the actual type is a string.

For more information and a different example (presented in the Basic language rather than C++), see the topic "Creating Parameter Queries with DAO" in DAO Help.

In the Class Library Reference, see and . In particular, see and .

What do you want to know more about:

See Also DAO: Where Is...

Share via