Problem with query LIKE and Access

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 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"

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 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"

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
>

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
>