My First Forum - a bulletin board application
Exporting data to Word with Cold Fusion
You are here: irt.org | Articles | Database | How to update/edit data using Cold Fusion [ previous next ]
Published on: Saturday 2nd January 1999 By: Janus Boye
If you already have a database up and running, that works with your Cold Fusion Application Server, you might want to be able to update/edit this data from your browser. This article will try to provide an example of this, with some ideas and thoughts for future improvement.
Updating pages is normally done with two pages: An update page, and an update form. On the update page, you display the data to be updated, and on the update form, the actual updating takes place.
This article will take a different approach, by combining these into one single page using CFIF code.
First of all you need to meet the standard Cold Fusion prerequisites. Cold Fusion needs to be installed, the ODBC datasource needs to be defined, and you need your database.
In this example, I just have a very simple Access database called irt.mdb, and I've defined it as my irt datasource. In the database, I've created an articles table, with the following rows and records:
Articleno - Title - Authors 69 Math functions in JavaScript Janus Boye 126 Re-directing access within Frames #2 Martin Webb 13 Re-directing access within Frames Martin Webb 5 Source Files Martin Webb 73 Introduction to CGI Jason Nugent
Basically it is just five articles here from irt.org, that I'm going to create a CFML page, where you'll be able to update the above fields.
To get going, I just started off, with creating a very simple .cfm file, index.cfm, that just outputs all the records in the Articles table.
The initial code is shown below:
<!-- First we select all data from the table -->
<CFQUERY NAME="GetData" DATASOURCE="irt">
SELECT *
FROM Articles
</CFQUERY>
<html>
<head>
<title>Update articles</title>
</head>
<body>
<p>Let's first display all data in a table
<table>
<tr>
<th>Article#</th>
<th>Author</th>
<th>Title</th>
</tr>
<!-- This is where all the data get's nicely outputted into the table -->
<CFOUTPUT QUERY="GetData">
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
</tr>
</CFOUTPUT>
</table>
</body>
</html>If you want more information or help on selecting and outputting data, you might want to take a look in the Selecting and outputting data section of the Introduction to Cold Fusion.
If you try to request the above page, the result should look like the file index1.htm.
The next step, is to introduce the form in the above page. Next to each and every record, I want a small edit-button, that, when pressed, will allow my users to edit a row.
To do this, I add another row to my table, by adding
<th></th> after the <th>Title</th>
and I include my form in the CFOUTPUT code, so that it now looks like the following:
<CFOUTPUT QUERY="GetData"> <tr> <td>#Articleno#</td> <td>#Author#</td> <td>#Title#</td> <!-- This is where the Edit button and form is created --> <td> <form action="index.cfm?From=Edit&ID=#ID#" method="post"> <input type="submit" value="Edit"> </form> </td> </tr> </CFOUTPUT>
The above code just creates a Edit button for each and every record. As the form action, it also adds the Record ID, plus a From attribute, that we'll use in the next section.
Basically the above update to index.cfm, just adds the edit button, so your page should now look like the file index2.htm.
In this section, things get a little more complicated.
What I now want to do, is to make my page look for the URL.From attribute -- which is created if the Edit button is pressed -- and if it is found, let me edit the chosen row.
I do this by using CFIF code in my table. The pseudo-code goes something like this:
If #URL.ID# equals the current row
then
display all data in a text field
and
display a new update button
if not
then
just output plain textTo achieve the above, I do a major rewrite of my table, so it now looks like below:
<table>
<tr>
<th>Article#</th>
<th>Author</th>
<th>Title</th>
<th></th>
</tr>
<!-- Here we check for the URL.From attribute -->
<CFIF IsDefined("URL.From")>
<CFLOOP QUERY="GetData">
<CFIF #URL.ID# IS #GetData.ID#>
<CFOUTPUT>
<form action="index.cfm?From=DoEdit&ID=#ID#" method="post">
<tr>
<td><input type="text" name="Articleno" size="4" value="#Articleno#"></td>
<td><input type="text" name="Author" size="20" value="#Author#"></td>
<td><input type="text" name="Title" size="40" value="#Title#"></td>
</tr>
</CFOUTPUT>
<CFELSE>
<!-- This is where all the data get's nicely output into the table -->
<CFOUTPUT>
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<!-- This is where the Edit button and form is created -->
<td>
</td>
</tr>
</CFOUTPUT>
</CFIF>
</CFLOOP>
<!-- If URL.From is undefined -->
<CFELSE>
<CFOUTPUT QUERY="GetData">
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<!-- This is where the Edit button and form is created -->
<td>
<form action="index.cfm?From=Edit&ID=#ID#" method="post">
<input type="submit" value="Edit">
</form>
</td>
</tr>
</CFOUTPUT>
</CFIF>
<!-- If URL.From is defined and equals Edit, then display an update button and close the form -->
<CFIF IsDefined("URL.From")>
<CFIF #URL.From# IS "Edit">
<tr>
<td colspan="3">
<input type="submit" value="Update">
</form>
</td>
</tr>
</CFIF>
</CFIF>
</table>The comments should be self-explanatory, and you should now have a page, that looks like the file index3.htm.
The only thing that we are now missing, is doing the actual update of the database.
I do this by adding some code into the top of index.cfm, that checks for the URL.From attribute, and if it equals DoEdit it then does the update, displays a short confirmation to the user, and outputs the data.
To do the update, I use the CFQUERY tag as shown below:
<!-- If URL.From is defined and equals DoEdit, then update the table -->
<CFIF IsDefined("URL.From")>
<CFIF #URL.From# IS "DoEdit">
<CFQUERY NAME="DoUpdate" DATASOURCE="irt">
UPDATE Articles
SET Articleno=#form.Articleno#,
Title='#form.Title#',
Author='#form.Author#'
WHERE ID=#URL.ID#
</CFQUERY>
</CFIF>
</CFIF>I insert this code, into the very top of my page before the already existing CFQUERY.
After my BODY tag I insert some additional, that checks to see if DoUpdate has been run, and if so, it then displays a short note to our dear user:
<body>
<!-- If DoUpdate has been run a short note is given -->
<CFIF IsDefined("DoUpdate.RecordCount")>
<p><b>Thanks for your update!</b>
</CFIF>Last, but not least, I need to add another pair of CFIF tags to my table, so that is only outputs the data and an Edit button if the URL.From parameter equals DoEdit.
My table should now look like below:
<table>
<tr>
<th>Article#</th>
<th>Author</th>
<th>Title</th>
<th></th>
</tr>
<!-- Here we check for the URL.From attribute -->
<CFIF IsDefined("URL.From")>
<!-- If URL.From just equals DoEdit, we simply just output the data, just as if it was undefined -->
<CFIF #URL.From# IS "DoEdit">
<CFOUTPUT QUERY="GetData">
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<!-- This is where the Edit button and form is created -->
<td>
<form action="index.cfm?From=Edit&ID=#ID#" method="post">
<input type="submit" value="Edit">
</form>
</td>
</tr>
</CFOUTPUT>
<CFELSE>
<CFLOOP QUERY="GetData">
<CFIF #URL.ID# IS #GetData.ID#>
<CFOUTPUT>
<form action="index.cfm?From=DoEdit&ID=#ID#" method="post">
<tr>
<td><input type="text" name="Articleno" size="4" value="#Articleno#"></td>
<td><input type="text" name="Author" size="20" value="#Author#"></td>
<td><input type="text" name="Title" size="40" value="#Title#"></td>
</tr>
</CFOUTPUT>
<CFELSE>
<!-- This is where all the data get's nicely outputted into the table -->
<CFOUTPUT>
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<td>
</td>
</tr>
</CFOUTPUT>
</CFIF>
</CFLOOP>
</CFIF>
<!-- If URL.From is undefined -->
<CFELSE>
<CFOUTPUT QUERY="GetData">
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<!-- This is where the Edit button and form is created -->
<td>
<form action="index.cfm?From=Edit&ID=#ID#" method="post">
<input type="submit" value="Edit">
</form>
</td>
</tr>
</CFOUTPUT>
</CFIF>
<!-- If URL.From is defined and equals Edit, then display an update button and close the form -->
<CFIF IsDefined("URL.From")>
<CFIF #URL.From# IS "Edit">
<tr>
<td colspan="3">
<input type="submit" value="Update">
</form>
</td>
</tr>
</CFIF>
</CFIF>
</table>After having run an update, you should then get a page, that should somewhat like the file index4.htm.
You should now have a complete all-in-one update.
To make things easier, you might want to order the GetData query by either the Author, Title or Articleno rows.
The below code shows how you can order it by the Title row:
<CFQUERY NAME="GetData" DATASOURCE="irt">
SELECT *
FROM Articles
ORDER BY Title
</CFQUERY>Note, that you only need to add the ORDER BY line.
Instead of using the CFQUERY tag, that forces you to do SQL code, you could simply use the much more simplistic CFUPDATE tag by replacing:
<CFQUERY NAME="DoUpdate" DATASOURCE="irt"> UPDATE Articles SET Articleno=#form.Articleno#, Title='#form.Title#', Author='#form.Author#' WHERE ID=#URL.ID# </CFQUERY>
with:
<CFUPDATE DATASOURCE="irt" TABLENAME="Articles">
and updating the last CFIF code in index.cfm to:
<!-- If URL.From is defined and equals Edit, then display an update button and close the form -->
<CFIF IsDefined("URL.From")>
<CFIF #URL.From# IS "Edit">
<tr>
<td colspan="3">
<CFOUTPUT>
<input type="hidden" name="ID" value="#URL.ID#">
</CFOUTPUT>
<input type="submit" value="Update">
</form>
</td>
</tr>
</CFIF>
</CFIF>CFUPDATE requires that the ID field also is passed along in the form, and then it just detects which value is the primary key, and simply just updates all other fields.
While CFUPDATE might seem easier, with only one single line of updating, is slower and also holds limited capabilities compared to using the SQL UPDATE done in CFQUERY. As an example of this CFUPDATE only lets you update all fields and not only selected fields.
Suggestions for future work, could among many things include, how to update multiple rows, or how to create a preview page before the data is updated.
Good luck!
<!-- If URL.From is defined and equals DoEdit, then update the table -->
<CFIF IsDefined("URL.From")>
<CFIF #URL.From# IS "DoEdit">
<CFUPDATE DATASOURCE="irt" TABLENAME="Articles">
</CFIF>
</CFIF>
<!-- The below code selects all data from the table -->
<CFQUERY NAME="GetData" DATASOURCE="irt">
SELECT *
FROM Articles
ORDER BY Title
</CFQUERY>
<html>
<head>
<title>Update articles</title>
</head>
<body>
<!-- If DoUpdate has been run a short note is given -->
<CFIF IsDefined("DoUpdate.RecordCount")>
<p><b>Thanks for your update!</b>
</CFIF>
<p>Let's first display all data in a table
<table>
<tr>
<th>Article#</th>
<th>Author</th>
<th>Title</th>
<th></th>
</tr>
<!-- Here we check for the URL.From attribute -->
<CFIF IsDefined("URL.From")>
<!-- If URL.From just equals DoEdit, we simply just output the data, just as if it was undefined -->
<CFIF #URL.From# IS "DoEdit">
<CFOUTPUT QUERY="GetData">
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<!-- This is where the Edit button and form is created -->
<td>
<form action="index.cfm?From=Edit&ID=#ID#" method="post">
<input type="submit" value="Edit">
</form>
</td>
</tr>
</CFOUTPUT>
<CFELSE>
<CFLOOP QUERY="GetData">
<CFIF #URL.ID# IS #GetData.ID#>
<CFOUTPUT>
<form action="index.cfm?From=DoEdit&ID=#ID#" method="post">
<tr>
<td><input type="text" name="Articleno" size="4" value="#Articleno#"></td>
<td><input type="text" name="Author" size="20" value="#Author#"></td>
<td><input type="text" name="Title" size="40" value="#Title#"></td>
</tr>
</CFOUTPUT>
<CFELSE>
<!-- This is where all the data get's nicely output into the table -->
<CFOUTPUT>
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<td>
</td>
</tr>
</CFOUTPUT>
</CFIF>
</CFLOOP>
</CFIF>
<!-- If URL.From is undefined -->
<CFELSE>
<CFOUTPUT QUERY="GetData">
<tr>
<td>#Articleno#</td>
<td>#Author#</td>
<td>#Title#</td>
<!-- This is where the Edit button and form is created -->
<td>
<form action="index.cfm?From=Edit&ID=#ID#" method="post">
<input type="submit" value="Edit">
</form>
</td>
</tr>
</CFOUTPUT>
</CFIF>
<!-- If URL.From is defined and equals Edit, then display an update button and close the form -->
<CFIF IsDefined("URL.From")>
<CFIF #URL.From# IS "Edit">
<tr>
<td colspan="3">
<CFOUTPUT>
<input type="hidden" name="ID" value="#URL.ID#">
</CFOUTPUT>
<input type="submit" value="Update">
</form>
</td>
</tr>
</CFIF>
</CFIF>
</table>
</body>
</html>Cold Fusion can be downloaded from: http://www.allaire.com
Allaire has a very good Cold Fusion discussion forum @ http://forums.allaire.com
My First Forum - a bulletin board application
Exporting data to Word with Cold Fusion
Diary of a WebObjects Developer