Problem Calculating the number of records in query results

[csbrmg]

I seem to have a problem traversing a dataset of a TSQLquery(DBEXpress) datatype because i am using sql server as my database the recordcount property does not work does anyone know of any other way to deterimine the number of records in a query when using sql server

 

[LucieLastic]

hi

What about :

select count(*) as RecordCount, fieldname... from Mytable

lou

 

[lespaul]

If you don't want to run a separate query for the count, I usually declare an integer variable then:

With query do
begin
While not eof do
begin
inc(i);
next;
end;
end;

Then I can use the i in a loop and know how many records there are.

HTH



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LucieLastic]

hi

In case my orig. post wasn't clear... just add the count(*) to the your normal query as an extra field - see my example in original post. This way you let the server do the counting whilst it's retrieving your dataset.

lou

 

[lespaul]

That's a great idea! I hadn't thought of that, but you have to use the group by clause when using an aggregate function.

So it would actually be:

SELECT COUNT(*), FIELD1, FIELD2, FIELD3 FROM TABLE GROUP BY FIELD1, FIELD2, FIELD3

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[lespaul]

So I tried that, lou, and it didn't work! It returned a count of 1 for each record. To get it to work I had to do:


SELECT (SELECT COUNT(*) FROM TABLE WHERE CONDITIONS) AS COUNTER, FIELD1, FIELD2 FROM TABLE WHERE CONDITIONS GROUP BY FIELD1, FIELD2

and it works, but it takes a few extra seconds to process. In terms of performance I think either the count in the SQL or the loop in Delphi probably takes about the same amount of time.

Leslie

 

[earlrainer]

the record count property can be trusted only to show 2 values correctly i.e if it has no records then it will always be 0 , but if there are records it neednot necessarily show the number of records ,mostly it shows -1.

so the only way I think to get the record count is the onle Les has suggested (and the ones we use also), is to loop through the records while incrementing an integer variable.

but make sure ,that if any controls use the query as a dataset ,then to disable the controls to prevent afterscroll\beforescroll events firing (i.e if they are written)

Weez, the count(*) always leads to more complicated queries , because of the groupings involved.