“喜欢"运算符可在MS Access中工作，但不能在ADO中工作

问题描述

我正在尝试使用带星号的喜欢"来过滤记录，当使用Access 2010返回许多记录时，它可以工作.我很困惑为什么与ADO一起使用时不返回任何内容.该代码包括多个表和列，因此要进行故障排除，我做了一个简单的查询.这是代码:

I'm trying to filter records using "Like" with asterisks, it works when using Access 2010 returning many records. I'm stumped why it returns nothing when used with ADO. The code includes multiple tables and columns so to troubleshoot I made a simple query. Here's the code:

strsql = "SELECT tproducts.Prod_Name FROM tproducts " _
& " WHERE tproducts.Prod_Name Like " & Chr(34) & "SO*" & Chr(34)

Set cn = New ADODB.Connection
cn = connString
cn.Open
Set rs = New ADODB.Recordset
rs.Open strsql, cn, adOpenStatic, adLockOptimistic

' test here
iRecCount = rs.RecordCount
rs.MoveFirst

Recordcount返回-1.

Recordcount returns -1.

当赞"替换为等于"时，它将返回正确的记录，因此我确定它能够连接到数据库，例如:

When "Like" is replaced by "equals" it returns correct record so I'm sure it's able to connect to the database, for example:

strsql = "SELECT tproducts.Prod_Name FROM tproducts " _
& " WHERE tproducts.Prod_Name = " & Chr(34) & "SONY Vaio SVD13213CXB" & Chr(34)

在ADO中使用Like运算符是否有一种特殊的方法?

Is there a special way to use the Like operator in ADO?

与使用赞"一样，我还能通过哪些其他方式过滤以得到结果?例如，要查找所有" SVD "产品?

What other ways can I filter to give results same as using "Like"? For example, to find all "SVD" products?

推荐答案

在MS Access中，通配符几乎总是*，在MS Access之外通配符几乎总是％，所以

In MS Access, the wildcard is nearly always *, outside of MS Access it is nearly always %, so

str = "SELECT tproducts.Prod_Name FROM tproducts) " _
& " WHERE tproducts.Prod_Name Like ""SO%"""

但是，我强烈建议您使用参数，以避免出现许多严重的问题.

However, I strongly recommend that you move to parameters to avoid a number of serious problems.

到目前为止，DAO是ACE/Jet的更好选择(粗略示例)

DAO is by far the better choice for ACE / Jet ( rough example Loop table rows in Access, with or without use of Private Const )

这篇关于“喜欢"运算符可在MS Access中工作，但不能在ADO中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！