LIKE clause bug in ASP.NET?

[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?

[Hans Kesting]

Rick Csucsai wrote:

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

[Bob Barrows [MVP]]

Rick Csucsai wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl...

Rick Csucsai wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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
Rick Csucsai wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.