Re: Correct Syntax for this piece of SQL ??
Ian,
Replace WHERE 0 = 1 in your current query with either WHERE 0 = 0 or WHERE 1
= 1.
Why? This is just a dummy clause that's used in a dynamic SQL statement to
let's you move past the WHERE and on to the dynamic AND/OR statements that
are based on your conditions. But this dummy WHERE clause must be true, or
processing will stop right there. Obviously, WHERE 0 = 1 is *not* true. Fix
that and you should be OK.
~Dina
- Original Message -
From: Ian Vaughan [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 9:17 AM
Subject: Re: Correct Syntax for this piece of SQL ??
Pascal
I removed the cached feature so the query looks like what I have below.
However If I search for an entry in the orgname field it displays the
result
as it should.
If I try to search in the other two fields it returns no results ???
CFQUERY datasource=liv8 name=funding
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
/CFQUERY
- Original Message -
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 2:49 PM
Subject: RE: Correct Syntax for this piece of SQL ??
You cannot use cfqueryparam with cached queries. Go back to
UPPER('%#Trim(form.var)#%')
-Oorspronkelijk bericht-
Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
Verzonden: wo 26/02/2003 14:17
Aan: CF-Talk
CC:
Onderwerp: Re: Correct Syntax for this piece of SQL ??
Pascal
Here is my query
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
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
/CFQUERY
CFIF funding.RecordCount is 0
span class=black10pbNo files found for specified
criteria/b/p
!--- ... else at least one file found ---
CFELSE
cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows -
Form.StartRow)) -1
p
CFOUTPUT
smallspan class=black10Viewing #Form.StartRow# to
#funding.RecordCount# of b#funding.RecordCount#/b records
found./font/small/span
/cfoutput
/cfif
div align=left
table width=761 border=0
tbody
tr
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Organization Name/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Funding/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Committe Approval/th
/tr
cfoutput query=funding startrow=#StartRow#
maxrows=#Form.MaxRows#
tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE
('f7f7de'))#
!--- td align=left valign=top
background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp
an class=black10#Evaluate(CurrentRow)#/td ---
td align=left valign=top span class=black10#orgname#/td
td align=left valign=topspan class=black10#funding#/td
td align=left valign=top span class=black10#commapproval#/td
/tr
/cfoutput/tbody/table
- Original Message -
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 11:34 AM
Subject: RE: Correct Syntax for this piece of SQL ??
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
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
- Original Message -
From: Jann VanOver [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 5:48 AM
Subject: Re: Correct Syntax for this piece of SQL ??
I think Lee said it too, you've got a basic logic flaw. Your query says
all
three criteria must be met.
Did you mean to say:
select * from
funding
Where ( orgname LIKE '%#Form.orgname#%' )
OR( funding LIKE '%#Form.funding#%' )
OR( commapproval LIKE '%#Form.commapproval#%' )
ORDER BY recordid
You can use parenthesis to be MORE subtle, like if you want the second and
third criteria to be required together:
Where ( orgname LIKE '%#Form.orgname#%' )
OR
( ( funding LIKE '%#Form.funding#%' ) AND ( commapproval LIKE
'%#Form.commapproval#%' ) )
On 2/25/03 6:48 AM, Ian Vaughan [EMAIL PROTECTED]
wrote:
Hi
I have the following form which is being used to search against my
database
table.
When I search using the 'orgname' field and there are matches then
results
are returned. However if I search in the other two fields then it
displays
no results found when it should bring back results ?
Is this because of my sql ??
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
select * from
funding
Where orgname LIKE UPPER('%#Form.orgname#%')
AND funding LIKE UPPER('%#Form.funding#%')
AND commapproval LIKE UPPER('%#Form.commapproval#%')
ORDER BY recordid
/CFQUERY
This is my search form
form method=POST action=/testpages/fundingresults.cfm
name=search
input type=Hidden name=MaxRows value=100
input type=hidden name=StartRow value=1
font color=#00 face=Verdanaspan class=black10
p
input type=text class=mini name=orgname size=22 style=WIDTH:
200pxb Organization Name/b
/p
input type=text class=mini name=funding size=22 style=WIDTH:
200pxbSource of Funding/b
p
input type=text class=mini name=commapproval size=22 style=WIDTH:
200pxbCommittee Approval Date/b
/p
p
input type=submit value=Search name=B1nbsp;nbsp;
input type=reset value=Reset Form name=B2
p
/form
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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.orgnameform.fundingform.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=subscribeforumid=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
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.orgnameform.fundingform.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=subscribeforumid=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
RE: Correct Syntax for this piece of SQL ??
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.orgnameform.fundingform.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=subscribeforumid=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
Re: Correct Syntax for this piece of SQL ??
Pascal
Here is my query
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
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
/CFQUERY
CFIF funding.RecordCount is 0
span class=black10pbNo files found for specified
criteria/b/p
!--- ... else at least one file found ---
CFELSE
cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows -
Form.StartRow)) -1
p
CFOUTPUT
smallspan class=black10Viewing #Form.StartRow# to
#funding.RecordCount# of b#funding.RecordCount#/b records
found./font/small/span
/cfoutput
/cfif
div align=left
table width=761 border=0
tbody
tr
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Organization Name/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Funding/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Committe Approval/th
/tr
cfoutput query=funding startrow=#StartRow# maxrows=#Form.MaxRows#
tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE ('f7f7de'))#
!--- td align=left valign=top
background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp
an class=black10#Evaluate(CurrentRow)#/td ---
td align=left valign=top span class=black10#orgname#/td
td align=left valign=topspan class=black10#funding#/td
td align=left valign=top span class=black10#commapproval#/td
/tr
/cfoutput/tbody/table
- Original Message -
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 11:34 AM
Subject: RE: Correct Syntax for this piece of SQL ??
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.orgnameform.fundingform.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
RE: Correct Syntax for this piece of SQL ??
You cannot use cfqueryparam with cached queries. Go back to
UPPER('%#Trim(form.var)#%')
-Oorspronkelijk bericht-
Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
Verzonden: wo 26/02/2003 14:17
Aan: CF-Talk
CC:
Onderwerp: Re: Correct Syntax for this piece of SQL ??
Pascal
Here is my query
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
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
/CFQUERY
CFIF funding.RecordCount is 0
span class=black10pbNo files found for specified
criteria/b/p
!--- ... else at least one file found ---
CFELSE
cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows -
Form.StartRow)) -1
p
CFOUTPUT
smallspan class=black10Viewing #Form.StartRow# to
#funding.RecordCount# of b#funding.RecordCount#/b records
found./font/small/span
/cfoutput
/cfif
div align=left
table width=761 border=0
tbody
tr
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Organization Name/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Funding/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Committe Approval/th
/tr
cfoutput query=funding startrow=#StartRow# maxrows=#Form.MaxRows#
tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE ('f7f7de'))#
!--- td align=left valign=top
background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp
an class=black10#Evaluate(CurrentRow)#/td ---
td align=left valign=top span class=black10#orgname#/td
td align=left valign=topspan class=black10#funding#/td
td align=left valign=top span class=black10#commapproval#/td
/tr
/cfoutput/tbody/table
- Original Message -
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 11:34 AM
Subject: RE: Correct Syntax for this piece of SQL ??
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
Re: Correct Syntax for this piece of SQL ??
Pascal
I removed the cached feature so the query looks like what I have below.
However If I search for an entry in the orgname field it displays the result
as it should.
If I try to search in the other two fields it returns no results ???
CFQUERY datasource=liv8 name=funding
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
/CFQUERY
- Original Message -
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 2:49 PM
Subject: RE: Correct Syntax for this piece of SQL ??
You cannot use cfqueryparam with cached queries. Go back to
UPPER('%#Trim(form.var)#%')
-Oorspronkelijk bericht-
Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
Verzonden: wo 26/02/2003 14:17
Aan: CF-Talk
CC:
Onderwerp: Re: Correct Syntax for this piece of SQL ??
Pascal
Here is my query
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
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
/CFQUERY
CFIF funding.RecordCount is 0
span class=black10pbNo files found for specified
criteria/b/p
!--- ... else at least one file found ---
CFELSE
cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows -
Form.StartRow)) -1
p
CFOUTPUT
smallspan class=black10Viewing #Form.StartRow# to
#funding.RecordCount# of b#funding.RecordCount#/b records
found./font/small/span
/cfoutput
/cfif
div align=left
table width=761 border=0
tbody
tr
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Organization Name/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Funding/th
th
background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
align=leftspan class=black10Committe Approval/th
/tr
cfoutput query=funding startrow=#StartRow# maxrows=#Form.MaxRows#
tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE
('f7f7de'))#
!--- td align=left valign=top
background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp
an class=black10#Evaluate(CurrentRow)#/td ---
td align=left valign=top span class=black10#orgname#/td
td align=left valign=topspan class=black10#funding#/td
td align=left valign=top span class=black10#commapproval#/td
/tr
/cfoutput/tbody/table
- Original Message -
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 11:34 AM
Subject: RE: Correct Syntax for this piece of SQL ??
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
RE: Correct Syntax for this piece of SQL ??
Just a stab in the dark:
Is orgname always uppercase in your DB? If so, that might explain why
searching by orgname always works but searching by the others doesn't.
You might have to do this:
...AND UPPER(funding) LIKE UPPER('%#Form.funding#%')
or EVEN (the same):
...AND UPPER(funding) LIKE '%#UCase(Form.funding)#%'
etc.
Thanks,
André
-Original Message-
From: Ian Vaughan [mailto:[EMAIL PROTECTED]
Sent: 25 February 2003 14:49
To: CF-Talk
Subject: Correct Syntax for this piece of SQL ??
Hi
I have the following form which is being used to search against my
database
table.
When I search using the 'orgname' field and there are matches then
results
are returned. However if I search in the other two fields then it
displays
no results found when it should bring back results ?
Is this because of my sql ??
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
select * from
funding
Where orgname LIKE UPPER('%#Form.orgname#%')
AND funding LIKE UPPER('%#Form.funding#%')
AND commapproval LIKE UPPER('%#Form.commapproval#%')
ORDER BY recordid
/CFQUERY
This is my search form
form method=POST action=/testpages/fundingresults.cfm
name=search
input type=Hidden name=MaxRows value=100
input type=hidden name=StartRow value=1
font color=#00 face=Verdanaspan class=black10
p
input type=text class=mini name=orgname size=22 style=WIDTH:
200pxb Organization Name/b
/p
input type=text class=mini name=funding size=22 style=WIDTH:
200pxbSource of Funding/b
p
input type=text class=mini name=commapproval size=22 style=WIDTH:
200pxbCommittee Approval Date/b
/p
p
input type=submit value=Search name=B1nbsp;nbsp;
input type=reset value=Reset Form name=B2
p
/form
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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
RE: Correct Syntax for this piece of SQL ??
Try this:
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
select * from
funding
cfif form.orgname gt
Where orgname LIKE UPPER('%#Form.orgname#%')
/cfif
cfif form.funding gt
cfif form.orgname gt ANDcfelseWHERE/cfif
funding LIKE UPPER('%#Form.funding#%')
/cfif
cfif form.comapproval gt
cfif form.orgname gt or form.funding gt ANDcfelseWHERE/cfif
commapproval LIKE UPPER('%#Form.commapproval#%')
/cfif
ORDER BY recordid
/CFQUERY
This will generate a query based on which fields actually have values to
search on.
If I understand what you are wanting to do here.
Larry Juncker
Senior Cold fusion Developer
Heartland Communications Group, Inc.
[EMAIL PROTECTED]
(515) 574-2122
CONFIDENTIALITY NOTICE
The information contained in this e-mail is intended only for the use of the
individual or entity to which it is addressed. This e-mail may contain
information that is privileged, confidential and/or personal. If the reader
of this message is not the intended recipient (or the employee or agent
responsible to deliver it to the intended recipient), you are hereby
notified that any dissemination, distribution, or copying of this
communication is prohibited.
If you have received this communication in error, please notify us at the
e-mail listed above.
-Original Message-
From: Ian Vaughan [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 25, 2003 8:49 AM
To: CF-Talk
Subject: Correct Syntax for this piece of SQL ??
Hi
I have the following form which is being used to search against my database
table.
When I search using the 'orgname' field and there are matches then results
are returned. However if I search in the other two fields then it displays
no results found when it should bring back results ?
Is this because of my sql ??
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
select * from
funding
Where orgname LIKE UPPER('%#Form.orgname#%')
AND funding LIKE UPPER('%#Form.funding#%')
AND commapproval LIKE UPPER('%#Form.commapproval#%')
ORDER BY recordid
/CFQUERY
This is my search form
form method=POST action=/testpages/fundingresults.cfm name=search
input type=Hidden name=MaxRows value=100
input type=hidden name=StartRow value=1
font color=#00 face=Verdanaspan class=black10
p
input type=text class=mini name=orgname size=22 style=WIDTH:
200pxb Organization Name/b
/p
input type=text class=mini name=funding size=22 style=WIDTH:
200pxbSource of Funding/b
p
input type=text class=mini name=commapproval size=22 style=WIDTH:
200pxbCommittee Approval Date/b
/p
p
input type=submit value=Search name=B1nbsp;nbsp;
input type=reset value=Reset Form name=B2
p
/form
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Correct Syntax for this piece of SQL ??
A bit complicated. Better
WHERE 0=0
cfif Len(form.orgname)
AND UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=%#Form.orgname#%)
/cfif
cfif Len(form.funding)
AND UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=%#Form.funding#%)
/cfif
cfif Len(form.commapproval)
AND UPPER(commapproval) LIKE UPPER(cfqueryparam
cfsqltype=CF_SQL_VARCHAR value=%#Form.commapproval#%)
/cfif
-Original Message-
From: Larry Juncker [mailto:[EMAIL PROTECTED]
Sent: dinsdag 25 februari 2003 15:59
To: CF-Talk
Subject: RE: Correct Syntax for this piece of SQL ??
Try this:
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 select *
from funding cfif form.orgname gt Where orgname LIKE
UPPER('%#Form.orgname#%') /cfif
cfif form.funding gt
cfif form.orgname gt ANDcfelseWHERE/cfif
funding LIKE UPPER('%#Form.funding#%')
/cfif
cfif form.comapproval gt
cfif form.orgname gt or form.funding gt ANDcfelseWHERE/cfif
commapproval LIKE UPPER('%#Form.commapproval#%') /cfif
ORDER BY recordid
/CFQUERY
This will generate a query based on which fields actually have values to
search on. If I understand what you are wanting to do here.
Larry Juncker
Senior Cold fusion Developer
Heartland Communications Group, Inc. [EMAIL PROTECTED]
(515) 574-2122
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct Syntax for this piece of SQL ??
I think Lee said it too, you've got a basic logic flaw. Your query says all
three criteria must be met.
Did you mean to say:
select * from
funding
Where ( orgname LIKE '%#Form.orgname#%' )
OR( funding LIKE '%#Form.funding#%' )
OR( commapproval LIKE '%#Form.commapproval#%' )
ORDER BY recordid
You can use parenthesis to be MORE subtle, like if you want the second and
third criteria to be required together:
Where ( orgname LIKE '%#Form.orgname#%' )
OR
( ( funding LIKE '%#Form.funding#%' ) AND ( commapproval LIKE
'%#Form.commapproval#%' ) )
On 2/25/03 6:48 AM, Ian Vaughan [EMAIL PROTECTED] wrote:
Hi
I have the following form which is being used to search against my database
table.
When I search using the 'orgname' field and there are matches then results
are returned. However if I search in the other two fields then it displays
no results found when it should bring back results ?
Is this because of my sql ??
CFQUERY datasource=liv8 name=funding
cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
select * from
funding
Where orgname LIKE UPPER('%#Form.orgname#%')
AND funding LIKE UPPER('%#Form.funding#%')
AND commapproval LIKE UPPER('%#Form.commapproval#%')
ORDER BY recordid
/CFQUERY
This is my search form
form method=POST action=/testpages/fundingresults.cfm name=search
input type=Hidden name=MaxRows value=100
input type=hidden name=StartRow value=1
font color=#00 face=Verdanaspan class=black10
p
input type=text class=mini name=orgname size=22 style=WIDTH:
200pxb Organization Name/b
/p
input type=text class=mini name=funding size=22 style=WIDTH:
200pxbSource of Funding/b
p
input type=text class=mini name=commapproval size=22 style=WIDTH:
200pxbCommittee Approval Date/b
/p
p
input type=submit value=Search name=B1nbsp;nbsp;
input type=reset value=Reset Form name=B2
p
/form
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
