I also stopped using the <cfquery> tag and now it works:
<cfscript>
classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");
con = dm.getConnection("jdbc:odbc:DRIVER={SQL Server};Database=" &
application.db_name & ";Server=" & application.dbserver_name & ";",
application.db_user, application.db_pword);
qText = "Select donorid, occupation, race, haircolor, hairtexture,
eyecolor, religion, bloodtype, height, weight, heightmetric, weightmetric,
reportedpregnancy, opendonorid, infomp3avail, ethnicity, cmvstatus,
DateEntered, ARTavail, ARTonly, SelectDonors FROM v_websearch where
available = 1 ";
if(len(drpHairColor)){
qText = qText & "AND haircolor like '" & drpHairColor & "%' ";
}
if(len(drpEyeColor)){
qText = qText & "AND eyecolor like '" & drpEyeColor & "%' ";
}
if(len(drpEthnic)){
qText = qText & "AND ethnicity like '" & drpEthnic & "%' ";
}
if(len(txtDonorId)){
qText = qText & "AND donorid like '%" & txtDonorId & "%' ";
}
//if(chkAudioFile){
//qText = qText & "AND InfoMP3Avail = 1 ";
//}
if(chkOpenId){
qText = qText & "AND OpenDonorID = 1 ";
}
if(chkCanadian){
qText = qText & "AND CanadianCompliantAvail = 1 ";
}
if(len(drpRace)){
qText = qText & "AND race like '" & drpRace & "%' ";
}
if(len(drpReligion)){
qText = qText & "AND religion like '" & drpReligion & "%' ";
}
if(len(drpBloodType)){
qText = qText & "AND bloodtype like '" & drpBloodType & "%' ";
}
if(len(drpHeight)){
if(drpHeight eq "1-66"){
qText = qText & "AND heightregular < 507 ";
}
if(drpHeight eq "67-71"){
qText = qText & "AND heightregular < 512 AND heightregular > 507
";
}
if(drpHeight eq "72-76"){
qText = qText & "AND heightregular < 605 AND heightregular > 512
";
}
if(drpHeight eq "77-100"){
qText = qText & "AND heightregular > 604 ";
}
}
if(IsNumeric(txtWeight1) AND IsNumeric(txtWeight2)){
if(chkWeight is "kg"){
qText = qText & " AND weightmetric <= " & txtWeight2 & " AND
weightmetric >= " & txtWeight1 & " ";
}
else{
qText = qText & " AND weight <= " & txtWeight2 & " AND weight >=
" & txtWeight1 & " ";
}
}
if(len(drpCMVstatus)){
qText = qText & "AND cmvstatus like '" & drpCMVstatus & "%' ";
}
if(chkART){
qText = qText & "AND ARTavail IS NOT NULL ";
}
if(chkSelect){
qText = qText & "AND SelectDonors IS NOT NULL ";
}
qText = qText & "ORDER BY donorid";
st = con.createStatement();
rs = st.ExecuteQuery(qText);
q = createObject("java", "coldfusion.sql.QueryTable").init(rs);
stRecordCount = con.createStatement();
rs2 = stRecordCount.ExecuteQuery("Select count(*) AS MyCount FROM
tbldonors where available = 1");
qRecordCount = createObject("java",
"coldfusion.sql.QueryTable").init(rs2);
</cfscript>
On Wed, Jan 27, 2010 at 4:18 PM, Forrest C. Gilmore <[email protected]>wrote:
> Can you show me the exact code you used when you tried
> PreserveSingleQuotes?
> According to the documentation, "This function is useful in SQL statements
> to defer evaluation of a variable reference until runtime. "
> Seems to be just what you need, but it must be used properly.
>
> *Example B*: Consider this code:
>
> <cfset list0 = " '1','2''3' ">
>
> <cfquery sql = "select * from foo where bar in (#list0#)">
>
> ColdFusion escapes the single-quote characters in the list as follows:
>
> ""1"", ""2"", ""3""
>
> The cfquery tag throws an error.
>
> You code this function correctly as follows:
>
> <cfquery sql = "select * from foo where bar in
> (#preserveSingleQuotes(list0)#)">
>
> This function ensures that ColdFusion evaluates the code as follows:
>
> '1', '2', '3'
> --------------------------------------
>
> Forrest C. Gilmore
> ===========================
> Dusty Hale wrote:
>
> Just to rule it out though I tried it. Didn't help though.
>
>
> On Wed, Jan 27, 2010 at 3:36 PM, Forrest C. Gilmore <[email protected]>wrote:
>
>> Go into CF Help and look up the Preservesinglequotes function.
>>
>> Forrest C. Gilmore
>> ========================
>> Dusty Hale wrote:
>>
>>> Yep that runs fine.
>>>
>>> This is really strange. The value of the qText variable is the same as
>>> the query below yet when I stuff #qText# in there, it somehow doubles the
>>> quotes by itself. Damn I just don't get how it could or would do that.
>>>
>>> If I output the value of the qText variable on a page, quotes are
>>> correct. Have you ever seen anything like this?
>>>
>>> Thanks,
>>> Dusty
>>>
>>> On Wed, Jan 27, 2010 at 3:00 PM, Teddy R. Payne
>>> <[email protected]<mailto:
>>> [email protected]>> wrote:
>>>
>>> Dusty,
>>> As a source of troubleshooting, have you put the SQL string into
>>> the cfquery statement in its final form? I am wondering if the
>>> SQL runs correctly in the cfquery before the dynamic evaluation.
>>>
>>> <cfquery name="q" datasource="#application.dsn_name#"
>>> username="#application.db_user#" password="#application.db_
>>>
>>> pword#">
>>> Select donorid, occupation, race, haircolor, hairtexture,
>>> eyecolor, religion, bloodtype, height, weight, heightmetric,
>>> weightmetric, reportedpregnancy, opendonorid, infomp3avail,
>>> ethnicity, cmvstatus, DateEntered, ARTavail, ARTonly, SelectDonors
>>> FROM v_websearch where available = 1 AND donorid like '%9986%'
>>> ORDER BY donorid
>>> </cfquery>
>>>
>>> Does this run correctly?
>>>
>>>
>>>
>>> Teddy R. Payne, ACCFD
>>> Google Talk - [email protected] <mailto:[email protected]>
>>>
>>>
>>>
>>>
>>> On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale <[email protected]
>>> <mailto:[email protected]>> wrote:
>>>
>>> Teddy here's how I build the qText string part where the
>>> quotes are:
>>>
>>> if(len(txtDonorId)){
>>> qText = qText & "AND donorid like '%" & txtDonorId &
>>> "%' ";
>>> }
>>>
>>>
>>>
>>> On Wed, Jan 27, 2010 at 2:28 PM, Teddy R. Payne
>>> <[email protected] <mailto:[email protected]>> wrote:
>>>
>>> Dusty,
>>> What type of single quotes are those? What is the source
>>> of the text? Was the query copied and pasted from a
>>> Microsoft document?
>>>
>>>
>>> Teddy R. Payne, ACCFD
>>> Google Talk - [email protected]
>>> <mailto:[email protected]>
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
>>> <[email protected] <mailto:[email protected]>>
>>> wrote:
>>>
>>> Hi:
>>>
>>> I've run into a very strange issue. I have a cfc which
>>> has a <cfquery> tag in it. I recently added one field
>>> the SQL in the query and am getting an error I've
>>> never seen before. I can't seem to dig out any info to
>>> solve this. If anyone is familiar, please share.
>>>
>>> Of course when I output the SQL and run in a SQL
>>> Studio Query window, the query runs fine with no errors.
>>>
>>> Here the error I see in CF:
>>>
>>>
>>>
>>>
>>> -----------------------------------------------------------------------------------------------------------------------------
>>> [Macromedia][SQLServer JDBC Driver][SQLServer]Divide
>>> by zero error encountered.
>>>
>>> The error occurred in
>>> *D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>> line 149*
>>> *Called from*
>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>> line 139
>>> *Called from*
>>> D:\websites\xytexcom_stage2\htdocs\search.cfm: line 48
>>> *Called from*
>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>> line 149
>>> *Called from*
>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:
>>> line 139
>>> *Called from*
>>> D:\websites\xytexcom_stage2\htdocs\search.cfm: line 48
>>>
>>> 147 : </cfscript>
>>> 148 : <cfquery name="q"
>>> datasource="#application.dsn_name#" username="#application.db_user#"
>>> password="#application.db_pword#">
>>> *149 : #qText#*
>>>
>>>
>>>
>>>
>>>
>>> 150 : </cfquery>
>>> -----------------------------------------
>>>
>>>
>>> Here's the SQL code in the qText variable. Please note
>>> that it runs fun in Query Analyzer. Also note that no
>>> division is being used.
>>>
>>>
>>> ----------------------------------------------------------------
>>> Select donorid, occupation, race, haircolor,
>>> hairtexture, eyecolor, religion, bloodtype, height,
>>> weight, heightmetric, weightmetric, reportedpregnancy,
>>> opendonorid, infomp3avail, ethnicity, cmvstatus,
>>> DateEntered, ARTavail, ARTonly, SelectDonors FROM
>>> v_websearch where available = 1 AND donorid like
>>> '%9986%' ORDER BY donorid
>>>
>>>
>>> ----------------------------------------------------------------------------------
>>>
>>> Any advise or thought on this of course is greatly
>>> appreciated.
>>>
>>> Dusty
>>>
>>>
>>>
>>>
>>>
>>> -- Dusty Hale
>>> Email: [email protected]
>>> Phone (Atlanta): 404.474.3754
>>> Phone (Toll Free USA): 877.841.3370
>>> Website: www.DustyHale.com <http://www.DustyHale.com>
>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Dusty Hale
>>> Email: [email protected]
>>> Phone (Atlanta): 404.474.3754
>>> Phone (Toll Free USA): 877.841.3370
>>> Website: www.DustyHale.com <http://www.DustyHale.com>
>>>
>>
>>
>>
>>
>> -------------------------------------------------------------
>> To unsubscribe from this list, manage your profile @
>> http://www.acfug.org?fa=login.edituserform
>>
>> For more info, see http://www.acfug.org/mailinglists
>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
>> List hosted by http://www.fusionlink.com
>> -------------------------------------------------------------
>>
>>
>>
>>
>
>
> --
> Dusty Hale
> Email: [email protected]
> Phone (Atlanta): 404.474.3754
> Phone (Toll Free USA): 877.841.3370
> Website: www.DustyHale.com
>
>
>
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @
> http://www.acfug.org?fa=login.edituserform
>
> For more info, see http://www.acfug.org/mailinglists
> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by FusionLink <http://www.fusionlink.com>
> -------------------------------------------------------------
--
Dusty Hale
Email: [email protected]
Phone (Atlanta): 404.474.3754
Phone (Toll Free USA): 877.841.3370
Website: www.DustyHale.com
