Ms Access 2010问题与C#中的where子句 [英] Ms Access 2010 issue with a where clause in C#

问题描述

对于Access 2010数据库，我有一个复杂的select语句，该语句使用多个LEFT JOIN语句从多个表中获取数据.该查询按预期工作，我得到了整个表.

I have a complex select statement for an Access 2010 database which grabs data from multiple tables using several LEFT JOIN statements. The query works as expected and I get the entire table.

所以现在我想添加搜索功能. 一种方法是在查询末尾添加WHERE子句，并引用JOIN ed表的文本字段之一，并将其​​与某些文本(WHERE [All Names].Name LIKE "*Mark*")进行比较.

So now I want to add search functionality. One way was to add a WHERE clause at the end of the query and reference one of the JOINed tables' text field and compare it against some text (WHERE [All Names].Name LIKE "*Mark*").

我尝试的第二个选项是select * from (**complex sql here**) where **condition**

Second option I tried was select * from (**complex sql here**) where **condition**

现在在两种情况下，当我的条件是像([ID]<15)这样简单的东西时，它就像一个超级按钮，但是当我将其更改为([Employee Name] LIKE "\*Mark\*")或选项1中的那个时，它会生成一个空数据表，就像请求通过，没有错误或异常，所有字段名称均存在，但不返回任何行.

Now in both cases, when my condition is something simple like ([ID]<15), it works like a charm, but when I change it to ([Employee Name] LIKE "\*Mark\*") or the one in option 1, it produces an empty data table as if the request goes through, there is no error or exception, all the field names are present, but no rows are returned.

但是，如果我使用调试器(或只是将其转储到文本文件中)捕获了生成的SQL字符串的完整字符串(任一种选择)，然后几乎没有任何更改，就将该字符串直接放入新的Access查询中，它工作正常，并返回名称包含"Mark"的几个字段

However, if I grab the full string of the generated SQL string (either option) using the debugger (or just dump it into a text file), and then with literally no changes put that string directly into a new Access query, it works fine and returns several fields where the name contains "Mark"

非常简单地说，在Access中可以正常工作的查询在C#中不能正常工作.

Very simply put, a query that works fine within Access, does not work from within C#.

所以我现在很困惑

推荐答案

您正在使用OleDb连接到Access数据库文件.在这种情况下，必须使用ANSI通配符(%和_)进行Like比较，而不是*和?.

You're using OleDb to connect to the Access db file. In that situation you must use ANSI wild cards (% and _) for a Like comparison instead of * and ?.

在WHERE子句中使用这种模式.

Use a pattern like this in your WHERE clause.

WHERE [Employee Name] LIKE "%Mark%"

如果您希望查询在Access会话中的工作原理与从OleDb连接进行的查询相同，请使用ALIKE而不是LIKE. ALIKE始终使用ANSI通配符.

If you want a query which works the same within an Access session as it does from an OleDb connection, use ALIKE instead of LIKE. ALIKE always uses the ANSI wild cards.

WHERE [Employee Name] ALIKE "%Mark%"

这篇关于Ms Access 2010问题与C#中的where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！