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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
