asked on

searching on multiple <form action> values

Hello again. Couldn't find what I was looking for within the solutions area so i'm asking the question. I'm getting an coldfusion error message when putting search values in any or all of the search boxes of the search webpage (see code below).

<!--- search boxes are on this page --->
<!-- Service Requests Tab code starts here -->

<cflayoutarea title="<span style='color:Green'>Service</span>" name="tab2" selected="#t2selected#" style="background-color:##B2B3A3;">
 


<br>

<table align="center">
<font size=6 align="center"><b><i>Service Requests</i></b></font>
<td align="center"><img src="http://127.0.0.1:8500/TicketTracking/pics/srs.bmp" width=125 height=125 alt="service request image.jpg" /></td>
</tr>
</table>


<table align="left">
<html>


<body>
<font size=5><i><u>Search for a Service Request</u></i></font><br><br>
<form action="searchsrstn.cfm?user_name=<cfoutput>#url.user_name#</cfoutput>" method="post" name="search">
<tr>
<td><b>Ticket#:</b></td>
<td><input type="text" name="tn" size="20" value=""></td>
<td><input type="submit" name="searchb" value="Search"></td>
</tr>
</form>



<form action="searchsrsln.cfm?user_name=<cfoutput>#url.user_name#</cfoutput>" method="post" name="search">
<tr>
<td><b>Customer Last Name:</b></td>
<td><input type="text" name="ln" size="20" value=""></td>
<td><input type="submit" name="searchb" value="Search"></td>
</tr>
</form>


<form action="searchsrsfn.cfm?user_name=<cfoutput>#url.user_name#</cfoutput>" method="post" name="search">
<tr>
<td><b>Customer First Name:</b></td>
<td><input type="text" name="fn" size="20" value=""></td>
<td><input type="submit" name="searchb" value="Search"></td>
</tr>
</form>
    
</body>
</html>
</table>



 </cflayoutarea>

<br>

</body>
</html>
</td>

</tr>
</table>

 </cflayoutarea>

<br>

</cflayout>

<!--- Below is the resulting page after the submit button is clicked on the page above --->

<!--- your dsn ... --->
<cfset yourDSN = "SSPSRs">

<body>
<a href="trackertabs.cfm?user_name=<cfoutput>#url.user_name#</cfoutput>&tab=2">
<img src="http://127.0.0.1:8500/TicketTracking/pics/imagesCA6WFWX0.jpg" width=50 height=50 alt="go back to service tab" border="0" /></a> 
</body>
<br>


<title>Search Results for Ticket #(<cfoutput>#form.tn#</cfoutput>) 
</title>


<table width="100%" height="5%" border="1" cellspacing="0" bordercolor="#FFFFff" id="AutoNumber1">
<tr>
<td width="100%" align="center" bgcolor="#ffffff" bordercolor="#ffffff" bordercolorlight="#FFFFff" bordercolordark="#FFFFff">
<b><font color="#000000" size="4">Search Results for Service Request # <cfoutput>#form.tn#</cfoutput></b></font>
<br>
<font size="2">For "Active" tickets Click on Service Request # below to edit ticket.<br><b>RESOLVED TICKETS CANNOT BE EDITED!</b></td>
</tr>
</table>  

<CFQUERY NAME="tn" DATASOURCE="#yourDSN#">
select * from Issues where ticket_num = <cfoutput>#form.tn#</cfoutput> or Last_Name = <cfoutput>#form.ln#</cfoutput> or First_Name =
<cfoutput>#form.fn#</cfoutput> 
</cfquery> 

<br>
<br>
                                       


	<!--- table headers --->
    <table border='0' width='100%' align='center' summary='script output'>
    <tr bgcolor="#cc9966">
        <th bgcolor="#cc9966"><font size=2><b>Service<br> Request #</b></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Request</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Network</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Assigned To</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Last Name</strong></font></th>
	<th bgcolor="#cc9966"><font size=2></strong>First Name</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Opened Date</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Status</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Priority</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Description</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Date Completed</strong></font></th>
        <th bgcolor="#cc9966"><font size=2></strong>Comments</strong></font></th>
        </tr>





<!--- Otherwise we simply output all data --->
 
        <CFOUTPUT query="tn">
            <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('cccccc'))#">
            <CFIF #Status# is 'Resolved'>
	    <td><font size=2>#Ticket_Num#</font></td>
            <cfelse>
            <td><font size=2><a href="http://127.0.0.1:8500/TicketTracking/mysrs.cfm?user_name=<cfoutput>#url.user_name#</cfoutput>">#Ticket_num#</a></font></td>
            </cfif>
	    <td><font size=2>#Title#</font></td>
            <td><font size=2>#Network#</font></td>
            <td><font size=2>#Assigned_To#</font></td>
            <td><font size=2>#Last_Name#</font></td>
	    <td><font size=2>#First_Name#</font></td>
            <td><font size=2>#DateFormat(Opened_Date,'mm/dd/yyyy')#</font></td>
            <td><font size=2>#Status#</font></td>
            <td><font size=2>#Priority#</font></td>
            <td><font size=2>#Description#</font></td>
            <td><font size=2>#DateFormat(Date_Completed,'mm/dd/yyyy')#</font></td>
            <td><font size=2>#Comments#</font></td>
            
	       
        </tr>
        </CFOUTPUT>
 
</table>
</body>
</html>

Open in new window

_agx_

Whenever you get an error, always post the full error message. Also if it's a db error, be sure to include the db you're using (MS SQL 2005, MySQL, ...)

cfEngineers

Do not put cfoutput in your cfquery
select * from Issues where ticket_num = <cfoutput>#form.tn#</cfoutput> or Last_Name = <cfoutput>#form.ln#</cfoutput> or First_Name =

instead use

select *
from Issues
where ticket_num = #form.tn#
or Last_Name = '#form.ln#'
or First_Name = '#form.fn#'

_agx_

where ticket_num = #form.tn#

Actually it's better to use cfqueryparam for ALL form values. But I see a few other problems ;-) So I'll wait to see the error message.

SidFishes

"Actually it's better to use cfqueryparam for ALL form values. "

I'd go as far as saying "It's completely insane not to use cfqueryparam for all from values" 8^)

Jerome Slaughter

ASKER

Used the code suggestion above and now receiving the following error:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ticket_num = or Last_Name = 'Jones' or First_Name = '''.

_agx_

You should use cfqueryparam on all of your parameters. We don't know the data types of your columns: ticket_num, Last_name, First_name. But assuming they're all type "text" cf_sql_varchar will work

<CFQUERY NAME="tn" DATASOURCE="#yourDSN#">
SELECT *
FROM Issues
WHERE ticket_num = <cfqueryparam value="#form.tn#" cfsqltype="cf_sql_varchar">
OR Last_Name = <cfqueryparam value="#form.ln#" cfsqltype="cf_sql_varchar">
OR First_Name = <cfqueryparam value="#form.fn#" cfsqltype="cf_sql_varchar">
</cfquery>

Jerome Slaughter

ASKER

1. I can search by ticket_num successfully.
2. Getting the following error when attempting to search by last name or first name:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

3. Both input types for last name and first name values = text
4. Data types of last_name and first_name fields are also text

_agx_

What's the data type of the ticket_num column - "text" or some kind of number?

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jerome Slaughter

ASKER

your suggested code above did the trick. Thanks for your assistance. It was a big help.

_agx_

Glad I could help.

searching on multiple &lt;form action&gt; values

searching on multiple <form action> values