CDatabase CRecordset - How to get the number of rows?

Discussion:

(too old to reply)

David++

2005-06-09 15:19:02 UTC

Hi there,

I'm using Visual C++6 and MFC to connect to a database. So far I have just
been able to connect to the database. I want to count the number of rows in
the database so I can see if there is any data in it. All I want to do is get
the number of rows if possible. I dont need to pass any of the actual data
(if there is any). I've been using the CDatabase class to connect to the
Database and have tried a few things using the CRecordset class to try and
return the number of rows. Here is a cut down version of my code in which I
have removed all the try catch blocks for clarity -

CDatabase db;
db.Open(connectionString);

CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly,_T( "SELECT COUNT (*) FROM Records" ) );

long recordCount = 0;
recordCount = rs.GetRecordCount();

However, when I check the value of recordCount it always returns 1 even when
there are none or many rows in the table.

Any tips on how to do this is much appreciated!

Thanks,

David

Bill Thompson

2005-06-09 20:23:52 UTC

Permalink

Post by David++
Hi there,
I'm using Visual C++6 and MFC to connect to a database. So far I have just
been able to connect to the database. I want to count the number of rows in
the database so I can see if there is any data in it. All I want to do is get
the number of rows if possible. I dont need to pass any of the actual data
(if there is any). I've been using the CDatabase class to connect to the
Database and have tried a few things using the CRecordset class to try and
return the number of rows. Here is a cut down version of my code in which I
have removed all the try catch blocks for clarity -
CDatabase db;
db.Open(connectionString);
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly,_T( "SELECT COUNT (*) FROM Records" ) );
long recordCount = 0;
recordCount = rs.GetRecordCount();
However, when I check the value of recordCount it always returns 1 even when
there are none or many rows in the table.
Any tips on how to do this is much appreciated!
Thanks,
David

the GetRecordCount() indicates you have to perform a MoveLast() prior to
getting the record count.

Obviously this is a problem for a forward only recordset. Can't suggest
anything else without knowing more details.

I understand that DAO will become deprecated at some point and that ADO is
the way to go.

Gert

2005-06-09 20:44:18 UTC

Permalink

Post by David++
rs.Open(CRecordset::forwardOnly,_T( "SELECT COUNT (*) FROM Records" ) );

COUNT(*) will always return 1 row with 1 column, and that doesn't depend
on the number of rows inside Records - the VALUE of the one column WILL
depend on the number of records. So the answer of "1" is correct.

Gert

David++

2005-06-09 21:29:02 UTC

Permalink

Hi,

Thanks for both your answers. I've changed my code a bit to this -

CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly,_T( "SELECT * FROM Records" ) );

int count = 0;
while (!rs.IsEOF())
{
count += 1;
rs.MoveNext();
}

I've now dropped the COUNT (*) and have used just * in the SQL Statement. It
seems to be working a little better but i cant fully check it as I'm on my
home computer now. Interestingly, if I use the MoveLast() function I get a
runtime Debug Assertion failure?

Well, thanks once again for your help and views. I'll post any outcomes I
make,

Best,
David

Roy Fine

2005-06-10 02:25:04 UTC

Permalink

Post by David++
Hi,
Thanks for both your answers. I've changed my code a bit to this -
CRecordset rs(&db);
rs.Open(CRecordset::forwardOnly,_T( "SELECT * FROM Records" ) );
int count = 0;
while (!rs.IsEOF())
{
count += 1;
rs.MoveNext();
}
I've now dropped the COUNT (*) and have used just * in the SQL Statement. It
seems to be working a little better but i cant fully check it as I'm on my
home computer now. Interestingly, if I use the MoveLast() function I get a
runtime Debug Assertion failure?
Well, thanks once again for your help and views. I'll post any outcomes I
make,
Best,
David

What you have is most inefficient method available for determining the
number of rows in a cursor - by fetching each row, and incrementing a
counter. Consider the impact of moving the database to a host on the
network (ever better, to a host on an internet). In this case, network
delays kill performance.

Consider something like this (which pushes most of the work to the database
server and fetches one row with one field):

CRecordset rs(&db);
rs.Open(CRecordset::ForwardOnly,"Select count(*) from
Records",CRecordset::ReadOnly);
CDBVariant vtval;
rs.GetFieldValue((short)0,vtval);
int rowcount = vtval.m_lVal;
rs.Close();

regards
Roy Fine

David++

2005-06-10 12:48:03 UTC

Permalink

Ok, I tried this way but now instead of getting 3 rows returned (which is how
many rows there are in my table), I now get 14549040 rows returned? Hmm, any
ideas?

Thanks,

David

Post by Roy Fine
Consider something like this (which pushes most of the work to the database
CRecordset rs(&db);
rs.Open(CRecordset::ForwardOnly,"Select count(*) from
Records",CRecordset::ReadOnly);
CDBVariant vtval;
rs.GetFieldValue((short)0,vtval);
int rowcount = vtval.m_lVal;
rs.Close();
regards
Roy Fine

Roy Fine

2005-06-10 12:57:38 UTC

Permalink

David

show some code -- the count function is a single row function - it can not
return anything but ONE row with one scalar type.

the snip that I provided has no error checking - specifically check the
datatype of the variant - and if necessary convert it to a long/integer.

roy

Post by David++
Ok, I tried this way but now instead of getting 3 rows returned (which is how
many rows there are in my table), I now get 14549040 rows returned? Hmm, any
ideas?
Thanks,
David

David++

2005-06-10 13:44:06 UTC

Permalink

Hi Roy,

This is the code I have so far -

CRecordset rs(&db);

rs.Open(CRecordset::forwardOnly,"SELECT * FROM Records",CRecordset::readOnly);

CDBVariant vtval;
rs.GetFieldValue((short)0,vtval);
int rowcount = vtval.m_lVal;

char buffer[10];
_itoa(rowcount,buffer,10);

FILE *f;
f = fopen("Rows.txt","w");
fprintf(f,"Rows: ");
fprintf(f, buffer);

fclose(f);
rs.Close();

I'm printing the result to a text file for debugging purposes. The database
I'm using was made in Access and the fields are all of type 'Text' so I guess
its returning a char here? That may be the problem

Thanks again,

David

Post by Roy Fine
David
show some code -- the count function is a single row function - it can not
return anything but ONE row with one scalar type.
the snip that I provided has no error checking - specifically check the
datatype of the variant - and if necessary convert it to a long/integer.
roy

Post by David++
Ok, I tried this way but now instead of getting 3 rows returned (which is

how

Post by David++
many rows there are in my table), I now get 14549040 rows returned? Hmm,

any

Post by David++
ideas?
Thanks,
David

Post by Roy Fine
Consider something like this (which pushes most of the work to the

database