Keyword search

[Terry]

[I apologize for possible multiple postings due to technical
problems]


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?

 

[Tom Ellison]

Dear Terry:

I recommend you drop the CStr function and see what it does. Please come
back here with results of that.

Tom Ellison

 

[Terry]

Original :: Tom Ellison :: 2006-01-07 20:02

Dear Terry:

I recommend you drop the CStr function and see what it does. Please come
back here with results of that.

Tom Ellison

[snip]
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]
objCmd.CreateParameter("@SearchTerm", adVarWChar,
adParamInput,
Len(p_strMySearchTerm), p_strMySearchTerm)

Thanks for your input Tom. I removed the CSTR function but I
saw no change whatsoever. The query still works when ran
directly from Access, but not when called from the ASP code.