Microsoft KB Archive/111816

{|
 * width="100%"|

DOC: Changes to m_strFilter/m_strSort Don't Affect Requery

 * }

Q111816

-

The information in this article applies to:


 * The Microsoft Foundation Classes (MFC), included with:
 * Microsoft Visual C++, version 1.5

-

SYMPTOMS
The online Help for the CRecordset::Requery function incorrectly states the following:

"'For either a dynaset or a snapshot, call Requery any time you want to rebuild the recordset using a new filter or sort, or new parameter values. Set the new filter or sort property by assigning new values to m_strFilter and m_strSort before calling Requery. Set new parameters by assigning new values to parameter data members before calling Requery. If the filter and sort strings are unchanged, you can reuse the query, which improves performance.'" This is a documentation error. The Requery function isn't affected by changes in the sort (m_strSort) or filter (m_strFilter) variable of CRecordset. The Requery function is affected by parameterized filters. The use of parameters is demonstrated in Chapter 4 of the Enroll database tutorial (see "Database Classes," "Part 1: Database Tutorial" in the Visual C++ version 1.5 Books Online).

CAUSE
Below is the code for the CRecordset::Requery function:

  BOOL CRecordset::Requery {      RETCODE nRetCode;

ASSERT_VALID(this); ASSERT(IsOpen); // Can't requery until all pending Async operations have // completed. ASSERT(!m_pDatabase->InWaitForDataSource);

TRY {          // Shut down current query. AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_CLOSE));

// Now try to reexecute the SQL query. AFX_SQL_ASYNC(this, ::SQLExecute(m_hstmt)); if (!Check(nRetCode)) ThrowDBException(nRetCode);

m_nFieldsBound = 0; InitRecord; }      CATCH_ALL(e) {          Close; THROW_LAST; }      END_CATCH_ALL

return TRUE;   // all set } NOTE: The function merely does a SQLExecute call; it relies on the Open Database Connectivity (ODBC) application programming interface (API) function SQLPrepare to initialize the query earlier in the program in the CRecordset::Open function. SQLPrepare helps improve query speed by telling the ODBC driver that the query will always be of the same form; the structure of the SQL statement won't change. This is where SQL statement parameters are beneficial.

For example, suppose that you have the following SQL statement, which is used for an SQLPrepare call:

  SELECT name, phonenum from customertable where name = ? The question mark (?) is an ODBC defined placement holder. It permits the program to specify a new filter using the ODBC API SQLSetParam without changing or specifying a new SELECT statement later in the program. There is only one filter and the SELECT statement won't change. ODBC drivers can optimize performance because no re-parsing needs to be done when SQLExecute is called. The parsing of the SELECT statement is done once in the SQLPrepare call.

In the SELECT line above, the parameterized filter would be handled by assigning m_strFilter = "name=?", and then using a variable to store the data for the parameter. For more information, see the section titled "Filtering and Parameterizing the Recordset" in "Database Classes," "Part 1: Database Tutorial," Chapter 4 "Step 2: Using a Second Recordset" of the Visual C++ version 1.5 Books Online.

See "Part 1: Database Tutorial" in the "Database Classes" reference and the "ODBC API Programmer's Reference" in the Visual C++ version 1.5 Books Online for more information about ODBC SQL statement parameters, SQLPrepare and SQLExecute.

Because SQL parameters (for example, ? markers) are not permitted in SORT clauses, you cannot use the Requery function if you need to change the sorting order.

RESOLUTION
If the structure of the SQL statement isn't going to change, use ODBC SQL parameters, which are described in the section titled "Filtering and Parameterizing the Recordset" in Chapter 4 of the database tutorial included with Visual C++ version 1.5. If the structure of the SELECT statement is changed in the program, you must requery by calling Close and then Open for the CRecordset.

Here is an example of a SELECT statement that changes structure during a program. Suppose the original query for the CRecordset is:

  Select name, phonenum from customertable where name = 'Dan' This is accomplished by setting m_strFilter to "name='Dan'". Now, later in the application, you want to change the query so that it shows all "Dan"'s in a certain zip code. You might have:

  Select name, phonenum from customertable where name = 'Dan' and zipcode=97439 The structure of the query has changed because the WHERE clause now contains two conditions. In other words, the m_strFilter string would be "name = 'Dan' and zipcode=97439". To query, you would need to call CRecordset::Close and then CRecordset::Open, rather than Requery. ODBC SQL parameters wouldn't work because the SELECT statement can't be written to accept one filter and then two filters.

STATUS
The CRecordset::Requery behavior is by design. The Requery function is being reviewed and may be modified in future versions to reflect changes to m_strFilter and m_strSort.

Additional query words:

Keywords : kb16bitonly kbDatabase kbMFC kbODBC kbVC

Issue type :

Technology : kbAudDeveloper kbMFC