CODEWITHSUNDEEP
c#.netsqlms-accessoledbconnection
Andrew
Andrew

Reputation: 7798

Query does not return anything

I am trying to get some results from the database, but the query is failing!

String sqlFindModel = "SELECT * FROM [PRODUCT] WHERE [PN] LIKE ('*" + textBox1.Text + "*')";

When I trim the "WHERE [PN] LIKE ..." part, it works fine. When I replace LIKE to '=' and look for Exact value, it works.

I am confused.

PS - It is Interesting, when doing Query in ACCESS directly, you have to use *; but when using C# and connect to MS Access, need to use %... interesting!

Upvotes: 3

Views: 255

Answers (3)

HansUp
HansUp

Reputation: 97131

Consider whether Access' undocumented ALike comparison operator would make this easier to deal with.

"SELECT * FROM [PRODUCT] WHERE [PN] ALike '%" + textBox1.Text + "%'"

ALike signals Access' db engine to expect ANSI wild cards (% and _ instead of * and ?). So your query could then work the same regardless of whether you're running it from within an Access session, or from outside an Access session using OleDb.

I've seen objections to ALike due to the fact that it's not standard SQL. However, when adapting Access queries for other db engines, I much prefer to change ALike to Like rather than having to change * and ? to % and _.

Upvotes: 3

Darren
Darren

Reputation: 70796

Try replacing the * character with a %

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1503729

* isn't used for wildcarding in SQL LIKE statements - % is.

However, you shouldn't just change your code to use % - you should fix your code so it's not vulnerable to SQL injection attacks, instead. You should use parameterized SQL instead. See the documentation for OleDbCommand.Parameters for an example.

Upvotes: 9

Related Questions