?
Solved

<cfupdate> based off of a search value

Posted on 2011-05-06
25
Medium Priority
?
383 Views
Last Modified: 2012-05-11
I've been successful at doing a <cfupdate> on a query results page based on a user_name passed through the url. I would like to do the same thing but based on a search value from another webpage.

1. There is a search capability on one webpage that allows the user to search on ticket# or customer last name or customer first name from the Microsoft Access database.
3. After the search button is clicked the values are then passed to another webpage which displays the result record from the search.
4. I would like to allow the user to be able to edit that one record on that page instead of having them do another click to a 3rd page.

I currently do the search and a blank screen displays. No error messages just a blank screen with the table headings.

Code included below.


<!--- query to put a list of of Request options in a dropdown box during an edit --->
<cfquery name="dropdownvalues" datasource="sspsrs">
    SELECT Title_Name
    FROM IssueTitles
</cfquery>

<!--- query to put a list of of SSP Internal network names from the database in a dropdown box during an edit --->
<cfquery name="ddvsnetwork" datasource="sspsrs">
    SELECT Network
    FROM Network
</cfquery>

<!--- query to put a list of "Assigned To" names from the database in a dropdown box during an edit --->
<cfquery name="at" datasource="sspsrs">
    SELECT Assigned_To
    FROM Assigned_To
</cfquery>

<!--- query to put a list of customer "Last Names" from the database in a dropdown box during an edit --->
<cfquery name="ln" datasource="sspsrs">
    SELECT Last_Name
    FROM Customers
</cfquery>

<!--- query to put a list of customer "First Names" from the database in a dropdown box during an edit --->
<cfquery name="fn" datasource="sspsrs">
    SELECT First_Name
    FROM Customers
</cfquery>

<cfquery name="rstatus" datasource="sspsrs">
    SELECT status
    FROM Status
</cfquery> 

<cfquery name="pr" datasource="sspsrs">
    SELECT priority
    FROM priority
</cfquery> 


<!--- 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">
<font color="#000000" size="4">Search Results for Service Request: # <b><cfoutput>#form.tn#</cfoutput></b></font><br>
<font color="#000000" size="4">Search Results for Last Name: <b><cfoutput>#form.ln#</cfoutput></b></font><br>
<font color="#000000" size="4">Search Results for First Name: <b><cfoutput>#form.fn#</cfoutput></b></font>
<br>
<br>
<font size="2">For "Active" tickets Click on Service Request # below to edit ticket.<br><br><b>RESOLVED SERVICE REQUESTS CANNOT BE EDITED!</b></td>
</tr>
</table>  


<!--- ensure variables always exist. default to invalid values --->
<cfparam name="url.user_name" default="">
<cfparam name="URL.From" default="">
<cfparam name="URL.ID"  default="-1">
<cfset wasUpdated = false>

<!-- If URL.From is defined and equals DoEdit, then update the table -->
<CFIF URL.From eq "DoEdit">
    <CFUPDATE DATASOURCE="#yourDSN#" TABLENAME="Issues">
    <!--- clear the id to mark the update complete --->
    <cfset URL.id = -1>
    <cfset wasUpdated = true>
</CFIF>


<CFQUERY NAME="tn" DATASOURCE="#yourDSN#">
<!---select * from Issues where ticket_num = <cfoutput>#form.tn#</cfoutput> --->
SELECT * 
FROM    Issues 
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">

</cfquery> 

<br>
<br>
                                       
<!-- If DoUpdate has been run a short note is given -->
    <CFIF wasUpdated>
        <p><b>Thanks for your update!</b>
    </CFIF>
    

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


<!--- We are editing a single record .... --->
    <CFIF URL.ID IS tn.Ticket_Num>
        <CFOUTPUT query="tn">
            <cfform action="searchsrs.cfm?user_name=#url.user_name#&From=DoEdit&ID=#Ticket_num#" method="post">
                <tr bgcolor="yellow">
                <td><font size=2>#Ticket_num#</font></td>

                <td><font size=2>#Title#<br><br>
		<select name="Title">
		<cfloop query="dropdownvalues">
		<option value="#dropdownvalues.title_name#"<cfif tn.title eq dropdownvalues.title_name>selected="selected"</cfif>>#dropdownvalues.title_name#</option>
		</cfloop>
		</select></font></td>

		 <td><font size=2>#Network#<br><br>
		<select name="Network">
		<cfloop query="ddvsnetwork">
		<option value="#ddvsnetwork.Network#"<cfif tn.Network eq ddvsnetwork.Network>selected="selected"</cfif>>#ddvsnetwork.Network#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#Assigned_To#<br><br>
		<select name="Assigned_To">
		<cfloop query="at">
		<option value="#at.Assigned_To#"<cfif tn.Assigned_To eq at.Assigned_To>selected="selected"</cfif>>#at.Assigned_To#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#Last_Name#<br><br>
		<select name="Last_Name">
		<cfloop query="ln">
		<option value="#ln.Last_Name#"<cfif tn.Last_Name eq ln.Last_Name>selected="selected"</cfif>>#ln.Last_Name#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#First_Name#<br><br>
		<select name="First_Name">
		<cfloop query="fn">
		<option value="#fn.First_Name#"<cfif tn.First_Name eq fn.First_Name>selected="selected"</cfif>>#fn.First_Name#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2><cfinput type="datefield" name="Opened_Date" size="10" width="10" mask="mm/dd/yyyy" value="#DateFormat(Opened_Date,'mm/dd/yyyy')#"></font></td>
                
		<td><font size=2>#Status#<br><br>
		<select name="Status">
		<cfloop query="rstatus">
		<option value="#rstatus.Status#"<cfif tn.Status eq rstatus.Status>selected="selected"</cfif>>#rstatus.Status#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2>#Priority#<br><br>
		<select name="priority">
		<cfloop query="pr">
		<option value="#pr.priority#"<cfif tn.priority eq pr.priority>selected="selected"</cfif>>#pr.priority#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2><input type="text" name="Description" size="80" value="#Description#"></font></td>
                <td><font size=2><cfinput type="datefield" name="Date_Completed" size="10" width="10" mask="mm/dd/yyyy" value="#DateFormat(Date_Completed,'mm/dd/yyyy')#"></font></td>
                <td><font size=2><input type="text" name="Comments" size="80" value="#Comments#"></font></td>
                </tr>
                <tr><td colspan="3">
                    <cfinput type="hidden" name="Ticket_num" value="#URL.ID#"><br><br><br>
                    <input type="submit">
                    </td>
                </tr>    
            </cfform>
        </CFOUTPUT>



<!--- 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=<cfoutput>#form.tn#</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>
            

<!--- This is where the Edit button and form is created --->
            <td width="0%">
            <form action="searchsrs1.cfm?user_name=#url.user_name#&From=Edit&ID=#Ticket_num#" method="post">
                <input type="submit" value="Edit">
            </form>
            </td>
	       
        </tr>
        </CFOUTPUT>
 
</CFIF>
</table>
</body>
</html>

Open in new window

0
Comment
Question by:jslaught
  • 13
  • 12
25 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 35707967
Is this the same code from your other thread? If yes, wouldn't it be simpler to just re-use that page instead of copying the code an creating a new one?

0
 

Author Comment

by:jslaught
ID: 35708056
I am using the same code-same page. Let me continue to troubleshoot what I'm doing then. It just seems different since i'm trying to do the same function from before but from a search page.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35708193
based on a user_name passed through the url. I would like to do the same thing but based on a search value from another webpage.

If you're accessing it from multiple places, I'd pass the initial search values in the URL instead of using <form method="post">.  It'll simplify certain parts of the code. Plus, most search forms use method="get" anyway.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

Author Comment

by:jslaught
ID: 35714298
Could you please provide a simple example of your previous suggestion? I need a little more assistance in order to understand. Thanks.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35721639
In your original search forms just change method "post"

     <form action="searchsrsln.cfm?..." method="post" name="search">

.. to method="get". Then those form field values will be passed in the URL scope instead of FORM.

     <form action="searchsrsln.cfm?..." method="get" name="search">
0
 

Author Comment

by:jslaught
ID: 35723329
just the code to  <form action="searchsrsln.cfm?..." method="get" name="search"> where appropriate is still giving me blank results. what else might I be doing wrong?

code below:

<!--- query to put a list of of Request options in a dropdown box during an edit --->
<cfquery name="dropdownvalues" datasource="sspsrs">
    SELECT Title_Name
    FROM IssueTitles
</cfquery>

<!--- query to put a list of of SSP Internal network names from the database in a dropdown box during an edit --->
<cfquery name="ddvsnetwork" datasource="sspsrs">
    SELECT Network
    FROM Network
</cfquery>

<!--- query to put a list of "Assigned To" names from the database in a dropdown box during an edit --->
<cfquery name="at" datasource="sspsrs">
    SELECT Assigned_To
    FROM Assigned_To
</cfquery>

<!--- query to put a list of customer "Last Names" from the database in a dropdown box during an edit --->
<cfquery name="ln" datasource="sspsrs">
    SELECT Last_Name
    FROM Customers
</cfquery>

<!--- query to put a list of customer "First Names" from the database in a dropdown box during an edit --->
<cfquery name="fn" datasource="sspsrs">
    SELECT First_Name
    FROM Customers
</cfquery>

<cfquery name="rstatus" datasource="sspsrs">
    SELECT status
    FROM Status
</cfquery> 

<cfquery name="pr" datasource="sspsrs">
    SELECT priority
    FROM priority
</cfquery> 


<!--- 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">
<font color="#000000" size="4">Search Results for Service Request: # <b><cfoutput>#form.tn#</cfoutput></b></font><br>
<font color="#000000" size="4">Search Results for Last Name: <b><cfoutput>#form.ln#</cfoutput></b></font><br>
<font color="#000000" size="4">Search Results for First Name: <b><cfoutput>#form.fn#</cfoutput></b></font>
<br>
<br>
<font size="2">For "Active" tickets Click on Service Request # below to edit ticket.<br><br><b>RESOLVED SERVICE REQUESTS CANNOT BE EDITED!</b></td>
</tr>
</table>  


<!--- ensure variables always exist. default to invalid values --->
<cfparam name="url.user_name" default="">
<cfparam name="URL.From" default="">
<cfparam name="URL.ID"  default="-1">
<cfset wasUpdated = false>

<!-- If URL.From is defined and equals DoEdit, then update the table -->
<CFIF URL.From eq "DoEdit">
    <CFUPDATE DATASOURCE="#yourDSN#" TABLENAME="Issues">
    <!--- clear the id to mark the update complete --->
    <cfset URL.id = -1>
    <cfset wasUpdated = true>
</CFIF>


<CFQUERY NAME="tn" DATASOURCE="#yourDSN#">
<!---select * from Issues where ticket_num = <cfoutput>#form.tn#</cfoutput> --->
SELECT * 
FROM    Issues 
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">

</cfquery> 

<br>
<br>
                                       
<!-- If DoUpdate has been run a short note is given -->
    <CFIF wasUpdated>
        <p><b>Thanks for your update!</b>
    </CFIF>
    

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


<!--- We are editing a single record .... --->
    <CFIF URL.ID IS tn.Ticket_Num>
        <CFOUTPUT query="tn">
            <cfform action="searchsrs.cfm?user_name=#url.user_name#&From=DoEdit&ID=#Ticket_num#" method="post">
                <tr bgcolor="yellow">
                <td><font size=2>#Ticket_num#</font></td>

                <td><font size=2>#Title#<br><br>
		<select name="Title">
		<cfloop query="dropdownvalues">
		<option value="#dropdownvalues.title_name#"<cfif tn.title eq dropdownvalues.title_name>selected="selected"</cfif>>#dropdownvalues.title_name#</option>
		</cfloop>
		</select></font></td>

		 <td><font size=2>#Network#<br><br>
		<select name="Network">
		<cfloop query="ddvsnetwork">
		<option value="#ddvsnetwork.Network#"<cfif tn.Network eq ddvsnetwork.Network>selected="selected"</cfif>>#ddvsnetwork.Network#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#Assigned_To#<br><br>
		<select name="Assigned_To">
		<cfloop query="at">
		<option value="#at.Assigned_To#"<cfif tn.Assigned_To eq at.Assigned_To>selected="selected"</cfif>>#at.Assigned_To#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#Last_Name#<br><br>
		<select name="Last_Name">
		<cfloop query="ln">
		<option value="#ln.Last_Name#"<cfif tn.Last_Name eq ln.Last_Name>selected="selected"</cfif>>#ln.Last_Name#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#First_Name#<br><br>
		<select name="First_Name">
		<cfloop query="fn">
		<option value="#fn.First_Name#"<cfif tn.First_Name eq fn.First_Name>selected="selected"</cfif>>#fn.First_Name#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2><cfinput type="datefield" name="Opened_Date" size="10" width="10" mask="mm/dd/yyyy" value="#DateFormat(Opened_Date,'mm/dd/yyyy')#"></font></td>
                
		<td><font size=2>#Status#<br><br>
		<select name="Status">
		<cfloop query="rstatus">
		<option value="#rstatus.Status#"<cfif tn.Status eq rstatus.Status>selected="selected"</cfif>>#rstatus.Status#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2>#Priority#<br><br>
		<select name="priority">
		<cfloop query="pr">
		<option value="#pr.priority#"<cfif tn.priority eq pr.priority>selected="selected"</cfif>>#pr.priority#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2><input type="text" name="Description" size="80" value="#Description#"></font></td>
                <td><font size=2><cfinput type="datefield" name="Date_Completed" size="10" width="10" mask="mm/dd/yyyy" value="#DateFormat(Date_Completed,'mm/dd/yyyy')#"></font></td>
                <td><font size=2><input type="text" name="Comments" size="80" value="#Comments#"></font></td>
                </tr>
                <tr><td colspan="3">
                    <cfinput type="hidden" name="Ticket_num" value="#URL.ID#"><br><br><br>
                    <input type="submit">
                    </td>
                </tr>    
            </cfform>
        </CFOUTPUT>



<!--- 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=<cfoutput>#form.tn#</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>
            

<!--- This is where the Edit button and form is created --->
            <td width="0%">
            <form action="searchsrs1.cfm?user_name=#url.user_name#&From=Edit&ID=#Ticket_num#" method="post">
                <input type="submit" value="Edit">
            </form>
            </td>
	       
        </tr>
        </CFOUTPUT>
 
</CFIF>
</table>
</body>
</html>

Open in new window

0
 

Author Comment

by:jslaught
ID: 35723370
oops this is the code with the code change. still getting no results. see below.


<!--- query to put a list of of Request options in a dropdown box during an edit --->
<cfquery name="dropdownvalues" datasource="sspsrs">
    SELECT Title_Name
    FROM IssueTitles
</cfquery>

<!--- query to put a list of of SSP Internal network names from the database in a dropdown box during an edit --->
<cfquery name="ddvsnetwork" datasource="sspsrs">
    SELECT Network
    FROM Network
</cfquery>

<!--- query to put a list of "Assigned To" names from the database in a dropdown box during an edit --->
<cfquery name="at" datasource="sspsrs">
    SELECT Assigned_To
    FROM Assigned_To
</cfquery>

<!--- query to put a list of customer "Last Names" from the database in a dropdown box during an edit --->
<cfquery name="ln" datasource="sspsrs">
    SELECT Last_Name
    FROM Customers
</cfquery>

<!--- query to put a list of customer "First Names" from the database in a dropdown box during an edit --->
<cfquery name="fn" datasource="sspsrs">
    SELECT First_Name
    FROM Customers
</cfquery>

<cfquery name="rstatus" datasource="sspsrs">
    SELECT status
    FROM Status
</cfquery> 

<cfquery name="pr" datasource="sspsrs">
    SELECT priority
    FROM priority
</cfquery> 


<!--- 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">
<font color="#000000" size="4">Search Results for Service Request: # <b><cfoutput>#form.tn#</cfoutput></b></font><br>
<font color="#000000" size="4">Search Results for Last Name: <b><cfoutput>#form.ln#</cfoutput></b></font><br>
<font color="#000000" size="4">Search Results for First Name: <b><cfoutput>#form.fn#</cfoutput></b></font>
<br>
<br>
<font size="2">For "Active" tickets Click on Service Request # below to edit ticket.<br><br><b>RESOLVED SERVICE REQUESTS CANNOT BE EDITED!</b></td>
</tr>
</table>  


<!--- ensure variables always exist. default to invalid values --->
<cfparam name="url.user_name" default="">
<cfparam name="URL.From" default="">
<cfparam name="URL.ID"  default="-1">
<cfset wasUpdated = false>

<!-- If URL.From is defined and equals DoEdit, then update the table -->
<CFIF URL.From eq "DoEdit">
    <CFUPDATE DATASOURCE="#yourDSN#" TABLENAME="Issues">
    <!--- clear the id to mark the update complete --->
    <cfset URL.id = -1>
    <cfset wasUpdated = true>
</CFIF>


<CFQUERY NAME="tn" DATASOURCE="#yourDSN#">
<!---select * from Issues where ticket_num = <cfoutput>#form.tn#</cfoutput> --->
SELECT * 
FROM    Issues 
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">

</cfquery> 

<br>
<br>
                                       
<!-- If DoUpdate has been run a short note is given -->
    <CFIF wasUpdated>
        <p><b>Thanks for your update!</b>
    </CFIF>
    

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


<!--- We are editing a single record .... --->
    <CFIF URL.ID IS tn.Ticket_Num>
        <CFOUTPUT query="tn">
            <cfform action="searchsrs.cfm?user_name=#url.user_name#&From=DoEdit&ID=#Ticket_num#" method="get">
                <tr bgcolor="yellow">
                <td><font size=2>#Ticket_num#</font></td>

                <td><font size=2>#Title#<br><br>
		<select name="Title">
		<cfloop query="dropdownvalues">
		<option value="#dropdownvalues.title_name#"<cfif tn.title eq dropdownvalues.title_name>selected="selected"</cfif>>#dropdownvalues.title_name#</option>
		</cfloop>
		</select></font></td>

		 <td><font size=2>#Network#<br><br>
		<select name="Network">
		<cfloop query="ddvsnetwork">
		<option value="#ddvsnetwork.Network#"<cfif tn.Network eq ddvsnetwork.Network>selected="selected"</cfif>>#ddvsnetwork.Network#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#Assigned_To#<br><br>
		<select name="Assigned_To">
		<cfloop query="at">
		<option value="#at.Assigned_To#"<cfif tn.Assigned_To eq at.Assigned_To>selected="selected"</cfif>>#at.Assigned_To#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#Last_Name#<br><br>
		<select name="Last_Name">
		<cfloop query="ln">
		<option value="#ln.Last_Name#"<cfif tn.Last_Name eq ln.Last_Name>selected="selected"</cfif>>#ln.Last_Name#</option>
		</cfloop>
		</select></font></td>

		<td><font size=2>#First_Name#<br><br>
		<select name="First_Name">
		<cfloop query="fn">
		<option value="#fn.First_Name#"<cfif tn.First_Name eq fn.First_Name>selected="selected"</cfif>>#fn.First_Name#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2><cfinput type="datefield" name="Opened_Date" size="10" width="10" mask="mm/dd/yyyy" value="#DateFormat(Opened_Date,'mm/dd/yyyy')#"></font></td>
                
		<td><font size=2>#Status#<br><br>
		<select name="Status">
		<cfloop query="rstatus">
		<option value="#rstatus.Status#"<cfif tn.Status eq rstatus.Status>selected="selected"</cfif>>#rstatus.Status#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2>#Priority#<br><br>
		<select name="priority">
		<cfloop query="pr">
		<option value="#pr.priority#"<cfif tn.priority eq pr.priority>selected="selected"</cfif>>#pr.priority#</option>
		</cfloop>
		</select></font></td>

                <td><font size=2><input type="text" name="Description" size="80" value="#Description#"></font></td>
                <td><font size=2><cfinput type="datefield" name="Date_Completed" size="10" width="10" mask="mm/dd/yyyy" value="#DateFormat(Date_Completed,'mm/dd/yyyy')#"></font></td>
                <td><font size=2><input type="text" name="Comments" size="80" value="#Comments#"></font></td>
                </tr>
                <tr><td colspan="3">
                    <cfinput type="hidden" name="Ticket_num" value="#URL.ID#"><br><br><br>
                    <input type="submit">
                    </td>
                </tr>    
            </cfform>
        </CFOUTPUT>



<!--- 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=<cfoutput>#form.tn#</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>
            

<!--- This is where the Edit button and form is created --->
            <td width="0%">
            <form action="searchsrs1.cfm?user_name=#url.user_name#&From=Edit&ID=#Ticket_num#" method="get">
                <input type="submit" value="Edit">
            </form>
            </td>
	       
        </tr>
        </CFOUTPUT>
 
</CFIF>
</table>
</body>
</html>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35723855

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">


Well first,  method="get" is going to change the scope of all of your search variables. Like I was saying they'll be passed in the URL scope, not FORM. So anywhere where you're using #form.fn#, #form.tn# and #form.ln#, they'll now be #URL.fn#, #URL.tn#, etc...

Second, if you're not seeing any results suggests the query isn't matching any of those 3 conditions. So you have to dump the URL scope  to see what those values are .. and why they're not being matched.  

Like in your other  thread, dump both the URL and FORM scope at the very top of the page. What are the results

<cfdump var="#URL#" label="URL variables">
<cfdump var="#FORM" label="FORM variables">
0
 

Author Comment

by:jslaught
ID: 35732169
I am having no luck with my troubleshooting of this. This is what i'm getting from the <cfdump>.

url variables = struct
fn = empty string (only searched on ticket # which is tn)
ln = empty string (only searched on ticket # which is tn)
searchbutton = Search
tn = 6

form variables - [struct empty]


error message is element USER_NAME i s undefined in URL.

thanks for your continued patience.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35732315
error message is element USER_NAME i s undefined in URL.

No worries :)  Regarding the error, it sounds like you didn't switch your search forms to use method="get".  It's not critical, so let's forget about that for now and figure out why your display isn't working first.

1) Go back to using the FORM variables:

2) Using the name #tn# for both your FORM field and the query name might cause a problem.  Let's switch name the query something else like "getIssues".  You're using it in several places, so be sure to update it everywhere it's used in your code.

<CFQUERY NAME="getIssues" DATASOURCE="#yourDSN#">
SELECT *
FROM    Issues
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">
</cfquery>


<CFIF URL.ID IS getIssues.Ticket_Num>
        <CFOUTPUT query="getIssues">
        ..... rest of code
0
 

Author Comment

by:jslaught
ID: 35733785
<cfdump var="#URL#" label="URL variables">
<cfdump var="#FORM#" label="FORM variables">

This is what I get now when doing the cfdump above:
======================================
URL Variables - struct
user_name = stewartd

FORM Variables - struct

FIELDNAMES TN,LN,FN,SEARCHBUTTON

FN    [empty string] (value only entered for TN)
LN    [empty string] (value only entered for TN)
SEARCHBUTTON    Search
TN    2 (this is the ticket #)
========================================

Did everything you suggested above and I do not get an error message anymore and the table headings are now displaying. But still no data in the table is displaying.

thanks,
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35733853
>>> TN    2 (this is the ticket #)

And you're sure the table contains a record for ticket_num = 2?

cfdump the query object. Does it contain any data?

<CFQUERY NAME="getIssues" DATASOURCE="#yourDSN#"> ... the sql .... </cfquery>

<!--- display query results --->
<cfdump var="#getIssues#">
...
0
 

Author Comment

by:jslaught
ID: 35734332
The table does contain a record for ticket_num =2.

cfdump of the query object shows the record for ticket_num = 2 in the query resultset.

cached = false

execution time = 0

sql = SELECT * FROM Issues WHERE ticket_num = ? OR Last_Name = ? OR First_Name = ?

sql parameters =
array 1 = 2
array 2 = empty string
array 3 = empty string
 
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35734824
cfdump of the query object shows the record for ticket_num = 2 in the query resultset.

Then the problem has to be the output code.

Oh... wait I think I see it.  There was a CFELSE in the original code. It was used to display 2 different things depending on the value of URL.ID.  It's missing from your new code. That's why there's no output.

<cfparam name="URL.ID"  default="-1">

<CFIF URL.ID IS tn.Ticket_Num> 
        .... display an edit form for 1 ticket number ...
        <cfform> .....</cfform>


<CFELSE>              <==== this is missing from the new code 

       <!--- Otherwise we simply output all data --->
        <cfoutput query="tn">....</cfoutput >

</CFIF>

Open in new window

0
 

Author Comment

by:jslaught
ID: 35734897
That worked! It now displays the correct result from the search. But..... Now I am having a problem with the cfupdate when the edit button is clicked. I am getting a "page cannot be displayed" message.
This is the part of the code that displays the edit button: Not sure why it's not working.

<!--- This is where the Edit button and form is created --->
            <td width="0%">
            <form action="searchsrs1.cfm?user_name=#url.user_name#&From=Edit&ID=#Ticket_num#" method="get">
                <input type="submit" value="Edit">
            </form>
            </td>
	       
        </tr>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35734957
>> <form action="searchsrs1.cfm?..."> <<

Is that the right URL?

>> I am getting a "page cannot be displayed" message.

Is that with Internet Explorer? If yes, it might be it's way of saying there's an error on the page.  Can you try it with another browser like FF or Safari?
0
 

Author Comment

by:jslaught
ID: 35736993
in Mozilla Firefox.. i am getting the following error:

"element TN is undefined in FORM."

tn is the value from the search page (trackertabs.cfm) that gets passed to the results page searchsrs.cfm (replaced searchsrs1.cfm). Then tn is used on the searchsrs.cfm search during the cfupdate.

Not sure why tn is not being recognized during the cfupdate.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35740207
Not sure why tn is not being recognized during the cfupdate.

The code isn't even getting that far.  Your script does multiple things,  but not all in same http request.  So different variables will exist depending on which action the user takes.  The problem is your code behaves as if the same variables exist throughout the entire process - they don't.

Ex. When you submit your search form (method=post) it creates a variable named #form.fn#

<form action="myPage.cfm" method="[b]post[/b]" name="search">
    <input type="text" name="[b]fn[/b]" size="20" value="">
    <input type="submit" name="searchb" value="Search">
</form>

Open in new window


#form.fn# only exists during that http request. If you click on the "edit" form button and reload the page, #form.fn# doesn't exist anymore! The only variables that will exist are the ones you pass in the edit form and it's url

<form action="searchsrs1.cfm?user_name=#url.user_name#&From=Edit&ID=#Ticket_num#" method="get">
<input type="submit" value="Edit">
</form>

Open in new window


... so when the page reloads, even the simple code below will cause an error. Because #form.tn# doesn't exist anymore! (Not unless you're using cfparam)

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

Open in new window


This is one of the problems of using a multi-purpose form. They can be handy but they're complex and hard to debug.  I usually recommend starting off with separate pages (simpler) and working your way up to multi-purpose formst.

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35740341
>> <form action="myPage.cfm" method="post" name="search">
>>    <input type="text" name="fn" size="20" value="">

Ignore those extra html tags ie "" and ""
0
 

Author Comment

by:jslaught
ID: 35741066
so where do I go from here? Do I revert back to simpler forms as you mentioned or is there some way to get this multi-purpose forms to work? I just can't seem to get over the hump with this.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 450 total points
ID: 35741492
It depends.  If you need to get it done fast, using separate pages is generally a) quicker to code b) easier to troubleshoot c) and takes less time to maintain - than multi/purpose scripts.  On the other hand, if this is just a learning experience OR you have the time/patience to do some debugging, then I'd be happy to help you get it working. It's your schedule, so it's up to you.

0
 

Author Comment

by:jslaught
ID: 35741591
It would be great if I can get this done by monday but it seems like it's going to take time to debug. This is a side project i've taken on for my job to create a simple service request tracking system for my IT team. I have some experience with Coldfusion and lots of experience with Microsoft Access. I did find a simpler example on the web that allows you to do insert, update and edit all in one table. It's not as fancy as I was trying to do but it may work out. Please let me know what you think. Link below.

http://tutorial40.learncf.com/
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35741800
Once you have a handle on how <form> and http requests work, debugging isn't hard. What makes your script harder is that there's a lot going on .. possibly too much.  So it's likely any maintenance will take more time as well.  

For that reason you might want to go with separate pages. Something like

1)  searchFormPage.cfm => search by last name, first name, tn number
2)  displaySearchResults.cfm => displays search results with link to "edit"
3)  addEditTicketNumber.cfm => allows users to add or edit record

The action of #3 could either be the same page or separate pages
0
 

Author Comment

by:jslaught
ID: 35754027
your suggestion of doing simple cf forms is working out better. I followed this example and things are working out good. http://tutorial40.learncf.com/ 

thanks for all your patience. here is another 50 points for your time.
0
 

Author Closing Comment

by:jslaught
ID: 35754070
was not able to do the cfupdate function I had hoped. Through the suggestions of the expert however I found out that a simplier solution worked out just as well.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question