Calling parameterized query from ASP - which datatype constant to use?

[Terry]

Hi,

My ASP page offers a search box allowing the user to enter a keyword.
My goal is to try and match this keyword against any of the following
fields in the 'tblProduit' table:

ProID (Long integer)
ProCode (Text(50))
ProNom (Text(255))
ProDescription (Text(255))

The query is defined as:
PARAMETERS [@SearchTerm] Text;
SELECT * FROM tblProduit
WHERE ((CStr([ProID])) Like ("*" & [@SearchTerm] & "*"))
OR
(ProCode Like ("*" & [@SearchTerm] & "*"))
OR
(ProNom Like ("*" & [@SearchTerm] & "*"))
OR
(ProDescription Like ("*" & [@SearchTerm] & "*"));

The parameter is passed from ASP as such:
objCmd.CreateParameter("@SearchTerm", adVarWChar, adParamInput,
Len(p_strMySearchTerm), p_strMySearchTerm)

* I have checked that the parameter is indeed passed and has a value
* Executing the query directly, inside Access, proves successful
* Calling the query from the ASP page returns an empty recordset
* I have also tried the adVarChar ADO datatype without success

Any ideas?

 

[Cinzia]

Hi,

My ASP page offers a search box allowing the user to enter a keyword.
My goal is to try and match this keyword against any of the following
fields in the 'tblProduit' table:

ProID (Long integer)
ProCode (Text(50))
ProNom (Text(255))
ProDescription (Text(255))

The query is defined as:
PARAMETERS [@SearchTerm] Text;
SELECT * FROM tblProduit
WHERE ((CStr([ProID])) Like ("*" & [@SearchTerm] & "*"))
OR
(ProCode Like ("*" & [@SearchTerm] & "*"))
OR
(ProNom Like ("*" & [@SearchTerm] & "*"))
OR
(ProDescription Like ("*" & [@SearchTerm] & "*"));

The parameter is passed from ASP as such:
objCmd.CreateParameter("@SearchTerm", adVarWChar, adParamInput,
Len(p_strMySearchTerm), p_strMySearchTerm)

* I have checked that the parameter is indeed passed and has a value
* Executing the query directly, inside Access, proves successful
* Calling the query from the ASP page returns an empty recordset
* I have also tried the adVarChar ADO datatype without success

Any ideas?

Hi Terry,
yes when you use Like Operator from ADO you must use % and not *.
So your query will be:

PARAMETERS [@SearchTerm] Text;

SELECT * FROM tblProduit
WHERE ((CStr([ProID])) Like ("*" & [@SearchTerm] & "*"))
OR
(ProCode Like ("%" & [@SearchTerm] & "%"))
OR
(ProNom Like ("%" & [@SearchTerm] & "%"))
OR
(ProDescription Like ("%" & [@SearchTerm] & "%"));

Bye
Cinzia

 

[Terry]

Original :: Cinzia :: 2006-01-09 03:58

The query is defined as:
PARAMETERS [@SearchTerm] Text;
SELECT * FROM tblProduit
WHERE ((CStr([ProID])) Like ("*" & [@SearchTerm] & "*"))
OR
(ProCode Like ("*" & [@SearchTerm] & "*"))
OR
(ProNom Like ("*" & [@SearchTerm] & "*"))
OR
(ProDescription Like ("*" & [@SearchTerm] & "*"));

[snip]


Hi Terry,
yes when you use Like Operator from ADO you must use % and not *.
So your query will be:

PARAMETERS [@SearchTerm] Text;

SELECT * FROM tblProduit
WHERE ((CStr([ProID])) Like ("*" & [@SearchTerm] & "*"))
OR
(ProCode Like ("%" & [@SearchTerm] & "%"))
OR
(ProNom Like ("%" & [@SearchTerm] & "%"))
OR
(ProDescription Like ("%" & [@SearchTerm] & "%"));

Bye
Cinzia

Thanks a lot Cinzia, that did the trick! You're amazing!