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 2 5548
> 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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: William Roberts |
last post by:
I have created a simple select query (bound to a form in Access 2K)
that calls my own public function and returns a result to an
existing field in a table. I use the AS clause to reference the...
|
by: Kerr |
last post by:
Hi all,
I am writing a simple app to query a microsoft access 2000 databse in
asp.net.
Nothing complicated, but I am writing some inline SQL to query a table
with a LIKE clause in it (i.e....
|
by: Chris, Master of All Things Insignificant |
last post by:
I have come to greatly respect both Herfried & Cor's reponses and since the
two conflicted, I wanted to get some clarification.
My orginal post:
Herfried, maybe your example here can get you to...
|
by: Mike Charney |
last post by:
I am trying to write an Open Form command in VBA with a where clause in it.
I need to be able to do two different where clauses:
1) With a between in it: i.e. Between Date1 and Date2 or...
|
by: wjreichard |
last post by:
OK ... I am using UPS Worldship that issues an ODBC query to my MS2K
server ... Worldship can query either a table or a view and retreive
shipping info for a supplied orderid.
I need to create a...
|
by: webgirl |
last post by:
I'm relatively new to SQL Server & looking for some guidance, if possible. I've been reading lots of different things & am a bit confused about some basics.
I have an Access Project with SQL...
|
by: Yitzak |
last post by:
Hi spent a few hours on this one wrote a query that joined on results
of 2 other queries.
Qry3 using Qry1 and Qry2
When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause
- got...
|
by: bilbo |
last post by:
Can anybody help me understand why I get the error
"Syntax error in CONSTRAINT clause"?
I get it in Access 2003 and Access 2007.
Both are clean installs with no add-ins
Running this code in...
|
by: canugi |
last post by:
I need to store the contents of an SQL "in clause" in an MS Access 2000 table.
I use MS Access version 9.0.8960 (SP3)
This is my statement (and it works fine with the explicit "in clause"...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |