LIKE clause bug in ASP.NET?

R

Rick Csucsai

I am trying to get a handful of records returned via the following query
using a datareader and then binding it to a datagrid:

Select * from documents WHERE (title LIKE '*network*') Order By department,
headername, title;

It keeps returning 0 records. Note that if I take out the crieteria for the
LIKE clause, it returns just fine. If I copy and paste this into a query
within the database itself, if works fine! Why will asp.net not return the
records? I have tried formatting it in a million different ways. All of
which work directly in the database, buy none when I try to go through the
datareader.

Any suggestions? Known bugs? Anything?
 
H

Hans Kesting

Rick said:
I am trying to get a handful of records returned via the following
query using a datareader and then binding it to a datagrid:

Select * from documents WHERE (title LIKE '*network*') Order By
department, headername, title;

It keeps returning 0 records. Note that if I take out the crieteria
for the LIKE clause, it returns just fine. If I copy and paste this
into a query within the database itself, if works fine! Why will
asp.net not return the records? I have tried formatting it in a
million different ways. All of which work directly in the database,
buy none when I try to go through the datareader.

Any suggestions? Known bugs? Anything?

Try
Select * from documents WHERE (title LIKE '%network%')

The usual wildcard in SQL is a %, not a *. If you run the query
in Access, you need the "*". If you run it through a middle layer,
that layer wants regular SQL which it will translate into the db-specific
syntax.
By the way: the "single character" wildcard in SQL is "_" (not "?").

Hans Kesting
 
B

Bob Barrows [MVP]

Rick said:
I am trying to get a handful of records returned via the following
query using a datareader and then binding it to a datagrid:

Select * from documents WHERE (title LIKE '*network*') Order By
department, headername, title;

I assume you are using Access ...

When using ADO.Net to execute queries, you need to use the ODBC wildcards
(?, _) instead of the Jet wildcards (*, ?)

WHERE title LIKE '%network%'

HTH,
Bob Barrows

PS. You should avoid using selstar in production code:
http://www.aspfaq.com/show.asp?id=2096
 
R

Rick Csucsai

Yep, you were right. Replaced the "*" with "%" and it worked fine.
Ironically, putting the syntax direct in Access now returns 0 records but in
the code works fine

go figure...

Thanks guys!

Rick
 
B

Bob Barrows [MVP]

Just to reiterate what Hans said:
When running it in Access, you have to use the Jet wildcards. Access passes
the query directly to Jet - neither OLE DB nor ODBC is involved.

When running it via ADO/ADO.Net, you have to use the ODBC wildcards. When
the Jet OLE DB Provider (or ODBC driver if you are using ODBC) parses the
query, it replaces the ODBC wildcards with the Jet wildcards before passing
it along to Jet to be executed. When the query already contains an asterisk,
the Jet Provider escapes it so it is treated as a literal character rather
than a wildcard.

Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top