Syntax for wildcard query in ASP 3.0

[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?

 

[Bob Barrows [MVP]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Dave]

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?

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]


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

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

 

[Dave]

My sincere apologies.

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

Thank you for the example and your patience

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"