All -
I am working on a search functionality for multiple columns of data. I using
'OR' condition for searching on a single search input. For example on front end
i have a search input box, and I can enter either numeric or alphabets or
alphanumeric characters. How can I handle this using cfqueryparam? if the
database column is a numeric, and I enter alphabets, I get errors like Invalid
data %0% for CFSQLTYPE CF_SQL_INTEGER.
I am first retreiving the full result set and performing query of queries to
filter on the search criteria. my query of query looks like below. The above
error happens on col5 search when I search string characters like 'abc'. Are
there any functions in coldfusion that help with this kind of scenarios?
<cfquery name="getinfo" dbtype="query">
SELECT
col1, col2, col3, col4, col5
FROM
getinfo
WHERE
(lower(col2) like lower(<cfqueryparam cfsqltype="cf_sql_varchar"
value="%#Arguments.search#%">) OR
lower(col3) like lower(<cfqueryparam cfsqltype="cf_sql_varchar"
value="%#Arguments.search#%">) OR
lower(col4) like lower(<cfqueryparam cfsqltype="cf_sql_varchar"
value="%#Arguments.search#%">) OR
lower(col5) like lower(<cfqueryparam cfsqltype="cf_sql_integer"
value="%#val(Arguments.search)#%">)
)
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357647
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
