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
我试图从TADOQuery过滤一些记录。我将filtered属性设置为true，当我将过滤器设置为field ='value'时，一切正常。我想通过追加来动态构建这个过滤器
<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.
一个值总是正确的，我认为1 = 1会成功。所以我会将1 = 1作为默认过滤器，然后根据需要将AND field ='value'附加到它。
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?
任何人都可以告诉我，我可以使用什么作为这个过滤器的多功能始终真实表达？
3 个解决方案
#1
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.
我想不言而喻，但这取决于OLE DB提供程序是否有效。当您在现有记录集上设置过滤器时，它最终会通过不同的OLE DB接口（如果我没记错的话，可以通过IViewFilter）。因此，即使过滤器在SQL语句的WHERE子句中工作，也不一定意味着它将作为过滤器使用。您设置的过滤器最终被解析为组件片段，然后传递给OLE DB接口。可能是提供者的实现不期望形式为“常量=常量”的过滤器。作为解决方法，您可以尝试在SQL语句的WHERE子句中设置所有内容。
#2
1
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.
如果不过滤某些内容，则必须将“Filtered”属性设置为False，并在需要过滤结果集时将其设置为True和条件。
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).
我会动态构建正确的SQL属性，以便您始终确切地知道发送到数据库的内容（并确保您的程序只接收您想要的那些记录）。
#3
1
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.
1 = 1技巧在查询的where子句中正常工作，但在过滤属性中没有。如果要禁用过滤器，请将filtered设置为false，并返回所有记录。
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.
过滤的问题在于它是客户端完成的。如果您正在使用SQL Server等数据库引擎并希望过滤大量记录，则可以通过更改SQL查询来更好地服务，这将允许数据库服务器仅返回请求的记录。只需记住先关闭TAdoQuery，然后更改SQL然后重新打开。
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.
我用来避免返回整个数据集（用于大型数据集）的技巧是考虑我想要显示的最大记录数，然后使用TOP SQL语法返回的数量超过我想要显示的记录数'n '...如果我达到那个号码，那么我会通知用户返回的记录超过n-1并调整搜索/过滤条件。
#1
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.
我想不言而喻，但这取决于OLE DB提供程序是否有效。当您在现有记录集上设置过滤器时，它最终会通过不同的OLE DB接口（如果我没记错的话，可以通过IViewFilter）。因此，即使过滤器在SQL语句的WHERE子句中工作，也不一定意味着它将作为过滤器使用。您设置的过滤器最终被解析为组件片段，然后传递给OLE DB接口。可能是提供者的实现不期望形式为“常量=常量”的过滤器。作为解决方法，您可以尝试在SQL语句的WHERE子句中设置所有内容。
#2
1
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.
如果不过滤某些内容，则必须将“Filtered”属性设置为False，并在需要过滤结果集时将其设置为True和条件。
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).
我会动态构建正确的SQL属性，以便您始终确切地知道发送到数据库的内容（并确保您的程序只接收您想要的那些记录）。
#3
1
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.
1 = 1技巧在查询的where子句中正常工作，但在过滤属性中没有。如果要禁用过滤器，请将filtered设置为false，并返回所有记录。
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.
过滤的问题在于它是客户端完成的。如果您正在使用SQL Server等数据库引擎并希望过滤大量记录，则可以通过更改SQL查询来更好地服务，这将允许数据库服务器仅返回请求的记录。只需记住先关闭TAdoQuery，然后更改SQL然后重新打开。
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.
我用来避免返回整个数据集（用于大型数据集）的技巧是考虑我想要显示的最大记录数，然后使用TOP SQL语法返回的数量超过我想要显示的记录数'n '...如果我达到那个号码，那么我会通知用户返回的记录超过n-1并调整搜索/过滤条件。