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

ID: Q111816

The information in this article applies to:

The Microsoft Foundation Classes (MFC), included with:
- Microsoft Visual C++ for Windows, 16-bit edition, 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 
Version           : 1.5 2.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: August 3, 1999