Home | Overview | How Do I | FAQ | Sample | Tutorial | 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:
DoFieldExchange
member function.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.
You can use a CDaoRecordset-derived class to retrieve the results of an SQL statement with an aggregate SQL function, as follows:
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]"));
DoFieldExchange
function of the CDaoRecordset-derived class so that it uses the alias names. For more on the DFX functions, see Record Field Exchange Functions 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)