In: Root » Computers and technology » Programming » Modifying an Employee in the Database
The DateFormat() function around the DateOfBirth column in the codereturns the employee’s birth date reformatted according to a display mask. DateOfBirth normally comes back from the database in the following format: 2002-01-01 00:00:00.0 That format is not very user-friendly. Calling DateFormat() with a mask of “mm/dd/yyyy” returns the date as follows: 01/01/2002 This version is, of course, more natural and easy to read. The same is true for the employee edit process. Before the user can edit an employee record, he must enter the SSN of the employee that he wants to edit. EmployeeGetEditForm.cfm is nearly identical in behavior to CompanyGetEditForm.cfm. Create a file named EmployeeGetEditForm.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>Edit an Employee</h1> <table> <cfform action=”EmployeeEditForm.cfm” method=”POST”> <tr> <td>SSN</td> <td> <cfinput type=”Text” name=”SSN” message=”Please enter the SSN” validate=”social_security_number” required=”Yes” size=”12” maxlength=”11”> </td> </tr> <tr> <td> </td> <td> <input type=”submit” value=”Get Employee”> </td> </tr> </cfform> </table> </body> </html> The employee edit form combines a number of techniques that you’ve learned so far, such as creating a query to retrieve a record from the database, pre-populating form fields with the data from that record, and so on. Create a file named EmployeeEditForm.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file. <cfquery name=”GetEmployee” datasource=”#Request.MainDSN#”> SELECT CompanyID, SSN, Firstname, Lastname, Salary, DateOfBirth FROM Employee WHERE SSN = ‘#Trim(SSN)#’ </cfquery> <cfquery name=”GetCompanies” datasource=”#Request.MainDSN#”> SELECT CompanyID, CompanyName FROM Company ORDER BY CompanyName </cfquery> <html> <head> <title>ColdFusion MX Bible</title> <link rel=”stylesheet” href=”styles.css”> </head> <body> <h1>Edit an Employee</h1> <table> <cfform action=”EmployeeEditAction.cfm” method=”POST”> <cfoutput> <input type=”hidden” name=”OldSSN” value=”#SSN#”> </cfoutput> <tr> <td>Company</td> <td> <cfselect name=”CompanyID” size=”1” selected=”#GetEmployee.CompanyID#” message=”Please select a Company.” query=”GetCompanies” value=”CompanyID” display=”CompanyName” required=”Yes”></cfselect> </td> </tr> <tr> <td>SSN</td> <td> <cfinput type=”Text” name=”SSN” value=”#GetEmployee.SSN#” message=”Please enter the employee’s Social Security Number.” validate=”social_security_number” required=”Yes” size=”12” maxlength=”11”> </td> </tr> <tr> <td>First Name</td> <td> <cfinput type=”Text” name=”Firstname” value=”#GetEmployee.Firstname#” message=”Please enter the employee’s first name.” required=”Yes” size=”22” maxlength=”20”> </td> </tr> <tr> <td>Last Name</td> <td> <cfinput type=”Text” name=”Lastname” value=”#GetEmployee.Lastname#” message=”Please enter the employee’s last name.” required=”Yes” size=”22” maxlength=”20”> </td> </tr> <tr> <td>Salary</td> <td> <cfinput type=”Text” name=”Salary” value=”#GetEmployee.Salary#” message=”Please enter a valid salary.” validate=”float” required=”Yes” size=”22” maxlength=”20”> </td> </tr> <tr> <td>DOB</td> <td> <cfinput type=”Text” name=”DateOfBirth” value=”#DateFormat(GetEmployee.DateOfBirth, ‘mm/dd/yyyy’)#” message=”Please enter a valid date of birth in the format mm/dd/yyyy” validate=”date” required=”Yes” size=”11” maxlength=”10”> </td> </tr> <tr> <td> </td> <td> <input type=”submit” value=”Update Database”> </td> </tr> </cfform> </table> </body> </html> Two calls to CFQUERY are on this page. GetEmployee populates the employee edit form with the employee record, and GetCompanies populates the CFSELECT menu with the list of companies. As you move down the template, you should see the CFFORM tag and then a hidden form field named OldSSN. Remember that, on the company edit form, the CompanyID wasn’t editable, so you could just pass the ID through to the action page. On the employee edit form, however, you must pass both the current SSN value (in the OldSSN hidden form field) and the new SSN value (in the SSN CFINPUT) to the action page. We need both the old and new values of SSN because changing its value requires you to use the old value (what’s currently in the database) in the action template’s WHERE clause and the new value (what the user has changed) in its SET clause.As in CompanyEditForm.cfm, each CFINPUT tag in this template has a Value attribute that populates the form field with the value retrieved from the query. CFSELECT’s Selected attribute tells ColdFusion which item to select in the select menu as the form is initially displayed to the user. After the employee edit form, the employee edit action template is a welcome reprieve. Create a file named EmployeeEditAction.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file. <cfquery name=”UpdateEmployee” datasource=”#Request.MainDSN#”> UPDATE Employee SET SSN = ‘#Trim(Form.SSN)#’, CompanyID = #Val(Form.CompanyID)#, Firstname = ‘#Trim(Form.Firstname)#’, Lastname = ‘#Trim(Form.Lastname)#’, Salary = #Val(Form.Salary)#, DateOfBirth = #CreateODBCDate(Form.DateOfBirth)# WHERE SSN = ‘#Trim(Form.OldSSN)#’ </cfquery> <cflocation url=”Finished.cfm?msg=#URLEncodedFormat(‘#Form.FirstName# #Form.Lastname# has been updated in the database.’)#”> Point your Web browser to http://<yourserver>/CFMXBible/Ch02/EmployeeGet EditForm.cfm. Enter the SSN of an employee currently in the database and click Submit. To get a valid SSN pick one from the employee list. Change a few values on the edit form and click Submit. Then go back to the employee list and make sure that the employee record was changed. (You may need to click Refresh.) If everything doesn’t work perfectly, check the files that you created against their respective listings. |
legal disclaimer
Our website is not responsible for the information contained by this article. Web-articles is a free articles resource.
Suggestion: If you need fresh, daily updated content for your website, feel free to use our service. Click here for more information.
related articles
The CFQUERY in CompanyDeleteAction.cfm uses a DELETE statement with two SQL clauses: DELETE and WHERE. DELETE tells the database the table from which to delete a record, and WHERE tells the database which record to delete. You can watch the company delete process in action. Point your Web browser to http://<yourserver>/CFMXBible/Ch02/CompanyGetDeleteForm.cfm and enter a CompanyID. (To get a valid ID, go to the company list and pick a number from the ID column.) Click Submit to see the chosen company’s informatio...
The user must have the capability to remove employees from the database. The employee delete process is a simple combination of techniques that you have already learned, such as retrieving a record from the database, displaying that record in a template, and so on. The first page in this process is nearly identical to the employee get edit form. Create a file named EmployeeGetDeleteForm.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file. <html> <head> <ti...
3. Making direct links to the forms
Say that you want to modify or delete a company. Right now, you need to remember the company’s ID, go back to the launch pad, click Company Edit, and enter the company ID, all just to get to the edit form. Wouldn’t you rather click a company in the list and go directly to the edit form? ... <table> <tr> <td><b>ID</b></td> <td><b>Name</b></td> <td><b>Address</b></td> <td><b>City</b></t...
4. Using a Custom Tag
Custom tags are reusable, developer-authored extensions to the ColdFusion language. The custom tag that you create in the following parts displays today’s date in a familiar format. Create a file named TodaysDate.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file. <cfoutput>#DateFormat(Now(), “ddd, mmm d, yyyy”)#</cfoutput> Now() returns the current date and time, and DateFormat() reformats the date. To call your custom tag, open index.cfm, a...
5. Creating your physical data model
Physical data models are extracted from their logical counterparts by mapping logical objects to physical objects supported by the target database platform. The following table shows the relationship between logical and physical objects. In many cases, your physical data model almost mirrors your logical data model because entities typically map directly to the tables that store them. Exceptions to this rule are logical data models that contain nonspecific relationships and entity subtypes. Although entity subtypes are an advanced top...
6. Understanding All Relational Result Sets
This article can help you better understand complicated SQL containing multitable joins, group-related clauses, and aggregate functions—by far the most problematic topics for most database developers. You also learn the correct way to handle database exceptions and incorporate them as actual functionality in your ColdFusion application. You learn, too, how to increase performance by caching queries in memory for fast access. You can memorize SQL clauses and Bachus-Naur forms until you’re blue in the face, ...
7. How and when to use Group by Having and Distinct
After learning how SQL works internally, you can better learn how to put the more complicated aspects of it to use. First we need to clarify the difference between GROUP BY and DISTINCT. Quite a bit of confusion exists over these two clauses, because they often produce the same query results but for very different reasons. In fact, GROUP BY and DISTINCT are not related in any way whatsoever. The GROUP BY clause produces as its intermediate work product a table of rows representing each group, and a sort of “invisible third dimen...