Microsoft KB Archive/141904

= How to use SQL aggregate functions with the MFC DAO Classes =

Article ID: 141904

Article Last Modified on 11/21/2006

-

APPLIES TO

 Microsoft Foundation Class Library 4.2, when used with:  Microsoft Visual C++ 4.0 Standard Edition

 Microsoft Visual C++ 4.1 Subscription

 Microsoft Visual C++ 4.2 Enterprise Edition

 Microsoft Visual C++ 4.2 Professional Edition</li></ul>

 Microsoft Visual C++ 4.2 Enterprise Edition</li></ul>

 Microsoft Visual C++ 4.2 Professional Edition</li></ul>

 Microsoft Visual C++ 5.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 5.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 6.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Standard Edition</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q141904

<div class="summary_section">

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.

<div class="moreinformation_section">

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; NOTE: 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]")); </li> <li> 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); ...  }                        </li></ul>

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; NOTE: There isn't a need to work with COleVariants unlike the GetFieldValue technique described previously.

Keywords: kbhowto kbdatabase kbcode KB141904

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.