LIKE clause bug in ASP.NET?

Discussion in 'ASP .Net' started by Rick Csucsai, Jul 12, 2005.

  1. Rick Csucsai

    Rick Csucsai Guest

    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?
    Rick Csucsai, Jul 12, 2005
    #1
    1. Advertising

  2. Rick Csucsai

    Hans Kesting Guest

    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
    Hans Kesting, Jul 12, 2005
    #2
    1. Advertising

  3. 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], Jul 12, 2005
    #3
  4. Rick Csucsai

    Rick Csucsai Guest

    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]" <> wrote in message
    news:...
    > 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, Jul 12, 2005
    #4
  5. 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.
    Bob Barrows [MVP], Jul 12, 2005
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ron Vecchi
    Replies:
    1
    Views:
    673
    Ron Vecchi
    Jul 27, 2003
  2. Ron Vecchi
    Replies:
    0
    Views:
    390
    Ron Vecchi
    Jul 30, 2003
  3. Michael C

    SQL LIKE Clause in Access

    Michael C, Jan 17, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    13,093
    Michael C#
    Jan 18, 2005
  4. Kev
    Replies:
    0
    Views:
    412
  5. john

    Dynamic Like clause in LINQ How?

    john, Feb 2, 2008, in forum: ASP .Net
    Replies:
    1
    Views:
    22,889
    Steven Cheng[MSFT]
    Feb 4, 2008
Loading...

Share This Page