SQL LIKE Clause in Access

[Michael C]

Hi all. When I run the following query against my Access database - from
within Access - it returns the correct results (5 records in this instance):

SELECT * FROM [tblItem] WHERE [Category] = "Clothes" AND [Description] LIKE
"*brown*"

However, when I run it from my ASP.NET page via an OleDBCommand, it returns
0 records every time. Now for the kicker: When I drop the LIKE clause, the
query returns the correct results (10 records in this instance) from within
Access and from my ASP.NET page via an OleDBCommand.

Here's the modified query above that works both ways:

SELECT * FROM [tblItem] WHERE [Category] = "Clothes"

Now my question is this: is there a problem with ASP.NET or OleDbCommands
interpreting the LIKE clause of a SQL Select statement? Maybe OleDb
requires a different wild-card character? (I tried the SQL Server % style
already - no luck). Maybe I just need to set a configuration value on my
OleDbConnection or OleDbCommand?

TIA

 

[Kevin Spencer]

Now my question is this: is there a problem with ASP.NET or OleDbCommands

interpreting the LIKE clause of a SQL Select statement? Maybe OleDb
requires a different wild-card character? (I tried the SQL Server % style
already - no luck). Maybe I just need to set a configuration value on my
OleDbConnection or OleDbCommand?

The "problem" lies with the Access proprietary version of SQL. Access is the
only app that understands it. Oddly enough, it isn't supported by the Jet
OLE DB driver; only "generic" SQL is.

The salient differences you're likely to enounter:

Double Quotes as text delimiters: Use Single Quotes in programming. Double
quotes delimit the SQL string.
Wild Card (*): Use % for wild card in programming.

Apparently, the double quotes didn't cause a problem for you, but they could
so beware. In standard SQL, double-quotes inside a string value are literal
double-quotes. Single quotes are delimiters for string values, such as Text
field values.

I believe the Jet OLE DB provider does recognize the single quote as the
date delimiter, so use that instead of the "#" character.

Your corrected original query:

SELECT * FROM [tblItem] WHERE [Category] = 'Clothes'
AND [Description] LIKE '%brown%'

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

Hi all. When I run the following query against my Access database - from
within Access - it returns the correct results (5 records in this
instance):

SELECT * FROM [tblItem] WHERE [Category] = "Clothes" AND [Description]
LIKE
"*brown*"

However, when I run it from my ASP.NET page via an OleDBCommand, it
returns
0 records every time. Now for the kicker: When I drop the LIKE clause,
the
query returns the correct results (10 records in this instance) from
within
Access and from my ASP.NET page via an OleDBCommand.

Here's the modified query above that works both ways:

SELECT * FROM [tblItem] WHERE [Category] = "Clothes"

Now my question is this: is there a problem with ASP.NET or OleDbCommands
interpreting the LIKE clause of a SQL Select statement? Maybe OleDb
requires a different wild-card character? (I tried the SQL Server % style
already - no luck). Maybe I just need to set a configuration value on my
OleDbConnection or OleDbCommand?

TIA

 

[Michael C#]

Yeah I tried the % signs and single quotes. Didn't seem to work... I
generally use SQL Server and only have to use Access for a course I'm
taking. I will try your query just to be sure.

Thanks

Now my question is this: is there a problem with ASP.NET or
OleDbCommands
interpreting the LIKE clause of a SQL Select statement? Maybe OleDb
requires a different wild-card character? (I tried the SQL Server %
style
already - no luck). Maybe I just need to set a configuration value on my
OleDbConnection or OleDbCommand?

The "problem" lies with the Access proprietary version of SQL. Access is
the only app that understands it. Oddly enough, it isn't supported by the
Jet OLE DB driver; only "generic" SQL is.

The salient differences you're likely to enounter:

Double Quotes as text delimiters: Use Single Quotes in programming. Double
quotes delimit the SQL string.
Wild Card (*): Use % for wild card in programming.

Apparently, the double quotes didn't cause a problem for you, but they
could so beware. In standard SQL, double-quotes inside a string value are
literal double-quotes. Single quotes are delimiters for string values,
such as Text field values.

I believe the Jet OLE DB provider does recognize the single quote as the
date delimiter, so use that instead of the "#" character.

Your corrected original query:

SELECT * FROM [tblItem] WHERE [Category] = 'Clothes'
AND [Description] LIKE '%brown%'

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

Hi all. When I run the following query against my Access database - from
within Access - it returns the correct results (5 records in this
instance):

SELECT * FROM [tblItem] WHERE [Category] = "Clothes" AND [Description]
LIKE
"*brown*"

However, when I run it from my ASP.NET page via an OleDBCommand, it
returns
0 records every time. Now for the kicker: When I drop the LIKE clause,
the
query returns the correct results (10 records in this instance) from
within
Access and from my ASP.NET page via an OleDBCommand.

Here's the modified query above that works both ways:

SELECT * FROM [tblItem] WHERE [Category] = "Clothes"

Now my question is this: is there a problem with ASP.NET or
OleDbCommands
interpreting the LIKE clause of a SQL Select statement? Maybe OleDb
requires a different wild-card character? (I tried the SQL Server %
style
already - no luck). Maybe I just need to set a configuration value on my
OleDbConnection or OleDbCommand?

TIA