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

[Dave]

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?

 

[Cinzia]

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?

Hi Dave,
try changing your query in Access in this way:

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

when used from ADO the wildcard char is % not *

 

[Dave]

Thanks but that query returns 0 records whether it is called from an ASP or
from Access itself.

I am calling a saved query in Access.

Do I have to build a dynamic query in the ASP using the "%" and have ADO
translate it for me?


IOW, build this in the ASP...

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

..and it will be translated to this...

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

 

[Dave]

Never mind.

I got it to work with a saved query in Access.

The saved Access query does not work with the % signs in Access itself, but
it does when called from an ASP.

Thanks for your help

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?

Hi Dave,
try changing your query in Access in this way:

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

when used from ADO the wildcard char is % not *

 

[Cinzia]

Never mind.

I got it to work with a saved query in Access.

The saved Access query does not work with the % signs in Access itself,
but it does when called from an ASP.

Thanks for your help

Hi dave
You are right, it is because DAO, used from Access, doesn't understand '%',
but ADO understand '%' and doesn't understand '*', so there is a bit of
confusion...

Bye