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.
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
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 DaoEnrol’s single parameter, m_strCourseIDParam
.
Place the following two lines of code after the line m_nDefaultType = dbOpenDynaset;
:
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(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.
Note No code is added during the next two procedures; the code already exists in the previous code block.
To specify a parameterized filter
m_pSet->m_strFilter = "CourseID = CourseIDParam";
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
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 CDaoRecordset::Open (or CDaoRecordView::OnInitialUpdate) or, as you will see later, before calling CDaoRecordset::Requery.
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.