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

• The Microsoft Foundation Classes (MFC), included with:

      - Microsoft Visual C++ for Windows, versions 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."

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
   }

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

   SELECT name, phonenum from customertable where name = ?

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'

   Select name, phonenum from customertable where name = 'Dan' and
       zipcode=97439

STATUS