• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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

0
jslaught
Asked:
jslaught
1 Solution
 
_agx_Commented:
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, ...)
0
 
cfEngineersCommented:
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#'

0
 
_agx_Commented:
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.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SidFishesCommented:
"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^)


0
 
jslaughtAuthor Commented:
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 = '''.
0
 
_agx_Commented:
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>

0
 
jslaughtAuthor Commented:
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
0
 
_agx_Commented:
What's the data type of the ticket_num column - "text" or some kind of number?
0
 
_agx_Commented:
Just a hunch but is "ticket_num" an autonumber (numeric) column?  


If it is, try adjusting your query to use the correct sql type, and use val() to make sure #form.tn# is always numeric

ie
...
WHERE  ticket_num = <cfqueryparam value="#val(form.tn)#" cfsqltype="cf_sql_integer">
OR Last_Name = <cfqueryparam value="#form.ln#" cfsqltype="cf_sql_varchar">
OR  First_Name = <cfqueryparam value="#form.fn#" cfsqltype="cf_sql_varchar">
0
 
jslaughtAuthor Commented:
your suggested code above did the trick. Thanks for your assistance. It was a big help.
0
 
_agx_Commented:
Glad I could help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now