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
