与记录筛选器问题 [英] Problems with Recordset Filter

问题描述

我在与在传统ASP经典code对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。在我的管理用户界面，我有一个控件来显示所有项目或只应显示给最终用户：状态= 1 AND（END_DATE为空或END_DATE＆GT;日期（））

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() )

要实现该逻辑，我想：

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() & "#"

但是，这只是一个意外 - 它看起来像过滤条件是为了评估，所以我得到了我想要的结果。如果我更改为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() & "# )"

不过，当然这个逻辑是错误的 - 它显示活跃，但已过期的项目

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.

我似乎无法predict什么都会，不会工作，我读过的文档是预期的语法非常粗略（它不是纯粹的T-SQL！）的限制，等等。和我见过的所有例子最多有两个条件。我不认为我的条件是那么复杂。谁能告诉我，如果我想要做的是支持的，如果有更好的方式来做到这一点，或点我对COM prehensive文档和样本相匹配这样的逻辑是什么？

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 Recordset对象筛选器属性：

有是AND和OR之间没有precedence。条款可以括号内进行分组。但是，你不能组子句通过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:

（姓氏='史密斯'OR =姓氏
  琼斯）和名字='约翰'

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

相反，你会构造此
  作为过滤：

Instead, you would construct this filter as:

（姓氏='史密斯'AND名字=
  '约翰'）或

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

（姓氏='琼斯和名字=
  '约翰'）

(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屋！