TADOQuery 过滤器和表达式始终为 true
我正在尝试从 TADOQuery 中过滤一些记录。我将过滤属性设置为 true，当我将过滤器设置为 field='value' 时，一切正常。我想通过附加
<space>AND field='value'
一个始终为 true 的值来动态构建这个过滤器，我认为 1=1 就可以了。因此，我将 1=1 作为默认过滤器，然后根据需要将 AND field='value' 附加到它。
然而，这是行不通的。错误消息如下：
参数类型错误、超出可接受范围或相互冲突。
谁能告诉我我可以使用什么作为此过滤器的多功能始终为真表达式？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我想这是不言而喻的，但这是否有效取决于 OLE DB 提供程序。当您在现有记录集上设置过滤器时，它最终会通过不同的 OLE DB 接口（如果我没记错的话，为 IViewFilter）。因此，即使过滤器在 SQL 语句的 WHERE 子句中工作，也并不一定意味着它将作为过滤器工作。您设置的过滤器最终会被解析为组件片段，然后传递到 OLE DB 接口。提供者的实现可能不需要“常量=常量”形式的过滤器。作为解决方法，您可以尝试将其全部设置在 SQL 语句的 WHERE 子句中。
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.
如果您不过滤某些内容，则必须将“Filtered”属性设置为 False；如果您希望过滤结果集，则必须将其设置为 True 以及您的条件。
不过，我会动态构建正确的 SQL 属性，以便您始终准确地知道正在发送到数据库的内容（并且您确定程序只接收您想要的那些记录）。
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).
1=1 技巧在查询的 where 子句中工作正常，但在过滤属性中不起作用。如果要禁用过滤器，请将filtered设置为false，然后将返回所有记录。
过滤的问题在于它是在客户端完成的。如果您使用的是 SQL Server 等数据库引擎，并且希望过滤大量记录，那么最好通过更改 SQL 查询来实现，这将允许数据库服务器仅返回所请求的记录。请记住先关闭 TAdoQuery，更改 SQL 然后重新打开。
我用来避免返回整个数据集（用于大型数据集）的一个技巧是考虑我想要显示的最大记录数，然后使用 TOP SQL 语法返回比我想要显示的记录数多 'n ' ...如果达到该数字，我会通知用户返回了超过 n-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.
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.