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