记录集过滤器的问题 [英] Problems with Recordset Filter

问题描述

我在使用旧版 ASP 经典代码中的 ADO 记录集的过滤器时遇到了问题，我正在尝试了解我正在尝试执行的操作是否不受支持，或者我是否只是做错了.

I'm having trouble with a filter on an ADO Recordset in legacy ASP Classic code, and I'm trying to understand if what I'm trying to do is not supported, or if I'm just doing it wrong.

我有一个项目记录集，它们的状态为 1(活动)或 0(非活动)，以及可选的 End_Date.在我的管理用户界面中，我有一个控件来显示所有项目或仅显示应向最终用户显示的项目:Status = 1 AND (End_Date is null OR End_Date > Date())

I have a recordset of Items, and they have a Status of 1 (active) or 0 (inactive), and an optional End_Date. In my administrative user interface, I have a control to show all items or only those that should be displayed to end-users: Status = 1 AND ( End_Date is null OR End_Date > Date() )

为了实现这个逻辑，我尝试了:

To implement that logic, I tried:

rs.Filter = "Status = 1 AND ( End_Date = null OR End_Date > #" & Date() & "# )"

但我明白了

ADODB.Recordset (0x800A0BB9)
Unknown runtime error

经过多次鬼混，ADO 似乎不喜欢将 End_Date 条件与 AND 条件结合使用的分组括号.如果我把括号拿出来，这行得通:

After much fooling around, it seems that ADO doesn't like the grouping parens around the End_Date conditions in combination with the AND condition. If I take the parens out, this works:

rs.Filter = "Status = 1 AND End_Date = null OR End_Date > #" & Date() & "#"

但这只是一个意外——看起来过滤条件是按顺序评估的，所以我得到了我想要的结果.如果我将 AND 更改为 OR，则括号起作用:

But that's just an accident -- it looks like the filter conditions are evaluated in order, and so I get the results I want. If I change the AND to OR, the parens work:

rs.Filter = "Status = 1 OR ( End_Date = null OR End_Date > #" & Date() & "# )"

当然，这个逻辑是错误的——它显示的是 Active but expired items.

But of course that logic is wrong -- it shows Active but expired items.

奇怪的是，如果我移动条件，它会再次中断:

Strangely, if I move the conditions around, it breaks again:

rs.Filter = "End_Date = null OR Status = 1 AND End_Date > #" & Date() & "# "

由于相同的 ADODB 错误而崩溃.

crashes with the same ADODB error.

我似乎无法预测什么会起作用，什么不会起作用，而且我读过的文档对预期的语法(它不是纯 T-SQL！)、限制等以及所有我见过的例子最多有两个条件.我不认为我的条件那么复杂.谁能告诉我是否支持我正在尝试做的事情，是否有更好的方法，或者向我指出符合这种逻辑的综合文档和示例?

I can't seem to predict what will and won't work, and the docs I've read are very sketchy on the syntax expected (it's not pure T-SQL!), the limitations, etc. and all the examples I've seen have at most two conditions. I don't think my conditions are all that complex. Can anyone tell me if what I'm trying to do is supported, if there's a better way to do it, or point me to comprehensive docs and samples that match this kind of logic?

谢谢！

推荐答案

ADO 记录集对象过滤器属性:

AND 和 OR 之间没有优先级.子句可以在括号内分组.但是，您不能对通过 OR 连接的子句进行分组，然后使用 AND 将该组连接到另一个子句，如下所示:

There is no precedence between AND and OR. Clauses can be grouped within parentheses. However, you cannot group clauses joined by an OR and then join the group to another clause with an AND, like this:

(姓氏 = '史密斯' 或姓氏 ='琼斯') AND FirstName = '约翰'

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

相反，您将构建这个过滤为:

Instead, you would construct this filter as:

(姓氏 = '史密斯' AND 名字 ='约翰') 或

(LastName = 'Smith' AND FirstName = 'John') OR

(姓氏 = '琼斯' AND 名字 ='约翰')

(LastName = 'Jones' AND FirstName = 'John')

所以你必须像这样构建你的过滤器:

So you would have to construct your filter like this:

rs.Filter = "( Status = 1 AND End_Date = null ) OR ( Status = 1 AND End_Date > #" & Date() & "# )"

这篇关于记录集过滤器的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！