So just to let you all know. I did find a solution. I basically rolled my code back to the way it was yesterday because it always worked before. Then I made the same little update I made today AGAIN (just adding a reference to one other column in the query). Now it works.
I still don't for the life of me get how I can output the string of SQL and run it fine in both a query analyzer and and in a cfquery tag yet when I pass it in as a variable, CF adds additional single quotes on its own. That is a new and very bizarre thing to see for me. Well I can't figure out but I did at least get past it so I can move on but still the curiosity is killing me. Thanks to all that replied and have a great day and a great rest of the week. On Wed, Jan 27, 2010 at 3:53 PM, Dusty Hale <du...@climbonline.com> 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 <fcg0...@wctel.net>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 >>> <teddyrpa...@gmail.com<mailto: >>> teddyrpa...@gmail.com>> 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 - teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com> >>> >>> >>> >>> >>> On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale <du...@climbonline.com >>> <mailto:du...@climbonline.com>> 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 >>> <teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com>> 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 - teddyrpa...@gmail.com >>> <mailto:teddyrpa...@gmail.com> >>> >>> >>> >>> >>> >>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale >>> <du...@climbonline.com <mailto:du...@climbonline.com>> 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: du...@dustyhale.com >>> Phone (Atlanta): 404.474.3754 >>> Phone (Toll Free USA): 877.841.3370 >>> Website: www.DustyHale.com <http://www.DustyHale.com> >>> >>> >>> >>> >>> >>> >>> -- >>> Dusty Hale >>> Email: du...@dustyhale.com >>> 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: du...@dustyhale.com > Phone (Atlanta): 404.474.3754 > Phone (Toll Free USA): 877.841.3370 > Website: www.DustyHale.com > -- Dusty Hale Email: du...@dustyhale.com Phone (Atlanta): 404.474.3754 Phone (Toll Free USA): 877.841.3370 Website: www.DustyHale.com
