Dusty,
How about something like this?
<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
<cfif len(drpHairColor)>
and [haircolor] like <cfqueryparam value="#drpHairColor#%"
cfsqltype="cf_sql_varchar" />
</cfif>
</cfquery>
Qualify the columns and the view and query param the search string. This
may also add some legibility as well.
Teddy R. Payne, ACCFD
Google Talk - [email protected]
On Wed, Jan 27, 2010 at 4:25 PM, Dusty Hale <[email protected]> wrote:
> 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
>
