RE: Correct Syntax for this piece of SQL ??

[Pascal Peters]

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
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

                                Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4