TADOQuery filter and an expression always true.

Question

I am trying to filter some records from a TADOQuery. I set the filtered property to true and when I set the filter to field='value', all works fine. I would like to dynamically build this filter by appending

<space>AND field='value'

to a value always true, and I thought 1=1 would do the trick. So I would have 1=1 as the default filter and then just append AND field='value' to it as necessary.

This, however, does not work. The error message reads:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Could anyone please tell me what could I use as a versatile always-true expression for this filter?

Answer 1

I suppose it goes without saying, but it depends on the OLE DB provider whether or not this works. When you set a filter on an existing record set, it ends up going through a different OLE DB interface (IViewFilter if I remember correctly). So even if a filter works in a WHERE clause on an SQL statement, it does not necessarily mean that it will work as a filter. The filter that you set ends up getting parsed apart into the component pieces and then passed to the OLE DB interface. It may be that the provider's implementation is not expecting a filter of the form "constant = constant". As a workaround, you might try setting it all in the WHERE clause of the SQL statement.

Answer 2

You have to set the 'Filtered' property to False if you are not filtering something, and set it True and your condition when you want the resultset to be filtered.

I would dynamically build the correct SQL property though so that you always exactly know what is being send to the database (and you are sure that only those records you want is received by your program).

Answer 3

The 1=1 trick works fine in the where clause of a query, but not in the filtered property. If you want to disable the filter, set filtered to false and all records will be returned.

The problem with filtering is that it is done client side. If you are using a database engine such as SQL Server and expect to have a large set of records to filter, then your better served by changing the SQL Query which will allow the database server to return only the records requested. Just remember to close your TAdoQuery first, change the SQL then re-open.

A trick I use to avoid returning the entire dataset (used for large datasets) is to consider a maximum number of records I want to display, then use the TOP SQL Syntax to return one more than the number of records I wanted to display 'n' ...if I reach that number, then I notify the user that there were more than n-1 records returned and to adjust the search/filter criteria.