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
m_strCourseIDParam
, just before the //Overrides section, after the //}}AFX_FIELD
line: CString m_strCourseIDParam;
To bind the parameter data member to the recordset
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 = "";
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.
To specify a parameterized filter
m_pSet->m_strFilter = "CourseID = ?";
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
m_pSet->m_strCourseIDParam = pDoc->m_courseSet.m_CourseID;
This sets the parameter value to be the first course record retrieved from the CCourseSet
recordset. All parameter values must be assigned before calling CRecordset::Open (or CRecordView::OnInitialUpdate), or as you will see later, before calling CRecordset::Requery.