How Count Number of Records Returned by a SQL Query Using OleDb

Question

Is there an easy way to find the Number of Records returned by a call to the Microsoft OleDbDataReader, reading from an Access Database (JetEngine)?

I want to find the number of different "Teams" (whose Team Number is greater than 0) in the "RoundData" table in my Access database .

The following code correctly returns the Teams with a distinct team number greater than zero.

CmdStr = $"SELECT DISTINCT {Team} FROM [RoundData] WHERE {Team} > 0";

ReadCmd = new OleDbCommand( CmdStr, ResultsReportingOleDbConnection );

Reader = ReadCmd.ExecuteReader();

I can loop through the records returned, counting the number of them, but all I want is the number of them.

I can't use "SELECT COUNT(DISTINCT...) as the Jet Engine does not support this feature (I believe) .

Is there a SELECT COUNT() ... command string that I can use, I tried the following:-

CmdStr = $"SELECT Count() FROM [RoundData] WHERE {Team} = ( SELECT DISTINCT {Team} FROM [RoundData] WHERE {Team} > 0 )";

ReadCmd = new OleDbCommand( CmdStr, ResultsReportingOleDbConnection );

int X = (int)ReadCmd.ExecuteScalar();

But received the following error message:-

At most one record can be returned by this subquery

Answer 1

Your last try is on the right track but your logic is wrong. I havent tested the syntax in Access but it would be more like this:

SELECT Count() FROM
(
    SELECT DISTINCT {Team} FROM [RoundData] WHERE {Team} > 0
)

In SQL Server you would have to name the subquery result, e.g.

SELECT Count() FROM
(
    SELECT DISTINCT {Team} FROM [RoundData] WHERE {Team} > 0
) A

but I'm not sure that Jet requires that.

You would then call ExecuteScalar to get that result value.

Answer 2

I have found that the following code works:-

            CmdStr = $"SELECT Count(*) (FROM SELECT DISTINCT {Direction} FROM [RoundData] WHERE {Direction} > 0)";                                                                          
            ReadCmd = new OleDbCommand( CmdStr, ResultsReportingOleDbConnection );
            int X = (int)ReadCmd.ExecuteScalar();

However if I omit the * from Count(*), the query fails.