Setting Up the Parameter

Enroll 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 by supplying a new m_strFilter value before calling Open. This works but is somewhat inefficient, because the framework has to completely reconstruct and invoke 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.

In order to parameterize the filter, you will 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. From FileView, open file SectionSet.h.

  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. From ClassView, jump to the CSectionSet constructor, and initialize the parameter count variable, m_nParams, which by default is zero. Also initialize Enroll’s single parameter, m_strCourseIDParam.

    Place the following two lines of code after the line //}}AFX_FIELD_INIT:

    m_nParams = 1;
    m_strCourseIDParam = "";
    
  2. From ClassView, jump 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(CFieldExchange::param);
    RFX_Text(pFX, "CourseIDParam", m_strCourseIDParam);
    

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

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

  3. Save your work.

To specify a parameterized filter

The question mark “?” indicates where the parameter value will be substituted at run time. If you have more than one parameter in your m_strFilter, such as:

m_pSet->m_strFilter = "CourseID = ? AND SectionNo = ?";

you must make multiple RFX calls after the call to:

pFX->SetFieldType(CFieldExchange::param); 

You must make the RFX calls for multiple parameters in exactly the same order as the question marks in the m_strFilter and/or m_strSort.

Note   If you have both a filter and a sort with parameters, specify the filter parameters first, then the sort parameters. Not all ODBC drivers permit parameters on a sort. Consult the Help file for your ODBC driver.

To supply the run-time parameter value