Syntax for wildcard query in ASP 3.0

Discussion in 'ASP General' started by Dave, Mar 24, 2007.

  1. Dave

    Dave Guest

    This is my query named "spVOC_Sp_Example_search" in Access 2003:

    PARAMETERS [pSearch] Text ( 255 );
    SELECT Example.Example
    FROM Example
    WHERE (((Example.Example) Like "*" & [pSearch] & "*"));

    It works great: I call the query, am prompted for the pSearch param value,
    I supply a string as the value, and it returns all records with that string
    contained anyhwere in the "example" field.

    But none of the queries below will work in ASP 3.0:

    Set rs = Server.CreateObject("ADODB.recordset")

    sSQL="spVOC_Sp_Example_search pSearch=%a%"
    sSQL="spVOC_Sp_Example_search pSearch=*"
    sSQL="spVOC_Sp_Example_search pSearch=%"
    sSQL="spVOC_Sp_Example_search pSearch='a'"
    sSQL="spVOC_Sp_Example_search pSearch=""a"""
    sSQL="spVOC_Sp_Example_search a"
    sSQL="spVOC_Sp_Example_search *a*"
    sSQL="spVOC_Sp_Example_search %a%"
    sSQL="spVOC_Sp_Example_search %"
    sSQL="spVOC_Sp_Example_search *"
    sSQL="spVOC_Sp_Example_search ''"
    rs.Open sSQL, cn, 0, 4

    i=rs.RecordCount


    All of the queries above either return 0 records or error out (Invalid SQL
    statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.)

    How do I properly call an Access parameterized wildcard query from ASP?
    Dave, Mar 24, 2007
    #1
    1. Advertising

  2. Dave wrote:
    > This is my query named "spVOC_Sp_Example_search" in Access 2003:
    >
    > PARAMETERS [pSearch] Text ( 255 );
    > SELECT Example.Example
    > FROM Example
    > WHERE (((Example.Example) Like "*" & [pSearch] & "*"));
    >


    > How do I properly call an Access parameterized wildcard query from
    > ASP?


    In order to call the query via ADO, you must change the Jet wildcards to
    ODBc wildcards:

    WHERE (((Example.Example) Like "%" & [pSearch] & "%"));

    Then you can simply call it by:

    set rs=createobject("adodb.recordset")
    cn.spVOC_Sp_Example_search "a", rs

    See:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Mar 25, 2007
    #2
    1. Advertising

  3. Dave

    Dave Guest

    Thanks Bob

    I think there is something fundamental I'm missing here.

    I am not using dynamic SQL, my query resides on Access so in the Access
    application the WHERE clause below returns over 1000 records:

    WHERE (((Example.Example) Like "*" & [pSearch] & "*"));

    While this returns 0 records:

    WHERE (((Example.Example) Like "%" & [pSearch] & "%"));

    Both of these WHERE clasues return 0 records to my ASP page.

    So how do I use "%" in my situation?




    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Dave wrote:
    >> This is my query named "spVOC_Sp_Example_search" in Access 2003:
    >>
    >> PARAMETERS [pSearch] Text ( 255 );
    >> SELECT Example.Example
    >> FROM Example
    >> WHERE (((Example.Example) Like "*" & [pSearch] & "*"));
    >>

    >
    >> How do I properly call an Access parameterized wildcard query from
    >> ASP?

    >
    > In order to call the query via ADO, you must change the Jet wildcards to
    > ODBc wildcards:
    >
    > WHERE (((Example.Example) Like "%" & [pSearch] & "%"));
    >
    > Then you can simply call it by:
    >
    > set rs=createobject("adodb.recordset")
    > cn.spVOC_Sp_Example_search "a", rs
    >
    > See:
    > http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=
    >
    > http://groups.google.com/groups?hl=...=1&selm=
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Dave, Mar 25, 2007
    #3
  4. Dave wrote:
    > Thanks Bob
    >
    > I think there is something fundamental I'm missing here.
    >
    > I am not using dynamic SQL,


    I know you're not.

    > my query resides on Access so in the
    > Access application the WHERE clause below returns over 1000 records:


    Yes I know. Access uses DAO to execute your stored queries, so the Jet
    wildcards can be used. When executing queries, even saved queries, via ADO,
    the ODBC wildcards must be used. It's very nonintuitive, I know.
    >
    > WHERE (((Example.Example) Like "*" & [pSearch] & "*"));
    >
    > While this returns 0 records:
    >
    > WHERE (((Example.Example) Like "%" & [pSearch] & "%"));
    >
    > Both of these WHERE clasues return 0 records to my ASP page.
    >


    The latter should work. I've just tested it with this sample data:

    abcd
    efgh
    halp
    pqrs

    I created a saved query called "wildcardsearch" with this sql:
    SELECT Example
    FROM Example
    WHERE Example Like "%" & [psearch] & "%";

    Using this code to execute the saved query:
    <%
    dim cn, rs, s
    s="a"
    set cn=createobject("adodb.connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & server.MapPath("db7.mdb")
    set rs=createobject("adodb.recordset")
    cn.wildcardsearch s,rs
    if not rs.EOF then
    Response.Write rs.GetString(,,,"<BR>")
    else
    Response.Write "No records retrieved"
    end if
    rs.Close
    cn.Close
    %>

    I get this result:
    abcd
    halp



    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Mar 25, 2007
    #4
  5. Dave

    Dave Guest

    My sincere apologies.

    It works fine now. I was testing improperly for the recordset.

    Thank you for the example and your patience


    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Dave wrote:
    >> Thanks Bob
    >>
    >> I think there is something fundamental I'm missing here.
    >>
    >> I am not using dynamic SQL,

    >
    > I know you're not.
    >
    >> my query resides on Access so in the
    >> Access application the WHERE clause below returns over 1000 records:

    >
    > Yes I know. Access uses DAO to execute your stored queries, so the Jet
    > wildcards can be used. When executing queries, even saved queries, via
    > ADO, the ODBC wildcards must be used. It's very nonintuitive, I know.
    >>
    >> WHERE (((Example.Example) Like "*" & [pSearch] & "*"));
    >>
    >> While this returns 0 records:
    >>
    >> WHERE (((Example.Example) Like "%" & [pSearch] & "%"));
    >>
    >> Both of these WHERE clasues return 0 records to my ASP page.
    >>

    >
    > The latter should work. I've just tested it with this sample data:
    >
    > abcd
    > efgh
    > halp
    > pqrs
    >
    > I created a saved query called "wildcardsearch" with this sql:
    > SELECT Example
    > FROM Example
    > WHERE Example Like "%" & [psearch] & "%";
    >
    > Using this code to execute the saved query:
    > <%
    > dim cn, rs, s
    > s="a"
    > set cn=createobject("adodb.connection")
    > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & server.MapPath("db7.mdb")
    > set rs=createobject("adodb.recordset")
    > cn.wildcardsearch s,rs
    > if not rs.EOF then
    > Response.Write rs.GetString(,,,"<BR>")
    > else
    > Response.Write "No records retrieved"
    > end if
    > rs.Close
    > cn.Close
    > %>
    >
    > I get this result:
    > abcd
    > halp
    >
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Dave, Mar 25, 2007
    #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. Naveen M

    using FTP Wildcard in asp.net code

    Naveen M, Jul 21, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    432
    Christopher Frazier
    Jul 23, 2003
  2. traneHead
    Replies:
    7
    Views:
    8,671
    Adam Maass
    Sep 27, 2007
  3. Replies:
    7
    Views:
    831
  4. Replies:
    1
    Views:
    111
    Bob Barrows [MVP]
    Sep 11, 2005
  5. Replies:
    3
    Views:
    100
Loading...

Share This Page