HOWTO: Use SQL Aggregrate Functions with the MFC DAO Classes

Last reviewed: June 26, 1997
Article ID: Q141904
The information in this article applies to:
  • The Microsoft Foundation Classes (MFC) included with: - Microsoft Visual C++, 32-bit Edition, versions 4.0, 4.1, 4.1b, 4.2,

         4.2b, 5.0
    

SUMMARY

There are two ways to use SQL aggregate functions to retrieve information with the MFC DAO classes. One method uses the GetFieldValue() member function of the CDaoRecordset class to retrieve the values. The other involves modifying a CDaoRecordset-derived class by changing the SQL statement used to open the recordset and placing column alias names in the DFX calls of the recordset's DoFieldExchange() member function.

NOTE: You can't use the technique described in the MFC Encylopedia article "Recordset: Obtaining SUMs and Other Aggregate Results (ODBC)" to work with SQL aggregate functions under DAO. That article pertains to the MFC ODBC classes only.

MORE INFORMATION

SQL Aggregate Functions Using CDaoRecordset::GetFieldValue()

The Visual C++ documentation discusses how to use the CDaoRecordset::GetFieldValue() member function. This function allows you to use a CDaoRecordset object without deriving from it. One technique for retrieving records returned from a SQL statement that contains an aggregate function is to use this GetFieldValue() member function to retrieve the values.

For example, assume that you have a student database that contains the name of the student and scores from tests they have taken. If you want the average score for each of the students, you could use this code:

   CDaoDatabase db;
   db.Open(_T("d:\\scores.mdb"));

   CDaoRecordset rs(&db);
   rs.Open(dbOpenDynaset,
      _T("SELECT [Student Name], AVG([Test Score]) AS AvgScore "
         "FROM Scores GROUP BY [Student Name]"));

   while (!rs.IsEOF())
   {
      COleVariant varName;
      COleVariant varAvg;
      varName = rs.GetFieldValue(_T("student name"));
      varAvg = rs.GetFieldValue(_T("AvgScore"));

      // You know that the return values are BSTR and VT_R8 types.
      // if you didn't know, you would have to do some checking
      // here. Look at the vt member of the COleVariant to see what
      // type the data is.

      // Do something with the data. This sample prints the
      // information to the output window of the debugger.
      TRACE(_T("%s\n%f\n"), V_BSTRT(&varName), V_R8(&varAvg));
      rs.MoveNext();
   }
   rs.Close();
   db.Close();

Notice that the alias name "AvgScore" is used for the column that will contain the average score for each student.

SQL Aggregate Functions Using a CDaoRecordset-Derived Class

To create a CDaoRecordset-derived class that retrieves the results of a SQL statement with an SQL aggregate function, you need to:

  • Explicitly specify the full SQL statement as the second argument of the Open() call or return the full SQL statement from the GetDefaultSQL() member function. In the SQL statement, specify a column alias for the functions that will contain the results of SQL aggregate functions such as AVG() or SUM(). For example:

    rs.Open(dbOpenDynaset,

           _T("SELECT [Student Name], AVG([Test Score]) AS AvgScore "
    
              "FROM Scores GROUP BY [Student Name]"));
    
    
  • Modify the DFX functions in the DoFieldExchange() member function of the CDaoRecordset-derived class so that it uses the alias names. For example:

       void CMyRecordset::DoFieldExchange(CDaoFieldExchange* pFX)
       {
          ...
          DFX_Text(pFX, _T("[student name]"), m_student_name);
          DFX_Double(pFX, _T("[AvgScore]"), m_avg_score);
          ...
       }
    
    
Using this second technique makes the code a little cleaner. For example, after the CDaoRecordset-derived class has been modified as described above, here is what the code might look like:

   CAvgSet rs;
   rs.Open(dbOpenDynaset,
      _T("SELECT [Student Name], AVG([Test Score]) AS AvgScore "
         "FROM Scores GROUP BY [Sudent Name]"));
   while (!rs.IsEOF())
   {
      TRACE(_T("%s\n%f\n"), (LPCTSTR)rs.m_student_name, rs.m_test_score);
      rs.MoveNext();
   }
   rs.Close();

Notice that there isn't a need to work with COleVariants unlike the GetFieldValue() technique described previously.


Keywords : kbprg MfcDAO
Technology : kbMfc
Version : 4.0 4.1 4.1b 4.2 4.2b 5.0
Platform : NT WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 26, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.