Problem with query LIKE and Access

Discussion in 'ASP General' started by Paolo Galli, Aug 8, 2008.

  1. Paolo Galli

    Paolo Galli Guest

    Hi all I have a problem with an ASP page and an Access query

    The following query works fine into Access 2007

    SELECT tbl_gallery.id, tbl_gallery.nome, tbl_gallery.luogo,
    tbl_gallery.provincia, tbl_gallery.data, tbl_gallery.descrizione,
    tbl_gallery.esposizione, tbl_gallery.diorama, tbl_gallery.privato,
    tbl_gallery.cap, tbl_gallery.telefono, tbl_gallery.online
    FROM tbl_gallery
    WHERE (((tbl_gallery.nome) Like [?] & "*") AND
    ((tbl_gallery.diorama)=No) AND ((tbl_gallery.online)=Yes))
    ORDER BY tbl_gallery.nome;

    it extract all the name starting with the letter I specify

    anyway when I call the query into an ASP page I get no records shown
    here a snippet...


    Set rs_pres_amici = Server.CreateObject("ADODB.Recordset")

    rs_pres_amici.cursorlocation = 3
    con_pres_amici.q_gallery chiave, rs_pres_amici

    totalRecs = rs_pres_amici.RecordCount

    quanti = 20

    pag = Request.QueryString("pag")
    If IsNumeric(pag) = False Or pag < 1 Then pag = 1

    contatore = 0

    If rs_pres_amici.EOF then

    I always get NO RECORDS FOUND

    where is my error ?

    thanks
    Paolo
    Paolo Galli, Aug 8, 2008
    #1
    1. Advertising

  2. Paolo Galli wrote:
    > Hi all I have a problem with an ASP page and an Access query
    >
    > The following query works fine into Access 2007
    >
    > SELECT tbl_gallery.id, tbl_gallery.nome, tbl_gallery.luogo,
    > tbl_gallery.provincia, tbl_gallery.data, tbl_gallery.descrizione,
    > tbl_gallery.esposizione, tbl_gallery.diorama, tbl_gallery.privato,
    > tbl_gallery.cap, tbl_gallery.telefono, tbl_gallery.online
    > FROM tbl_gallery
    > WHERE (((tbl_gallery.nome) Like [?] & "*") AND
    > ((tbl_gallery.diorama)=No) AND ((tbl_gallery.online)=Yes))
    > ORDER BY tbl_gallery.nome;
    >


    The wildcards used _in Access_ are * and ?

    These wildcards must be replaced by the ODBC wildcards % and _ when running
    queries via ADO. Even when running saved queries. It's weird, I know, but
    replace * with % and give it a try.


    --
    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], Aug 8, 2008
    #2
    1. Advertising

  3. Paolo Galli

    Paolo Galli Guest

    Thanks Bob, now I can retrieve the data but...

    if I hard encoded the string value into the query

    con_pres_amici.q_gallery "c", rs_pres_amici

    I get results... if I do

    con_pres_amici.q_gallery chiave, rs_pres_amici

    I don't

    query now is

    SELECT tbl_gallery.id, tbl_gallery.nome, tbl_gallery.luogo,
    tbl_gallery.provincia, tbl_gallery.data, tbl_gallery.descrizione,
    tbl_gallery.esposizione, tbl_gallery.diorama, tbl_gallery.privato,
    tbl_gallery.cap, tbl_gallery.telefono, tbl_gallery.online
    FROM tbl_gallery
    WHERE (((tbl_gallery.nome) Like [?] & "%") AND
    ((tbl_gallery.diorama)=No) AND ((tbl_gallery.online)=Yes))
    ORDER BY tbl_gallery.nome;

    I get the query parameter this way

    If not Request.QueryString("chiave").Count > 0 Then
    If IsNumeric( chiave ) OR chiave ="" Then chiave = "a"
    End If

    In fact if I omit the chiave parametr the query works but when I call
    the URL (i.e http://localhost/test.asp?chiave=b ) I get an error

    Parameter object is improperly defined. Inconsistent or incomplete
    information was provided. the line pointed is the above one
    (con_pres_amici.q_gallery chiave, rs_pres_amici)

    what's wrong?

    thanks
    Paolo

    Bob Barrows [MVP] wrote:
    > Paolo Galli wrote:
    >> Hi all I have a problem with an ASP page and an Access query
    >>
    >> The following query works fine into Access 2007
    >>
    >> SELECT tbl_gallery.id, tbl_gallery.nome, tbl_gallery.luogo,
    >> tbl_gallery.provincia, tbl_gallery.data, tbl_gallery.descrizione,
    >> tbl_gallery.esposizione, tbl_gallery.diorama, tbl_gallery.privato,
    >> tbl_gallery.cap, tbl_gallery.telefono, tbl_gallery.online
    >> FROM tbl_gallery
    >> WHERE (((tbl_gallery.nome) Like [?] & "*") AND
    >> ((tbl_gallery.diorama)=No) AND ((tbl_gallery.online)=Yes))
    >> ORDER BY tbl_gallery.nome;
    >>

    >
    > The wildcards used _in Access_ are * and ?
    >
    > These wildcards must be replaced by the ODBC wildcards % and _ when running
    > queries via ADO. Even when running saved queries. It's weird, I know, but
    > replace * with % and give it a try.
    >
    >
    Paolo Galli, Aug 8, 2008
    #3
  4. Paolo Galli wrote:
    > Thanks Bob, now I can retrieve the data but...
    >
    > if I hard encoded the string value into the query
    >
    > con_pres_amici.q_gallery "c", rs_pres_amici
    >
    > I get results... if I do
    >
    > con_pres_amici.q_gallery chiave, rs_pres_amici
    >
    > I don't
    >
    > query now is
    >
    > SELECT tbl_gallery.id, tbl_gallery.nome, tbl_gallery.luogo,
    > tbl_gallery.provincia, tbl_gallery.data, tbl_gallery.descrizione,
    > tbl_gallery.esposizione, tbl_gallery.diorama, tbl_gallery.privato,
    > tbl_gallery.cap, tbl_gallery.telefono, tbl_gallery.online
    > FROM tbl_gallery
    > WHERE (((tbl_gallery.nome) Like [?] & "%") AND
    > ((tbl_gallery.diorama)=No) AND ((tbl_gallery.online)=Yes))
    > ORDER BY tbl_gallery.nome;
    >
    > I get the query parameter this way
    >
    > If not Request.QueryString("chiave").Count > 0 Then
    > If IsNumeric( chiave ) OR chiave ="" Then chiave = "a"
    > End If
    >
    > In fact if I omit the chiave parametr the query works but when I call
    > the URL (i.e http://localhost/test.asp?chiave=b ) I get an error
    >
    > Parameter object is improperly defined. Inconsistent or incomplete
    > information was provided. the line pointed is the above one
    > (con_pres_amici.q_gallery chiave, rs_pres_amici)
    >
    > what's wrong?
    >

    You don't seem to have passed the parameter. Does chiave contain a value? I
    don't see shere you assign a value to it if the querystring variable
    contains a value and it is not numeric. I would have done this:

    chiave = Request.QueryString("chiave")

    if len(chiave) > 0 then
    if isnumeric(chiave) then
    chiave = "a"
    end if
    else
    chiave = "a"
    end if

    --
    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], Aug 8, 2008
    #4
  5. Paolo Galli

    Paolo Galli Guest

    OK but when I click on
    http://localhost/test.asp?chiave=b

    I get the error message pointing the line

    con_pres_amici.q_gallery chiave, rs_pres_amici

    Paolo


    Bob Barrows [MVP] wrote:
    > Paolo Galli wrote:
    >> Thanks Bob, now I can retrieve the data but...
    >>
    >> if I hard encoded the string value into the query
    >>
    >> con_pres_amici.q_gallery "c", rs_pres_amici
    >>
    >> I get results... if I do
    >>
    >> con_pres_amici.q_gallery chiave, rs_pres_amici
    >>
    >> I don't
    >>
    >> query now is
    >>
    >> SELECT tbl_gallery.id, tbl_gallery.nome, tbl_gallery.luogo,
    >> tbl_gallery.provincia, tbl_gallery.data, tbl_gallery.descrizione,
    >> tbl_gallery.esposizione, tbl_gallery.diorama, tbl_gallery.privato,
    >> tbl_gallery.cap, tbl_gallery.telefono, tbl_gallery.online
    >> FROM tbl_gallery
    >> WHERE (((tbl_gallery.nome) Like [?] & "%") AND
    >> ((tbl_gallery.diorama)=No) AND ((tbl_gallery.online)=Yes))
    >> ORDER BY tbl_gallery.nome;
    >>
    >> I get the query parameter this way
    >>
    >> If not Request.QueryString("chiave").Count > 0 Then
    >> If IsNumeric( chiave ) OR chiave ="" Then chiave = "a"
    >> End If
    >>
    >> In fact if I omit the chiave parametr the query works but when I call
    >> the URL (i.e http://localhost/test.asp?chiave=b ) I get an error
    >>
    >> Parameter object is improperly defined. Inconsistent or incomplete
    >> information was provided. the line pointed is the above one
    >> (con_pres_amici.q_gallery chiave, rs_pres_amici)
    >>
    >> what's wrong?
    >>

    > You don't seem to have passed the parameter. Does chiave contain a value? I
    > don't see shere you assign a value to it if the querystring variable
    > contains a value and it is not numeric. I would have done this:
    >
    > chiave = Request.QueryString("chiave")
    >
    > if len(chiave) > 0 then
    > if isnumeric(chiave) then
    > chiave = "a"
    > end if
    > else
    > chiave = "a"
    > end if
    >
    Paolo Galli, Aug 8, 2008
    #5
  6. Paolo Galli

    Paolo Galli Guest

    wow... I didn't understood clearly
    sorry.. it work fine ;)

    thanks a lot

    Bob Barrows [MVP] wrote:
    > Paolo Galli wrote:
    >> Thanks Bob, now I can retrieve the data but...
    >>
    >> if I hard encoded the string value into the query
    >>
    >> con_pres_amici.q_gallery "c", rs_pres_amici
    >>
    >> I get results... if I do
    >>
    >> con_pres_amici.q_gallery chiave, rs_pres_amici
    >>
    >> I don't
    >>
    >> query now is
    >>
    >> SELECT tbl_gallery.id, tbl_gallery.nome, tbl_gallery.luogo,
    >> tbl_gallery.provincia, tbl_gallery.data, tbl_gallery.descrizione,
    >> tbl_gallery.esposizione, tbl_gallery.diorama, tbl_gallery.privato,
    >> tbl_gallery.cap, tbl_gallery.telefono, tbl_gallery.online
    >> FROM tbl_gallery
    >> WHERE (((tbl_gallery.nome) Like [?] & "%") AND
    >> ((tbl_gallery.diorama)=No) AND ((tbl_gallery.online)=Yes))
    >> ORDER BY tbl_gallery.nome;
    >>
    >> I get the query parameter this way
    >>
    >> If not Request.QueryString("chiave").Count > 0 Then
    >> If IsNumeric( chiave ) OR chiave ="" Then chiave = "a"
    >> End If
    >>
    >> In fact if I omit the chiave parametr the query works but when I call
    >> the URL (i.e http://localhost/test.asp?chiave=b ) I get an error
    >>
    >> Parameter object is improperly defined. Inconsistent or incomplete
    >> information was provided. the line pointed is the above one
    >> (con_pres_amici.q_gallery chiave, rs_pres_amici)
    >>
    >> what's wrong?
    >>

    > You don't seem to have passed the parameter. Does chiave contain a value? I
    > don't see shere you assign a value to it if the querystring variable
    > contains a value and it is not numeric. I would have done this:
    >
    > chiave = Request.QueryString("chiave")
    >
    > if len(chiave) > 0 then
    > if isnumeric(chiave) then
    > chiave = "a"
    > end if
    > else
    > chiave = "a"
    > end if
    >
    Paolo Galli, Aug 8, 2008
    #6
    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. Brenda Pasquarello

    MS Access Query - use like stored procedure?

    Brenda Pasquarello, Mar 7, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    3,699
    Michael Bohman
    Mar 8, 2006
  2. Anthony Bollinger

    LIKE not working for Access Query in ASP.NET 2.0

    Anthony Bollinger, Oct 30, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    588
    Anthony Bollinger
    Oct 30, 2006
  3. Patrick Kowalzick
    Replies:
    5
    Views:
    458
    Patrick Kowalzick
    Mar 14, 2006
  4. ITHELP

    like statement in SQL query in Access

    ITHELP, Jul 21, 2005, in forum: ASP General
    Replies:
    1
    Views:
    135
    Aaron Bertrand [SQL Server MVP]
    Jul 21, 2005
  5. Roland Hall

    ASP LIKE query using parameterized query

    Roland Hall, Jan 12, 2007, in forum: ASP General
    Replies:
    8
    Views:
    206
    Roland Hall
    Jan 13, 2007
Loading...

Share This Page