DAO Recordset: Using Aggregate SQL Functions with MFC DAO Classes

|  Overview |  How Do I |  FAQ |  Sample |  |  ODBC Driver List

With DAO records, you can't use the techniques described in the article Recordset: Obtaining SUMs and Other Aggregate Results (ODBC), which works for the MFC ODBC classes only. However, there are two methods for retrieving information from aggregate SQL functions using the MFC DAO classes:

• Use CDaoRecordset::GetFieldValue.

• Modify a CDaoRecordset-derived class by:

  • Changing the SQL statement used.

  • Placing column alias names in DFX calls of the recordset's DoFieldExchange member function.

Retrieve SQL Values with CDaoRecordset::GetFieldValue

allows you to use a CDaoRecordset object without deriving a class from it. You can retrieve records from an SQL statement that contains an aggregate function with GetFieldValue.

For example, if you have a student database that contains the names of students and test scores for each, and you want the average score for each student (a kind of aggregate value), you can use the following 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("student name");
     varAvg=  rs.GetFieldValue("AvgScore");
// We know the return values are BSTR and VT_R8 types.
// If we didn't know, we would have to look at the vt
// member of COleVariant to see what type the data is.

// Prints the data to output window of 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 contains the average score for each student.

For more on DAO and SQL, see the article DAO Queries: SQL for DAO.

Using a CDaoRecordset-Derived Class to Retrieve SQL Values

You can use a CDaoRecordset-derived class to retrieve the results of an SQL statement with an aggregate SQL function, as follows:

• Explicitly specify the full SQL statement as the second argument of the Open call or specify the full SQL statement in the GetDefaultSQL function. Then, in the SQL statement, specify a column alias for the functions that will contain the results of aggregate SQL functions such as AVG or SUM.

  For example (assuming you have defined a recordset rs):

    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 function of the CDaoRecordset-derived class so that it uses the alias names. For more on the DFX functions, see in the Class Library Reference.

  For example:

    void CMyRecordset::DoFieldExchange(CDaoFieldExchange* pFX)
    {
    ...
       DFX_Text(pFX, _T("[student name]"), m_student_name);
       DFX_Double(pFX, _T("[AvgScore]"), m_avg_score);
       ...
    }
  

With this technique, you do not need to work with COleVariant objects as you do when retrieving SQL values with CDaoRecordset::GetFieldValue.

After the recordset has been modified, 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 [Student Name]"));
  while (!rs.IsEOF())
  {
     TRACE(_T("%s\n%f\n"), (LPCSTR)rs.m_student_name,
           rs.m_test_score);
     rs.MoveNext();
  }
  rs.Close();

See Also   DAO Queries: SQL for DAO, Recordset: Obtaining SUMs and Other Aggregate Results (ODBC)