select count(*) as .... help please

[ed]

Hi all,
I'm trying to get a table's record/row count using the following piece of
code:


ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select count(*) as countrex from accountdata');
ADOQuery1.ExecSQL;
Label1.Caption := 'Total Records in table : ' +
IntToStr(ADOQuery1countrex.AsInteger);

Unfortunately, it returns 0 every time although I know that the table in
question has over 1,000 records. Incidentally, at design time, it works
fine and returns the correct result when I make the Query active.

Any help appreciated.

Regards,
ed

[Bill Todd]

Call the Open method, not ExecSQL. You must call Open for a SELECT.

[ed]

Thanks for your help Bill. I've corrected my code, but I still can't get
this to work properly.

As I understand it, the SQL statement 'select count(*) as countrex from
TABLEa' should return the correct result in countrex. I should then be
able to access this field (I have added it to ADOQuery1) and view the
result. However, I must be missing something (apart from knowledge!) as
this approach doesn't work as I expect.

If I add a second ADOQuery using the same SQL statement and link this to a
DBGrid, the result is displayed correctly in the first cell. So, I'm
perplexed!

Btw, I'm using Delphi 7 Pro and the database in question accessed via
ADO/ODBC. If you (or anyone else) can shed any further light on the
problem, I'd really appreciate it.

Regards,
ed

Post by Bill Todd
Call the Open method, not ExecSQL. You must call Open for a SELECT.
--
Bill Todd (TeamB)

[Del M]

you might have more than one sql statement in your adoquery which produces
multiple recordsets.

[Bill Todd]

That would be a neat trick if he really as calling SQL.Clear right
before calling SQL.Add as shown in his original message.<g>

[Del M]

Yes, but the use of ADOQuery1 leads me to believe he **may** be posting a
typed in sample and not the actual code snippet from his program. <g>

[Josh Fraser]

Post by Del M
Yes, but the use of ADOQuery1 leads me to believe he **may** be posting a
typed in sample and not the actual code snippet from his program. <g>

That or he has no clue about naming conventions :)

[Josh Fraser]

Well, people who don't post real code don't want real help. :)

Nope they are just tryin to get nice suckers to help with their homework :)

[Bill Todd]

Well, people who don't post real code don't want real help. :)

[Bill Todd]

Post by ed
Thanks for your help Bill. I've corrected my code, but I still can't
get this to work properly.
As I understand it, the SQL statement 'select count(*) as countrex
from TABLEa' should return the correct result in countrex. I should
then be able to access this field (I have added it to ADOQuery1) and
view the result. However, I must be missing something (apart from
knowledge!) as this approach doesn't work as I expect.
If I add a second ADOQuery using the same SQL statement and link this
to a DBGrid, the result is displayed correctly in the first cell.
So, I'm perplexed!

Why add a second ADOQuery? Connect the one that does not work to a
DBGrid or inspect the returned value in the debugger.

Is the following the actual code from your app?

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select count(*) as countrex from accountdata');
ADOQuery1.Open;
Label1.Caption := 'Total Records in table : ' +
IntToStr(ADOQuery1countrex.AsInteger);

You can change the last two lines to:

Label1.Caption := 'Total Records in table : ' +
ADOQuery1countrex.AsString;

What database are you using?