Using LIKE in Access Query from C#

[Guest]

I have an Access database used to organize my images taken with my digital
camera. There are many tables representing dates and each contains image
names and descriptions of each image. I am rewriting my web site using C#
and I am trying to select only those images where the description contains
certain keywords. Using a query that contains the LIKE parameter from my
program returns no records. Using the same query directly in the Access
database and the correct records are returned. If I remove the LIKE
parameter in my program, then all records are returned. I have been trying
to resolve this issue for a week. Can anyone assist?

Following is the code including the query that works and the one that don't:


System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source=
h:\inetpub\wwwroot\database\gallery.mdb";
conn.Open();
string Query= "SELECT Images, Description FROM 040904 WHERE Description Like
\"*Lewis*\"";
// Query= "SELECT Images, Description FROM 040904";
OleDbCommand cmdSearch = new OleDbCommand(Query, conn);
cmdSearch.ExecuteNonQuery();
OleDbDataReader sreader = cmdSearch.ExecuteReader();

while (sreader.Read())
{
string d= sreader.GetString(0);
}
sreader.Close();
conn.Close();

 

[Miha Markic [MVP C#]]

Hi Evan,

Try with this statement:
"SELECT Images, Description FROM 040904 WHERE Description Like '%Lewis%'";

 

[MacDermott]

I think against an Access (Jet) database, your syntax should look like this:
string Query= "SELECT Images, Description FROM 040904 WHERE Description
Like '*Lewis*'";

If you're going to be using a variable string instead of 'Lewis', be sure to
provide for escaping any apostrophes in that string:
"...Like '*" & Replace(MyString, chr(39),chr(39) & chr(39)) & "*'"
(That's VB syntax - you may need to change it a bit for C#)

HTH

 

[Guest]

Hi,

Miha is correct. You have to use the % sign instead of the * when doing a
wildcard search to Access via OleDb. If you use * it will run without error
but I think that it will search for the literal string * (e.g. LIKE 'A*' will
be look for a string like A*) instead of the wild card search.

Hope this helps.
--------------------------