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 5571
> 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******** ******@tk2msftn gp13.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***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:u1******** ******@TK2MSFTN GP14.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******** ******@tk2msftn gp13.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
existing field name using the same field name in the AS clause. This
works great, but I have never heard of the As clause being used like
this. Will I be in trouble doing it this way? Would it work in
SQLServer too? See my code for clarification....
|
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. select * from tblcompany where compname
like 'a*')
now this query works in access but I want to parameter the like clause.
Has anyone got any code to successfully parse a like clause to an access
|
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 answer a question I've
wondered about for a while.
With Me.Label1
.Text = ...
.Refresh()
|
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 Between Data1 and
Data2
2) With a Like in it: Like *data1* or Like *date1*
3) Or a statement with both Between and Like in it.
|
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 DB table that will track the orderids requested
from Worldship so that I can stop doubleships. That is to set up a
function to allow the info to be sent only once to worldship.
I need to execute a stored procedure to write to a table and...
| |
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 Server backend. There is a form in the .adp whose record source is a View which contains a WHERE clause.
My questions (and sorry these may be kinda basic):
- I am going to sort the view using a further query.. something like: "SELECT * from...
|
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 invalid use of null.
Fair enough Field1 and Field2 can contain nulls
I altered the 2 queries to exclude nulls from FasText and FasInteger-
|
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 VBA, or pasting the SQL directly in a query results in the same error.
Sub test()
sTableName = "Test"
|
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" parenthesis):
SELECT Permutation FROM PermutationsOne WHERE
Permutation in ('B01','B02','B06');
Now, I want to store different strings similar to 'B01','B02','B06' in table beentogether. Both fields beentogetherwith and member of table beentogether...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |