Thanks Robin and Duncan.
Finally, I figured it out. The code I changed to, as follow,
******Just Part of them, but you will understand what I did*******
<cfif IsDefined("uln")>
<cfif len(select_condition) gt 0>
<cfset select_condition="#select_condition# AND lastname=^#uln#^">
<cfelse>
<cfset select_condition="lastname=^#uln#^">
</cfif>
</cfif>
<cfif len(select_condition) gt 0>
select * from user where
<cfoutput>#replace(select_condition,"^","'","all")#</cfoutput>
<cfelse>
Select * from user
</cfif>
Cheers guys, good luck
Bo Jiang
----- Original Message -----
From: "Robin Griffiths" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 27, 2003 3:30 PM
Subject: Re: [ cf-dev ] string and numeric
Try using the preserveSingleQuotes() function
----- Original Message -----
From: Bo Jiang
To: [EMAIL PROTECTED]
Sent: Thursday, November 27, 2003 1:43 PM
Subject: Re: [ cf-dev ] string and numeric
Hi,
Please, any body can give me a clue about my question? Is that infeasible in CF?
Is there any alternative way to do that? Thanks a lot.
Advice welcome
Bo Jiang
----- Original Message -----
From: "Bo Jiang" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 27, 2003 9:04 AM
Subject: [ cf-dev ] string and numeric
> Hi,
> I am doing two functions in CF a component. One has arguments all in numeric, it
works fine, another has some arguments in string, when I quote the variable in a SQL,
it says syntax error. As far as I can see, that because the single quotation marks are
converted to double quotation marks automatically. What can I do to get it work?
> Thanks
>
> Bo
>
> ********This one with string arguments doesn't work******
> <cffunction name="getuserinfo" access="public" returntype="query">
> <cfargument name="uid" type="numeric" required="no">
> <cfargument name="unm" type="string" required="no">
> <cfargument name="uem" type="string" required="no">
> <cfargument name="ufn" type="string" required="no">
> <cfargument name="uln" type="string" required="no">
> <cfset select_condition="">
> <cfquery name="usrinfo" datasource="opps">
> <cfif IsDefined("uid")>
> <cfset select_condition="userid=#uid#">
> </cfif>
> <cfif IsDefined("unm")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND username='#unm#'">
> <cfelse>
> <cfset select_condition="username='#unm#'">
> </cfif>
> </cfif>
> <cfif IsDefined("uem")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND email='#uem#'">
> <cfelse>
> <cfset select_condition="email='#uem#'">
> </cfif>
> </cfif>
> <cfif IsDefined("ufn")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND firstname='#ufn#'">
> <cfelse>
> <cfset select_condition="firstname='#ufn#'">
> </cfif>
> </cfif>
> <cfif IsDefined("uln")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND lastname='#uln#'">
> <cfelse>
> <cfset select_condition="lastname='#uln#'">
> </cfif>
> </cfif>
> <cfif len(select_condition) gt 0>
> Select * from user where #select_condition#
> <cfelse>
> Select * from user
> </cfif>
> </cfquery>
> <cfreturn usrinfo>
> </cffunction>
>
> ******This one with all numeric arguments works fine*********
> <cffunction name="getads" access="public" returntype="query">
> <cfargument name="eid" type="numeric" required="no">
> <cfargument name="uid" type="numeric" required="no">
> <cfargument name="cid" type="numeric" required="no">
> <cfargument name="pid" type="numeric" required="no">
> <cfargument name="rid" type="numeric" required="no">
> <cfset select_condition="">
> <cfquery name="adsout" datasource="opps">
> <cfif IsDefined("eid")>
> <cfset select_condition="eventid=#eid#">
> </cfif>
> <cfif IsDefined("uid")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND userid=#uid#">
> <cfelse>
> <cfset select_condition="userid=#uid#">
> </cfif>
> </cfif>
> <cfif IsDefined("cid")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND categoryid=#cid#">
> <cfelse>
> <cfset select_condition="categoryid=#cid#">
> </cfif>
> </cfif>
> <cfif IsDefined("pid")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND productid=#pid#">
> <cfelse>
> <cfset select_condition="productid=#pid#">
> </cfif>
> </cfif>
> <cfif IsDefined("rid")>
> <cfif len(select_condition) gt 0>
> <cfset select_condition="#select_condition# AND rank=#rid#">
> <cfelse>
> <cfset select_condition="rank=#rid#">
> </cfif>
> </cfif>
> <cfif len(select_condition) gt 0>
> Select * from ads where #select_condition#
> <cfelse>
> Select * from ads
> </cfif>
> </cfquery>
> <cfreturn adsout>
> </cffunction>
>
