Parameterizing the Filter in DaoEnrol

DaoEnrol reselects, or “requeries,” class section records every time the user selects a new course name from the combo box. One way to implement this is to close the old CSectionSet object and reopen it, supplying a new m_strFilter value before calling Open. This works, but is somewhat inefficient, because the framework has to completely reconstruct and run a new SQL SELECT statement. A more efficient way to requery the same recordset is to “parameterize” the filter — call Requery with a new filter value and a specific parameter value.

Suggested Reading

In order to parameterize the filter, you’ll perform the following procedures:

To implement the Requery with a new filter and a specific parameter value supplied at run time, you:

To declare a parameter data member in the recordset’s header file

  1. Using FileView, open the SectSet.h file.

  2. Add the following member variable declaration for m_strCourseIDParam, just before the //Overrides section, after the //}}AFX_FIELD line:
    CString m_strCourseIDParam;
    

To bind the parameter data member to the recordset

  1. Use ClassView to navigate to the CSectionSet constructor, and initialize the parameter count variable, m_nParams, which by default is zero. Also initialize DaoEnrol’s single parameter, m_strCourseIDParam.

    Place the following two lines of code after the line m_nDefaultType = dbOpenDynaset;:

    m_nParams = 1;
    m_strCourseIDParam = "";
    
  2. Use ClassView to navigate to the DoFieldExchange member function definition, and add the following two lines of code to identify m_strCourseIDParam as a parameter data member. Add the code at the end of the function, after the //}}AFX_FIELD_MAP line.
    pFX->SetFieldType(CDaoFieldExchange::param);
    DFX_Text(pFX, "CourseIDParam", m_strCourseIDParam);
    

    DoFieldExchange recognizes two kinds of fields: columns and parameters. The call to the CDaoFieldExchange member function SetFieldType indicates what kind of field(s) follow in the DFX function calls. In this example, there is one parameter: m_strCourseIDParam.

    The name of the column for the parameter in the DFX_Text call — “CourseIDParam” — is arbitrary; you can provide any name you want.

  3. Save your work.

Note   No code is added during the next two procedures; the code already exists in the previous code block.

To specify a parameterized filter

CourseID is a column (field) name, and CourseIDParam is a named parameter associated with the column. Its value will be substituted at run time. DAO parameters are always named, rather than positional, as in most ODBC code. If you have more than one parameter in m_strFilter, such as:

m_pSet->m_strFilter = "CourseID = CourseIDParam AND SectionNo = SectionNoParam";

you must make multiple RFX calls after the call to:

pFX->SetFieldType(CDaoFieldExchange::param); 

To supply the run-time parameter value

Note   The technique just described for parameterizing a query is not the only approach available. The MFC DAO classes provide two alternative ways to manage recordsets, including any parameters you might give them. The approach described here relies on the wizards and uses the DAO record field exchange (DFX) mechanism to move data between the database and the recordset and to manage parameters. The alternative approach uses a different mechanism, called dynamic binding.