Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating records 1

Status
Not open for further replies.
scottew

scottew

IS-IT--Management
Mar 6, 2003
492
US
I'm a newbie and I am trying to create a very basic help desk system for my company.

The table below shows all the open tickets and I want to be able to click on a link to update the record.

Code: 
<table align="center" width="790" border="1" cellspacing="1" cellpadding="1">
	<cfquery name="viewOpen" datasource="helpdesk" dbtype="odbc">
	select *
	from tickets
	order by ticket_ID
	</cfquery>
  <tr>
    <td>Ticket ID</td>
    <td>Date Created</td>
    <td>Employee</td>
    <td>Category</td>
    <td>Description</td>
    <td>Tech</td>
  </tr>
  	<cfoutput query="viewOpen">
  <tr>
	<td align="left">#ticket_ID#</td>
	<td align="left">#date_created#</td>
	<td align="left">#employee#</td>
	<td align="left">#category#</td>
	<td align="left">#description#</td>
	<td align="left">#tech#</td>
  </tr>
	</cfoutput>
</table>

I tried the following link, but it didn't work.

Code: 
<td align="left"><a href="update.cfm?ticket_id>#ticket_ID#</td>

What do I need to use as a link to be able to update the record?

Thanks,
Scott
 
Sort by date Sort by votes
Modify the link code slightly. Close the anchor tag and use "=" between the parameter name (ticket_id) and value (#ticket_ID#).

Code: 
<td align="left"><a href="update.cfm?ticket_id=#ticket_ID#">#ticket_ID#</a></td>
 
Upvote 0 Downvote
Thank you cfStarlight,

On the update.cfm page, I have the following, but it still isn't working.

Code: 
<cfoutput query="getTicket">
<form action="action?update.cfm" method="post" name="update">
<table align="center" width="" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td align="right">Date Created:</td>
    <td align="left">#getTicket.date_created#</td>
  </tr>  
  <tr>
    <td align="right">Date Created:</td>
    <td align="left">#getTicket.date_created#</td>
  </tr> 
</table>
Do I need to do another query on this page?

Thanks for the help.
Scott
 
Upvote 0 Downvote
Yes, you would.

The query should use the URL.ticket_id value to get the information for the selected ticket. You can then output the query values in your form fields (textboxes, etc).

Code: 
    <cfquery name="getTicket" datasource="yourDatasource"
    SELECT  ticket_ID, date_created, employee, 
            category, description, tech
    FROM    tickets
    WHERE   ticket_ID = 
                  <cfqueryparam 
                   value="#url.ticket_id#" 
                    cfsqltype="your column data type here">
    </cfquery>
 
Upvote 0 Downvote
Thanks again, I got it working now!

Scott
 
Upvote 0 Downvote
Although I got it working, when it updates, it is update all the records not just the specific record.

On my action.cfm page, I have the following

Code: 
<cfif isDefined("url.update")>
<cfquery name="update" datasource="helpdesk" dbtype="ODBC">
	update tickets 
	set resolution = '#resolution#', date_closed = '#date_closed#', closed = '#closed#'
	where ticket_ID = <cfqueryparam 
                   value="#url.ticket_id#" 
                    cfsqltype="numeric">

</cfquery>

The ticket has been updated!
</cfif>

I had tried a couple of other things, but nothing works.

Scott
 
Upvote 0 Downvote
I think that only way that could happen is if you're running a different update statement than the one posted, or if all of the records have the same ticket_id value. Turn on debugging. What is the exact sql statement generated ?

Also "numeric" isn't a valid cfsqltype. Take a look at the documentation to find the correct type
 
Upvote 0 Downvote
On the update.cfm page, I have the following

Code: 
    <cfquery name="getTicket" datasource="yourDatasource"
    SELECT  ticket_ID, date_created, employee, 
            category, description, tech
    FROM    tickets
    WHERE   ticket_ID = 
                  <cfqueryparam 
                   value="#url.ticket_id#" 
                    cfsqltype="your column data type here">
    </cfquery>

and the information is populated into the following form.

Code: 
<cfoutput query="getTicket">
<form action="update_process.cfm" method="post" name="update">
<table align="center" width="" border="0" cellspacing="2" cellpadding="2">  <tr>
    <td align="right"><b>Ticket ID:</b></td>
    <td align="left">#getTicket.ticket_ID#</td>
  </tr>  
  <tr>
    <td align="right"><b>Date Created:</b></td>
    <td align="left">#getTicket.date_created#</td>
  </tr>
  <tr>
    <td align="right"><b>Description:</b></td>
    <td align="left">#getTicket.description#</td>
  </tr>
  <tr>
    <td align="right"><b>Tech:</b></td>
    <td align="left">#getTicket.tech#</td>
  </tr>

  <tr>
    <td align="right"><b>Resolution:</b></td>
    <td align="left"><textarea NAME="resolution" ROWS="3" COLS="40" wrap="soft"></textarea></td>
  </tr>
  <tr>
    <td align="right"><b>Date Closed:</b></td>
    <td align="left"><input type="text" name="date_closed" value="<cfoutput>#DateFormat(now(), "mm/dd/yyyy")#</cfoutput>"></a></td>
  </tr>
</table>

On the update_process.cfm page, I have the following

Code: 
<cfquery name="update" datasource="helpdesk" dbtype="ODBC">
	update tickets 
	set resolution = '#resolution#', 
	date_closed = '#date_closed#', 
		closed = '#closed#'
	    where ticket_id = <cfqueryparam value="#url.ticket_id#" cfsqltype="cf_sql_numeric">
</cfquery>

When I run this, it gets to the update_process.cfm page, but nothing is displayed on the page.

I am trying to figure out the debugging, but I'm a beginner when it comes to this CF stuff.

Thanks again for all the help.
Scott

 
Upvote 0 Downvote
In the first query that retrieves the ticket information, just fix the cfsqltype to match the datatype of the Ticket_ID column in your db table. For example, if column type is "integer" use cf_sql_integer, etc.

Code: 
  WHERE   ticket_ID = <cfqueryparam
                   value="#url.ticket_id#"
                    cfsqltype="CF_SQL_INTEGER">

Your form looks okay, but a few minor tweaks

1) You need to store the Ticket_ID in a hidden field. So the update query will know which record to update.

2) You need to close the <input> tag properly. See the comment below

3) I noticed your update query uses a form field named "Closed", but I don't see it in your form. If that field is missing it will cause an error in your update query. Is "Closed" a value in your query?

Code: 
<cfoutput query="getTicket">
<form action="update_process.cfm" method="post" name="update">
<!--- include the ticket_ID as a hidden field --->
<input type="hidden" name="ticket_ID" value="#getTicket.ticket_ID#">
<table align="center" width="" border="0" cellspacing="2" cellpadding="2">  <tr>
    <td align="right"><b>Ticket ID:</b></td>
    <td align="left">#getTicket.ticket_ID#</td>
  </tr>  
  <tr>
    <td align="right"><b>Date Created:</b></td>
    <td align="left">#getTicket.date_created#</td>
  </tr>
  <tr>
    <td align="right"><b>Description:</b></td>
    <td align="left">#getTicket.description#</td>
  </tr>
  <tr>
    <td align="right"><b>Tech:</b></td>
    <td align="left">#getTicket.tech#</td>
  </tr>

  <tr>
    <td align="right"><b>Resolution:</b></td>
    <td align="left"><textarea NAME="resolution" ROWS="3" COLS="40" wrap="soft"></textarea></td>
  </tr>
  <tr>
    <td align="right"><b>Date Closed:</b></td>
    <!--- you don't need the extra cfoutput tags here --->
    <td align="left"><input type="text" name="date_closed" value="#DateFormat(now(), "mm/dd/yyyy")#"></td>
  </tr>
</table>
</form>
</cfoutput>

Code: 
<cfif isDefined("url.update")>
<cfquery name="update" datasource="helpdesk" dbtype="ODBC">
    update tickets
    set resolution = '#resolution#', date_closed = '#date_closed#', closed = '#closed#'
    where ticket_ID = <cfqueryparam
                   value="#url.ticket_id#"
                    cfsqltype="numeric">

</cfquery>

The ticket has been updated!
</cfif>


When I run this, it gets to the update_process.cfm page, but nothing is displayed on the page
Click to expand...

That's probably a scope problem. The if statement is checking for a variable in the URL scope. It never runs because that variable doesn't exist.

<cfif isDefined("url.update")> ...

Since your form is using <form method="post"...> the variables are in the FORM scope, not the URL scope. So you should be using FORM in all your variable references

<cfif isDefined("FORM.theNameOfSomeFormFieldHere")> ...

But again, your form doesn't have a field named "closed" so you may get an error when you run the update query.

HTH
 
Upvote 0 Downvote
I reviews both pages and made the changes you suggested. The only other thing that I needed to change to get it working was on the update_process.cfm page.

I changed

where ticket_ID = <cfqueryparam value="#url.ticket_id#"
cfsqltype="numeric">

to

where ticket_ID = <cfqueryparam value="#form.ticket_id#"
cfsqltype="numeric">

It's now working 100%.

Thanks for all the help.
Scott
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Luvsql
  • Locked
  • Question
Hidden Date Field Issue Not Updating Properly
Replies
3
Views
86
darryncooke
darryncooke
sue0912
  • Locked
  • Question
Update Multiple Records
Replies
1
Views
74
sue0912
sue0912
szeiss
  • Locked
  • Question
Rendering Recordset in HTML Using Cfdocument
Replies
2
Views
80
cfSearching
cfSearching
wjgrayson
  • Locked
  • Question
New Template Question : Template updating from a template?
Replies
1
Views
49
vacunita
vacunita
jmcg
  • Locked
  • Question
Outputting Query records from within loop
Replies
5
Views
101
LyndonOHRC
LyndonOHRC

Part and Inventory Search

Sponsor

Back
Top