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 "">AND<cfelse>WHERE</cfif>
funding LIKE UPPER('%#Form.funding#%')
</cfif>
<cfif form.comapproval gt "">
<cfif form.orgname gt "" or form.funding gt "">AND<cfelse>WHERE</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="#000000" face="Verdana"><span class="black10">
<p>
<input type="text" class=mini name="orgname" size=22 style="WIDTH:
200px"><b> Organization Name</b>
</p>
<input type="text" class=mini name="funding" size=22 style="WIDTH:
200px"><b>Source of Funding</b>
<p>
<input type="text" class=mini name="commapproval" size=22 style="WIDTH:
200px"><b>Committee Approval Date</b>
</p>
<p>
<input type="submit" value="Search" name="B1">
<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=subscribe&forumid=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
