You need to verify that the search terms you are providing for that column are
numeric. The cfqueryparam does that but returns an error when it finds a
problem. It's up to you to make sure that you are providing the proper type
for the columns you are querying. In this case if the search terms are not
numeric then you want to skip the column all together. You need to check that
it is both numeric and an integer.
<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#%">)
<cfif IsNumeric(arguments.search) and (Int(arguments.search) is
arguments.search)>
OR lower(col5) like lower(<cfqueryparam cfsqltype="cf_sql_integer"
value="%#val(Arguments.search)#%">)
</cfif>
)
</cfquery>
David Phelan
Web Developer
IT Security & Web Technologies
Emerging Health
Montefiore Information Technology
3 Odell Plaza, Yonkers, NY 10701
914-457-6465 Office
862-234-9109 Cell
dphe...@emerginghealthit.com
www.emerginghealthit.com
www.montefiore.org
-----Original Message-----
From: fun and learning [mailto:funandlrnn...@gmail.com]
Sent: Monday, February 10, 2014 10:04 AM
To: cf-talk
Subject: cfquery multiple column search
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:357652
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
