This should work on ORACLE8/CF4.5.1 (I'm developping for that platform right now) I can't make much of your error. Can you post your entire cfquery. CFQUERYPARAM is used for parameterized sql. It is usually good for performence and it helps to avoid sql hacks. cfsqltype describes the datatype of your column (in this case a VARCHAR2). You can check out the help or devnet (http://www.macromedia.com/desdev/articles/ben_forta_faster.html)
-----Oorspronkelijk bericht----- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 11:55 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Thanks for your solution but it does not work, I am using CF 4.5 and Oracle 8. I am getting the following error when using your code, is it not compatible with 4.5 ? and what is the need for <cfqueryparam cfsqltype="CF_SQL_VARCHAR" ?? Error Occurred While Processing Request Error Diagnostic Information CFQuery The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (61:1) to (62:59 ----- Original Message ----- From: "Pascal Peters" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, February 26, 2003 10:12 AM Subject: RE: Correct Syntax for this piece of SQL ?? > If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match one of the criteria you entered, this should work: > > SELECT * > FROM funding > WHERE 0=1 > <cfif Len(Trim(form.orgname))> > OR UPPER(orgname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.orgname#%">) > </cfif> > <cfif Len(Trim(form.funding))> > OR UPPER(funding) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.funding#%">) > </cfif> > <cfif Len(Trim(form.commapproval))> > OR UPPER(commapproval) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.commapproval#%">) > </cfif> > > This will have no result if you don't enter a value at all. If you want to match all if you don't provide any value, you can add a cfif around the entire where clause > > <cfif Len(Trim(form.orgname&form.funding&form.commapproval))> > > -----Oorspronkelijk bericht----- > Van: Ian Vaughan [mailto:[EMAIL PROTECTED] > Verzonden: wo 26/02/2003 10:20 > Aan: CF-Talk > CC: > Onderwerp: Re: Correct Syntax for this piece of SQL ?? > > > > Hi > > I have just tried the following query > > select * from > funding > Where ( orgname LIKE '%#Form.orgname#%' ) > OR ( funding LIKE '%#Form.funding#%' ) > OR ( commapproval LIKE '%#Form.commapproval#%' ) > ORDER BY recordid > > however whatever I type in the fields it returns all results ??? > > All 3 criteria do no have to be met, > > Any ideas why the above sql is returning all results ?? > > Ian > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4