By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,643 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

SQL LIKE Clause in Access

P: n/a
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
Nov 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> 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.

"Michael C" <me@mine.com> wrote in message
news:ej**************@tk2msftngp13.phx.gbl... 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

Nov 19 '05 #2

P: n/a
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

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:u1**************@TK2MSFTNGP14.phx.gbl...
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.

"Michael C" <me@mine.com> wrote in message
news:ej**************@tk2msftngp13.phx.gbl...
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


Nov 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.