Setting Up the Parameter

Article
09/15/2006

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:

Declare a parameter data member in the recordset’s header file
Bind the parameter data member to the recordset

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

Specify a parameterized filter
Supply the run-time parameter value

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

From FileView, open file SectionSet.h.
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

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 = "";
```
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.
Save your work.

To specify a parameterized filter

Before the call to the base class function CRecordset::Open, which is called by CRecordView::OnInitialUpdate, specify the parameterized filter, as shown in this line (which you’ve already added):
```
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

Assign the value to the previously bound parameter data member, as shown in the following line (which you’ve already added in the OnInitialUpdate function).
```
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.

Setting Up the Parameter

Additional resources