Posted At : Aug 16, 2008 17:30 PM
| Posted By : Ed Tabara
Related Categories: ColdFusion
Related Categories: ColdFusion
Problem:
We have a DB table with varchar values. To make it easy i've created it with a QueryNew:
And what is needed is to do the following query:
What we will get is the following error:
It come that ColdFusion see the value of CFQUERYPARAM as a DATE and not as VARCHAR. Casting to varchar left and right sides of the equalities does not help at all.
Solution:
Use LIKEinstead of =in the query like this:
We have a DB table with varchar values. To make it easy i've created it with a QueryNew:
<cfset getTMP = QueryNew("id, col1, col2")>
<cfset QueryAddRow(getTMP, 2)>
<cfset QuerySetCell(getTMP, "id", "1", 1)>
<cfset QuerySetCell(getTMP, "col1", "1212-77-01", 1)>
<cfset QuerySetCell(getTMP, "col2", "1212-77-01", 1)>
<cfset QuerySetCell(getTMP, "id", "2", 2)>
<cfset QuerySetCell(getTMP, "col1", "1212-77-01ewr", 2)>
<cfset QuerySetCell(getTMP, "col2", "1212-77-01qwe", 2)>And what is needed is to do the following query:
Select *
From getTMP
Where col1 = <cfqueryparam CFSQLTYPE="CF_SQL_VARCHAR" value="1212-77-01">
or col2 = <cfqueryparam CFSQLTYPE="CF_SQL_VARCHAR" value="1212-77-01"> What we will get is the following error:
It come that ColdFusion see the value of CFQUERYPARAM as a DATE and not as VARCHAR. Casting to varchar left and right sides of the equalities does not help at all.
Solution:
Use LIKEinstead of =in the query like this:
Select *
From getTMP
Where col1 like <cfqueryparam CFSQLTYPE="CF_SQL_VARCHAR" value="1212-77-01">
or col2 like <cfqueryparam CFSQLTYPE="CF_SQL_VARCHAR" value="1212-77-01">
