65.9K
CodeProject is changing. Read more.
Home

A simple count() query class

starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.04/5 (8 votes)

Aug 17, 2004

viewsIcon

36670

downloadIcon

423

A class to easily do count() queries.

Introduction

This simple class uses the basic CRecordset to implement count() queries. It's not too hard to do it by issuing SQL statements, it's not necessarily that hard to catch the return value. This class just makes it easier.

Background

I wrote this as part of a much larger database application. I've re-used the code several times since, so I figured it must be of use to others, too.

Using the code

To use the code, just add the code and header files to your project. You need to edit one line in countset.cpp:

CString CCountSet::GetDefaultConnect()
{
    return _T("ODBC;DSN=WinTAS");  // put your own DSN in here
}

Then just #include countset.h in the header file you want to use it in, and set it up as per the example:

CCountSet countSet;
countSet.sSelect="some SQL statement returning a numeric value, 
    such as MAX(age)";  // delete this line to use the next one
countSet.sDistinct="field name for select(distinct <fieldname>) 
    statement";  // delete this line for select(*);
countSet.sCheckTable="table name you're counting in"; 
// this is just appended to the SQL,
// so could include JOIN statements

countSet.sCheckField="WHERE condition for SQL statement";
// delete for no WHERE clause

countSet.Open();
// never Requery() it: Close() and Open() if you want to re-use it.

// return value is in countSet.m_TOTAL;

So, there it is - declare an instance of CCountSet, set some variables, and you're away. For example, to generate the SQL statement count(max(age)) from client, you'd write:

CCountSet countSet;
CString sText;

countSet.sSelect="MAX(age)";
countSet.sCheckTable="client";
countSet.Open();
sText.Format("The oldest client is %d",countSet.m_TOTAL);
AfxMessageBox(sText);