In: Categories » Computers and technology » Programming » Removing an Employee From the Database
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> <title>ColdFusion MX Bible</title> <link rel=”stylesheet” href=”styles.css”> </head> <body> <h1>Delete an Employee</h1> <table> <cfform action=”EmployeeDeleteForm.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 only difference between the get delete form and the get edit form is where each one posts: the get edit form posts to EmployeeEditForm.cfm, whereas the get delete form posts to employeeDeleteForm.cfm. No new concepts are presented in this template—just new combinations of what you’ve already learned so far in this article. Create a file named EmployeeDeleteForm.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 c.CompanyName, e.SSN, e.Firstname, e.Lastname, e.Salary, e.DateOfBirth FROM Employee e INNER JOIN Company c ON e.CompanyID = c.CompanyID WHERE e.SSN = ‘#Trim(SSN)#’ </cfquery> <html> <head> <title>ColdFusion MX Bible</title> <link rel=”stylesheet” href=”styles.css”> </head> <body> <h1>Delete an Employee</h1> <cfoutput> <table> <cfform action=”EmployeeDeleteAction.cfm” method=”POST”> <input type=”hidden” name=”SSN” value=”#SSN#”> <tr> <td>Company</td> <td>#GetEmployee.CompanyName#</td> </tr> <tr> <td>SSN</td> <td>#GetEmployee.SSN#</td> </tr> <tr> <td>First Name</td> <td>#GetEmployee.Firstname#</td> </tr> <tr> <td>Last Name</td>
<td>#GetEmployee.Lastname#</td> </tr> <tr> <td>Salary</td> <td>#GetEmployee.Salary#</td> </tr> <tr> <td>DOB</td> <td>#DateFormat(GetEmployee.DateOfBirth, ‘mm/dd/yyyy’)#</td> </tr> <tr> <td> </td> <td> <input type=”submit” value=”Delete from Database”> </td> </tr> </cfform> </table> </cfoutput> </body> </html>
The GetEmployee query looks like the query on EmployeeList.cfm with a WHERE clause to restrict the result set to a single record. The inner join enables the company name to appear on the delete form. You don’t need the INNER JOIN on the edit form because the company name came from a separate query (GetCompanies). Create a file named EmployeeDeleteAction.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.
<cfquery name=”DeleteEmployee” datasource=”#Request.MainDSN#”> DELETE FROM Employee WHERE SSN = ‘#Trim(Form.SSN)#’ </cfquery>
<cflocation url=”Finished.cfm?msg=#URLEncodedFormat(‘Employee #Form.SSN# has been deleted from the database.’)#”>
The CFQUERY deletes the employee from the database, and the CFLOCATION redirects the user. Now that you’ve finished the employee delete process, take it for a spin. Point your Web browser to http://<yourserver>/CFMXBible/Ch02/EmployeeGetDeleteForm.cfm. Enter the SSN of an employee currently in the database, and click Submit. Look at the employee information, and Click Submit. Then go back to the employee list and make sure that the employee record was removed. The way things that stand right now, to operate this application, you must manually enter the names of eight different templates. Instead, you can create a “launch pad” to make the application a little easier to use. Create a file named index.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>Companies</h1> <a href=”CompanyList.cfm”>List Companies</a><br> <a href=”CompanyAddForm.cfm”>Add a Company</a><br> <a href=”CompanyGetEditForm.cfm”>Edit a Company</a><br> <a href=”CompanyGetDeleteForm.cfm”>Delete a Company</a> <h2>Employees</h2> <a href=”EmployeeList.cfm”>List Employees</a><br> <a href=”EmployeeAddForm.cfm”>Add an Employee</a><br> <a href=”EmployeeGetEditForm.cfm”>Edit an Employee</a><br> <a href=”EmployeeGetDeleteForm.cfm”>Delete an Employee</a> </body> </html>
No ColdFusion tags are in this template—just hyperlinks pointing to each process’s starting template. Point your Web browser to http://<yourserver>/CFMXBible/Ch02/index.cfm and click away! You now have a fully functioning application, but you can do a number of things to make it work better. You can easily give the application a way to get back to the launch pad no matter where you are. You could, of course, open up every template and add a link at the bottom, but that’s timeconsuming —and what if you forget a template? A better option is to use a special template named OnRequestEnd.cfm that ColdFusion automatically executes after the end of every request (just as ColdFusion executes Application.cfm at the beginning of every request). OnRequestEnd.cfm must be capitalized exactly as shown for it to function on all platforms. Create a file named OnRequestEnd.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.
<p><a href=”index.cfm”>Home</a></p>
Try it out by going to any page in your application. You should see the Home link at the bottom of the page. A nice touch would be if the company list had a direct link to the employee list so that the user could view all employees of a selected company. Of course, you would still want to be able to view the employees of all the companies at once if the user goes directly to the employee list. To accomplish this new functionality, you must modify two templates. First, you modify CompanyList.cfm so that it contains a link to EmployeeList.cfm, passing the company ID in the URL. You pass the CompanyID so that EmployeeList.cfm can know the company from which to select employees.
... <table> <tr> <td><b>ID</b></td> <td><b>Name</b></td> <td><b>Address</b></td>
<td><b>City</b></td> <td><b>State</b></td> <td><b>ZIP Code</b></td> <td> </td> </tr> <cfoutput query=”GetCompanies”> <tr> <td>#CompanyID#</td> <td>#CompanyName#</td> <td>#Address#</td> <td>#City#</td> <td>#State#</td> <td>#ZipCode#</td> <td><a href=”EmployeeList.cfm?CompanyID=#CompanyID#”>Employees</a></td> </tr> </cfoutput> </table> ...
After the user clicks the Employees link, he goes to the employee list page, which receives the CompanyID in a URL parameter named URL.CompanyID. You don’t need Val() here because CompanyID comes directly from a numeric database column with no user intervention, so #CompanyID# is absolutely guaranteed to be a number. Now turn your attention to EmployeeList.cfm.
<cfquery name=”GetEmployees” datasource=”#Request.MainDSN#”> SELECT c.CompanyName, e.SSN, e.Firstname, e.Lastname, e.Salary, e.DateOfBirth FROM Employee e INNER JOIN Company c ON e.CompanyID = c.CompanyID <cfif IsDefined(“URL.CompanyID”)> WHERE e.CompanyID = #Val(URL.CompanyID)# </cfif> ORDER BY c.CompanyName, e.Lastname, e.Firstname </cfquery> ...
You’re adding an additional part to the CFQUERY, but notice that the new part is inside a CFIF test. Remember that you can dynamically construct an SQL statement by using this method. By the way, you do need Val() here because the user could have modified URL.CompanyID by playing with the URL. Go to the launch pad and click Company List. Click any of the links named Employees and you see only those employees who work for that company.
legal notice
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.
Useful tools and features
If you like this article (tutorial), please link to it from your web page using the information above.
related articles
The data that CFQUERY requests from the database comes back in a result set, and the Name attribute tells ColdFusion what that result set is to be named. The SQL statement consists of three clauses: SELECT, FROM, and ORDER BY. SELECT tells the database which columns to retrieve from the database; FROM tells the database which table to retrieve those columns from; and ORDER BY tells the database how to sort the results. The result set returned from CFQUERY contains multiple rows of data, and each row has multiple columns. It would ...
2. Building the company edit action template
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)...
3. Adding a New Employee to the Database
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...
4. 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...
5. 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...
6. 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...
7. 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...
8. 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, ...
9. 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...