CODEWITHSUNDEEP
c#sqlms-accessms-access-2010
nurchi
nurchi

Reputation: 800

Ms Access 2010 issue with a where clause in C#

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.

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*").

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

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.

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"

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

So I am now confused

Upvotes: 1

Views: 1655

Answers (2)

HansUp
HansUp

Reputation: 97131

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 ?.

Use a pattern like this in your WHERE clause.

WHERE [Employee Name] LIKE "%Mark%"

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%"

Upvotes: 3

nurchi
nurchi

Reputation: 800

Simon's question and HansUp's answer ended up solving my problem. For those curious or having a similar problem, here is full query:

string query=
  "SELECT Employees.ID, " +
  "[All Names E].Name AS [Employee Name], " +
  "Titles.Title, " +
  "[All Names S].Name AS [Supervisor Name], " +
  "Employees.[Phone #], " +
  "Offices.[Office Location], " +
  "PCs.PC " +
  "FROM (((((Employees LEFT JOIN [All Names] as [All Names E] ON Employees.Employee = [All Names E].ID) " +
  "LEFT JOIN [All Names] as [All Names S] on Employees.Supervisor=[All Names S].ID) " +
  "LEFT JOIN Titles on Employees.Title=Titles.ID) " +
  "LEFT JOIN Offices on Employees.[Office Location]=Offices.ID) " +
  "LEFT JOIN PCs on Employees.PC=PCs.ID) " +
  "ORDER BY Employees.ID";

Adding a where clause before ORDER BY that is WHERE ([All Names E].Name LIKE \"*Mark*\") did work from within Access. The second way was:

string searchQuery="select * from ("+query+") where ([Employee Name] like \"*Mark*\")";

Both methods worked perfectly in Access, but I had no idea there was a different wildcard symbol for use with OleDB.

So changing the asterisk to a percentage sign fixed the issue.

Thanks again.

Upvotes: 1

Related Questions