Home | Overview | How Do I | FAQ | Sample | Tutorial | ODBC Driver List
This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.
Sometimes you’d like to be able to select records at run time, using information you’ve calculated or obtained from your end-user. Recordset parameters let you accomplish that goal.
This article explains:
A parameterized recordset lets you pass parameter information at run time. This has two valuable effects:
When you call Open to run the query, the recordset uses the parameter information to complete its SQL SELECT statement. You can parameterize any recordset.
Typical uses for parameters include:
To pass parameters to a stored procedure, you must specify a complete custom ODBC CALL statement — with parameter placeholders — when you call Open, overriding the recordset’s default SQL statement. See CRecordset::Open in the Class Library Reference and the articles SQL: Customizing Your Recordset’s SQL Statement (ODBC) and Recordset: Declaring a Class for a Predefined Query (ODBC).
For example, each time your end-user looks up information for a particular student in the student registration database, you can specify the student’s name or ID as a parameter obtained from the user. Then, when you call your recordset’s Requery member function, the query selects only that student’s record.
Your recordset’s filter string, stored in m_strFilter, might look like this:
"StudentID = ?"
Suppose you obtain the student ID in the variable strInputID
. When you set a parameter to strInputID
(for example, the student ID 100) the value of the variable is bound to the parameter placeholder represented by the “?” in the filter string.
Assign the parameter value as follows:
strInputID = "100";
...
m_strParam = strInputID;
Note that you would not want to set up a filter string this way:
m_strFilter = "StudentID = 100"; // 100 is incorrectly quoted
// for some drivers
For a discussion of how to use quotes correctly for filter strings, see the article Recordset: Filtering Records (ODBC).
The parameter value is different each time you requery the recordset for a new student ID.
Tip Using a parameter is more efficient than simply a filter. For a parameterized recordset, the database must process an SQL SELECT statement only once. For a filtered recordset without parameters, the SELECT statement must be processed each time you Requery with a new filter value.
For more information about filters, see the article Recordset: Filtering Records (ODBC).
Note This section applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you are using bulk row fetching, implementing parameters is a similar process. For more information, see the article Recordset: Fetching Records in Bulk (ODBC).
Before you create your recordset class, determine what parameters you need, what their data types are, and how the recordset will use them.
To parameterize a recordset class
class CStudentSet : public CRecordset
{
// Field/Param Data
//{{AFX_FIELD(CStudentSet, CRecordset)
CString m_strFirstName;
CString m_strLastName;
CString m_strStudentID;
CString m_strGradYear;
//}}AFX_FIELD
CString m_strGradYrParam;
};
ClassWizard writes field data members inside the “//{{AFX_FIELD” comment brackets. You add your parameter data members outside the comment brackets. The convention is to append the word “Param” to each name.
pFX->SetFieldType( CFieldExchange::param );
// RFX calls for parameter data members
For information, see The Recordset Constructor in the article Record Field Exchange: Working with the Wizard Code.
At run time, “?” placeholders are filled, in order, by the parameter values you pass. The first parameter data member set after the SetFieldType call replaces the first “?” in the SQL string, the second parameter data member replaces the second “?”, and so on.
Important Parameter order is important: the order of RFX calls for parameters in your DoFieldExchange
function must match the order of the parameter placeholders in your SQL string.
Tip The most likely string to work with is the string you specify (if any) for the class’s m_strFilter data member, but some ODBC drivers may allow parameters in other SQL clauses.
You must specify parameter values before you call Open (for a new recordset object) or Requery (for an existing one).
To pass parameter values to a recordset object at run time
For example, suppose you want to specify a filter string for your recordset using information obtained at run time. Assume you have constructed a recordset of class CStudentSet
earlier — called rsStudent
s — and now want to requery it for a particular kind of student information.
// Set up a filter string with
// parameter placeholders
rsStudents.m_strFilter = "GradYear <= ?";
// Obtain or calculate parameter values
// to pass--simply assigned here
CString strGradYear = GetCurrentAcademicYear( );
// Assign the values to parameter data members
rsStudents.m_strGradYrParam = strGradYear;
// Run the query
if( !rsStudents.Requery( ) )
return FALSE;
The recordset contains records for those students whose records meet the conditions specified by the filter, which was constructed from run-time parameters. In this case, the recordset contains records for all senior students.
Note If needed, you can set the value of a parameter data member to Null, using SetParamNull. You can likewise check whether a parameter data member is Null, using IsFieldNull.
See Also Recordset: Adding, Updating, and Deleting Records (ODBC), Recordset: How Recordsets Select Records (ODBC)