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
                                

Reply via email to