The code in CompanyEditAction.cfm is like the code in CompanyAddAction.cfm, but the edit action updates rather than inserts. Create a file named CompanyEditAction.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.

<cfquery name=”UpdateCompany”
   datasource=”#Request.MainDSN#”>
   UPDATE Company
   SET
   CompanyName = ‘#Trim(Form.CompanyName)#’,
   Address = ‘#Trim(Form.Address)#’,
   City = ‘#Trim(Form.City)#’,
   State = ‘#Trim(Form.State)#’,
   ZipCode = ‘#Trim(Form.ZipCode)#’,
   Comments =
   <cfif Len(Trim(Form.Comments)) GT 0>
   ‘#Trim(Form.Comments)#’
   <cfelse>
   NULL
   </cfif>
   WHERE
   CompanyID = #Val(Form.CompanyID)#
   </cfquery>
   <cflocation url=”Finished.cfm?msg=#URLEncodedFormat(‘#Form.CompanyName#
 has been updated in the database.’)#”>

Now that you’ve finished these three templates, you can see them in action. Point your Web browser to http://<yourserver>/CFMXBible/Ch02/CompanyGetEditForm.cfm and enter a CompanyID. (To get a valid ID, go to the company list and pick a number in the ID column.) Click Submit, and you should see the chosen company’s information appear in the form fields on the edit form. Edit the Zip Code and click Submit; then revisit the company list and click your browser’s Refresh button. You should see the updated data in the company list. If you don’t, compare the files that you’ve created with their respective listings. Open CompanyEditForm.cfm in HomeSite. Because the edit form is very similar to the add form, compare the two. The first difference between the edit form and add form is the CFQUERY at the beginning of CompanyEditForm.cfm. CompanyEditForm.cfm calls CFQUERY to retrieve the company record that the user wants to edit. This query is like the one in CompanyList.cfm, with a few differences. First, CompanyEditForm.cfm doesn’t include CompanyID in the select list, because the chosen company’s ID is already available in the CompanyID that was sent to this template in the form post. Second, you now find a WHERE clause that restricts the result set to include only the company with an ID that matches CompanyID. Third, you find no ORDER BY clause, because this query returns only a single record. Look at the WHERE clause again in the following code:

WHERE
   CompanyID = #Val(CompanyID)#

CompanyID came from the CompanyGetEditForm.cfm form post, so it is said to be a part of the Form scope, and as such would normally be referenced as Form.CompanyID. Likewise, if CompanyID had been passed in a hyperlink as a URL parameter, it would be a part of the URL scope, and as such would normally be referenced as URL.CompanyID. Notice that in the preceding line of code, we omitted the scope prefix to the CompanyID variable. This causes ColdFusion to go “hunting” for the scope that contains the CompanyID variable. By using this technique, you can build a flexible template that may receive values from either the URL or a form post. Val() is a ColdFusion function that takes a string and returns the numeric part of that string (for example, 123ABC would become 123). If the string does not begin with a number, Val() returns zero. Val() is very important, especially with URL parameters, for the following two reasons:

If the database is expecting a number but the user passes a nonnumeric parameter, Val() ensures that the database doesn’t throw a syntax error.

Val() is a security precaution. If you don’t use Val(), a hacker could append a malicious query such as DELETE FROM Customer onto the end of the URL parameter, and the extra query would execute against your database. Val() removes all nonnumeric characters, so the malicious query could never touch your database. Notice also that you don’t surround the pound signs with single quotes as you do in CompanyAddAction.cfm, because CompanyID is a numeric column. Only text strings can be surrounded with single quotes. Moving further down CompanyEditForm.cfm, you see a plain HTML INPUT tag of Type = “hidden”. Hidden form fields are invisible to the user; they pass data to the action page without user intervention. The hidden INPUT tag has two attributes aside from its Type: Name and Value. Name becomes the name of the Form variable on the action page. Value is what that variable contains. You use a hidden form field here so that CompanyEditAction.cfm knows which company to update in the database. Notice that the value of the hidden form field is CompanyID, which was the CompanyID posted from CompanyGetEditForm.cfm. The hidden INPUT field is the mechanism that passes through CompanyID to CompanyEditAction.cfm. The final difference between CompanyEditForm.cfm and CompanyAddForm.cfm is that the edit form’s fields are prepopulated with data. Each of the five CFINPUT tags on CompanyEditForm.cfm has a Value attribute. The Value attribute is the text that the user sees in the input field as he first browses CompanyEditForm.cfm. All the Value attributes look as follows:

value=”#GetCompany.CompanyName#”

Remember that, in CompanyList.cfm, if you output the value of a column in a result set, you just use the column name without the name of the result set preceding it. In CompanyEditForm.cfm, however, your data is not inside a <cfoutput query=”GetCompany”> tag, so you must tell ColdFusion where this variable is by preceding the column name with the name of the result set containing it. The final difference is in the TEXTAREA tag. TEXTAREA doesn’t have a Value attribute; the value of the text area is whatever appears between the <textarea> and </textarea> tag pair. Now look at CompanyEditAction.cfm. As in CompanyAddAction.cfm, only two ColdFusion tags are in this template: CFQUERY, which updates the company record in the database; and CFLOCATION, which redirects the user away from the edit action template. The UPDATE statement on this page uses three SQL clauses: UPDATE, SET, and WHERE. UPDATE tells the database which table to update; SET tells the database the new values to assign to each column in the updated record; and WHERE tells the database which record to update. Look at the WHERE, clause and you’ll see why you used a hidden form field on the company edit form. Remember that the hidden form field contained the CompanyID of the company that you were editing. The WHERE clause tells the database to update the company with the ID that matches CompanyID. After CFQUERY, CompanyEditAction.cfm calls CFLOCATION, which redirects the user to the Finished page. You don’t need a separate Finished page for the Edit Action; simply modify URL.msg to describe what happened: #Form.CompanyName# has been updated in the database. Compared to adding and editing companies, deleting a company is a breeze. As does editing a company, deleting a company requires four templates: one template to choose which company to delete, one template to display the company record so that the user can confirm that he is deleting the correct company, an action page to delete the company from the database, and the Finished page that you have already created. Creating the first template is a piece of cake because you’ve already written it! The Get Delete Form listing is almost exactly the same as the Get Edit Form listing. Create a file named CompanyGetDeleteForm.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.

<html>
   <head>
   <title>ColdFusion MX Bible</title>
   <link rel=”stylesheet” href=”styles.css”>
   </head>
   <body>
   <h1>Delete a Company</h1>
   <table>
   <cfform action=”CompanyDeleteForm.cfm” method=”POST”>
   <tr>
   <td>Company ID</td>
   <td>
   <cfinput type=”Text”
   name=”CompanyID”
   message=”Please enter the Company ID”
   validate=”integer”
   required=”Yes”
   size=”22”
   maxlength=”20”>
   </td>
   </tr>
   <tr>
   <td>&nbsp;</td>
   <td>
   <input type=”submit” value=”Get Company”>
   </td>
   </tr>
 </cfform>

</table>
   </body>
 </html>

The only difference between the Get Delete and Get Edit forms is where the user submits them! The delete form is a template that enables the user to inspect the company record before deciding to delete the company. No editing takes place on this page—the user is just viewing static data. Create a file named CompanyDeleteForm.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.

<cfquery name=”GetCompany”
   datasource=”#Request.MainDSN#”>
   SELECT
   CompanyName,
   Address,
   City,
   State,
   ZipCode,
   Comments
   FROM
   Company
   WHERE
   CompanyID = #Val(CompanyID)#
   </cfquery>
   <html>
   <head>
   <title>ColdFusion MX Bible</title>
   <link rel=”stylesheet” href=”styles.css”>
   </head>
   <body>
   <h1>Delete a Company</h1>
   <cfoutput>
   <table>
 <cfform action=”CompanyDeleteAction.cfm” method=”POST”>

<input type=”hidden” name=”CompanyID” value=”#Val(CompanyID)#”>
   <tr>
   <td>Company Name</td>
   <td>#GetCompany.CompanyName#</td>
   </tr>
   <tr>
   <td>Address</td>
   <td>#GetCompany.Address#</td>
   </tr>
   <tr>
   <td>City</td>
   <td>#GetCompany.City#</td>
   </tr>
   <tr>
   <td>State</td>
   <td>#GetCompany.State#</td>
   </tr>
   <tr>
   <td>ZIP Code</td>
   <td>#GetCompany.ZipCode#</td>
   </tr>
   <tr>
   <td>Comments</td>
   <td>#GetCompany.Comments#</td>
   </tr>
   <tr>
   <td>&nbsp;</td>
   <td>
   <input type=”submit” value=”Delete From Database”>
   </td>
   </tr>
   </cfform>
   </table>
   </cfoutput>
   </body>
 </html>

The first line of code in the delete form is the same CFQUERY used in the edit form; it retrieves the company record from the database. You use CFFORM and a hidden form field because you are submitting the CompanyID to the action page. You don’t need any CFINPUT tags because you don’t need any user input on this form. All that’s left is the Submit button. The last template in the company delete process controls the delete action. Create a file named CompanyDeleteAction.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.

<cfquery name=”DeleteCompany”
   datasource=”#Request.MainDSN#”>
   DELETE FROM Company
   WHERE
   CompanyID = #Val(Form.CompanyID)#
   </cfquery>
   <cflocation url=”Finished.cfm?msg=#URLEncodedFormat(‘Company ID
 #Form.CompanyID# has been deleted from the database.’)#”>

Only two ColdFusion tags are on this page: CFQUERY, which tells the database to delete a row; and CFLOCATION, which redirects the user to the Finished page.