Problem with query LIKE and Access

[Paolo Galli]

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

 

[Bob Barrows [MVP]]

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]

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

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.

 

[Bob Barrows [MVP]]

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]

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

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]

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

thanks a lot

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