Incorrect record count in ADOTable

[MLNorton]

I have an ADOTable that contains an ACCESS database of over 3, 000 records. In this database there are 284 records with a field named. ‘Password’ and 430 records with a field named ‘ExpYearMonDay”. I need to count the records containing each of thesr fields. I am using the following code:

Form_Main.ADOTable1.Filter := 'Passwords <> Null';
Form_Main.ADOTable1.Filtered := true;
Passwords := Form_Main.ADOTable1.RecordCount;

TodayStr := FormatDateTime('yyyymmdd',now);
TodaysDte := StrToInt(TodayStr);

Form_Main.ADOTable1.Filter := 'ExpYearMonDay >' + IntToStr(TodaysDte);
Form_Main.ADOTable1.Filtered := true;
Members := Form_Main.ADOTable1.RecordCount;Passwords is an integer value.

I get a result of 525 rather than the correct 284 for the Passworde but I get the CORRECT result for Members.

What is causing the incorrect count for Password?

 

[DjangMan]

Googling TDataset Refresh reveals:

http://docwiki.embarcadero.com/VCL/en/DB.TDataSet.Refresh[/URL]]
Call Refresh to ensure that an application has the latest data from a database. For example, when an application turns off filtering for a dataset, it should immediately call Refresh to display all records in the dataset, not just those that used to meet the filter condition.

See if that helps.

 

[majlumbo]

A different method to get the number of records that meets a condition is to use the sql COUNT(fieldname) approach.
I've read that the property recordcount is unreliable, and so I don't use it for anything other than to check if recordcount > 0 (ensures that the query did not return an empty set).

SQL count, by definition if you put an actual field name in the parenthesis, will only count records that do not have null values, as opposed to COUNT(*) which will count every record that meets the where clause. This means you don't actually need to add the password <> null in the where clause.

Additionally, I have never worked with Access, so I cannot tell you that password <> null is or isn't a valid check, but I can tell you this will not work 'as expected' in an RDBMS. You'd need to use 'WHERE PASSWORD IS NOT NULL' instead. Any attempt to compare anything to a null value will always fail. By definition, NULL means no value/unknown, so the database has no basis to compare it to anything, even to the point of comparing it to yet another null value...

 

[VintageWine]

I agree with majlimbo: better to do this as a query on the database rather than load in a dataset and count in Delphi.

However, if it has to be done in the database these suggestions may help.
- Check for existence of blank strings in your data (a string consisting of a space is not a null).
- Filter is implemented independently and inconsistently in each database flavour. So syntax is different between TClientDataset.Filter and TADODatset.Filter. I have found bugs in TClientDataset.Filter. It is worth experimenting with different syntaxes of Filter.