In: Categories » Computers and technology » Programming » 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 information. Click Submit again and then go back to the company list. If everything worked, you should no longer see the deleted company in the list. You’ve just created an entire series of company maintenance processes—and now you get to do the same for employees! But the employee processes have a twist: Each employee is related to a company. Start with the add form. Create a file named EmployeeAddForm.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.
<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>Add an Employee</h1> <table> <cfform action=”EmployeeAddAction.cfm” method=”POST”> <tr> <td>Company</td> <td> <cfselect name=”CompanyID” size=”1” query=”GetCompanies” value=”CompanyID” display=”CompanyName” required=”Yes” message=”Please select a Company.”></cfselect> </td> </tr> <tr> <td>SSN</td> <td> <cfinput type=”Text” name=”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” 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” 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” 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”
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=”Add to Database”> </td> </tr> </cfform> </table> </body> </html>
This template is similar to the company add form. You’re using Employee table columns instead of Company table columns, but this template still has a CFFORM surrounding CFINPUT tags. By the way, look at the Validate attributes on the SSN, Salary, and DateOfBirth CFINPUT tags; social_security_number, float, and date are more ways to validate user input. The CFQUERY call should be somewhat familiar because it’s a stripped-down version of the CFQUERY from CompanyList.cfm. This query is used to populate the CFSELECT menu used to choose the company for which this employee works. Most of this template should be familiar, but this time you’re also going to handle dates. Create a file named EmployeeAddAction.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.
<cfquery name=”InsertEmployee” datasource=”#Request.MainDSN#”> INSERT INTO Employee( SSN, CompanyID, Firstname, Lastname, Salary, DateOfBirth
) VALUES ( ‘#Trim(Form.SSN)#’, #Val(Form.CompanyID)#, ‘#Trim(Form.Firstname)#’, ‘#Trim(Form.Lastname)#’, #Val(Form.Salary)#, #CreateODBCDate(Form.DateOfBirth)# ) </cfquery> <cflocation url=”Finished.cfm?msg=#URLEncodedFormat(‘#Form.FirstName# #Form.Lastname# has been added to the database.’)#”>
The most noticeable differences between CompanyAddAction.cfm and EmployeeAddAction. cfm are the different table and column names in use. Of course, you remember to use Trim() on all strings and use Val() on all numbers, but dates are handled differently, as follows: #CreateODBCDate(Form.DateOfBirth)# CreateODBCDate() takes a date-formatted string and converts it into a formal date value that the database can understand. Notice that you don’t use single quotes because the converted date is not a string value. Finally, CFLOCATION redirects the user to the Finished page, as in all your other action pages. Now you can add an employee to the database. Point your Web browser to http://<yourserver>/ CFMXBible/Ch02/EmployeeAddForm.cfm. Fill out the form and click Submit. You should be redirected to the Finished page. If not, compare the files that you’ve created with their respective listings. The only new tag in EmployeeAddForm.cfm is CFSELECT, which is another ColdFusion form control with extended features—in this case, the capability to populate the select menu by pointing CFSELECT to a query. Every option in a select menu has two parts: the text that appears to the user and the value submitted to the server. In this case, you want the user to choose from a list of company names, but you want the company ID to be submitted to the server. The list after the following code discusses each of the CFSELECT tag’s attributes in turn:
<cfselect name=”CompanyID” query=”GetCompanies” display=”CompanyName” value=”CompanyID” required=”Yes” message=”Please select a Company.”> </cfselect>
Name becomes the name of the Form variable on the action page—in this case, Form.CompanyID.
CFSELECT uses the specified Query to generate its options.
Each option’s text comes from the Display column.
Each option’s value comes from the Value column
If Required=”Yes”, the user must choose a value before the form can be submitted.
If the user doesn’t choose an option and attempts to submit the form, a JavaScript alert box containing this Message appears and the form isn’t submitted.
You also need a list of all the employees in the database. The employee list is more complicated than the company list because it shows not only employee information, but also the name of the company each employee works for. Create a file named EmployeeList.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.
<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 ORDER BY c.CompanyName, e.Lastname, e.Firstname </cfquery> <html> <head> <title>ColdFusion MX Bible</title> <link rel=”stylesheet” href=”styles.css”> </head>
<body> <h1>Employee List</h1> <table> <tr> <td><b>Company</b></td> <td><b>SSN</b></td> <td><b>Name</b></td> <td><b>Salary</b></td> <td><b>DOB</b></td> </tr> <cfoutput query=”GetEmployees”> <tr> <td>#CompanyName#</td> <td>#SSN#</td> <td>#Lastname#, #Firstname#</td> <td>#Salary#</td> <td>#DateFormat(DateOfBirth, “mm/dd/yyyy”)#</td> </tr> </cfoutput> </table> </body> </html>
This file is much like CompanyList.cfm, with the difference of the relational query in the CFQUERY call and the different column names inside CFOUTPUT. Point your web browser to http://<yourserver>/ CFMXBible/Ch02/EmployeeList.cfm. You should see all the employees in the database. If not, compare the files that you’ve created to their respective listings.
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
In this article, you create the template that puts your form data into the database. Create a file named CompanyAddAction.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file. <cfquery name=”InsertCompany” datasource=”#Request.MainDSN#”> INSERT INTO Company( CompanyName, Address, City, State, ZipCode, Comments ) VALUES ( ‘#Trim(Form.CompanyName)#’, ‘#Trim(Form.Address)#’, ...
2. Modifying a Company in the Database
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 ...
3. 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)...
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. 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> <ti...
6. 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...
7. 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...
8. 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...
9. 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, ...