| In this example, we are going to create a form to insert a new employee into the database. This time, however, we are going to throw in a little twist to our form page/action page combination. We want to make sure that only valid data gets entered into the database. Again, we are trying to anticipate any way in which users might make mistakes that would result in our application not acting the way we intend it to act. Imagine that a user enters new employee information into a form and hits the submit button to send that information to an action page. The action page, of course, will insert that new employee information into the database and typically will display some sort of confirmation message to the user to let him know his action was successful. If the confirmation message isn't displayed quickly enough, the user might become impatient and hit the refresh button on their browser. This would result in the INSERT SQL statement running a second time and creating a second instance of the new employee in the database. If the user were to hit the refresh button five times, for example, it could result in that new employee being entered into the database five times andhey, presto!we have corrupted data in our database. To remedy this problem, we are going to throw an additional page into the insert cycle. We will start with a form page as usual. This form page will then pass information to an action page that only contains code to insert the new record into the database. Once the insert has been completed, we will use the <CFLOCATION> tag to redirect the browser to a confirmation page. This way, there is less of a chance that the user will insert the employee into the database more than once. If the user hits his refresh button on the confirmation page, only the code to display the message will be refreshed, not the code that inserts information into the database. Let's start with the form. It will be very similar to the form we used to update our employee information, but it will not be prepopulated, and there will be no hidden EmployeeID field. -
Open your text editor and type the code in Listing 6.3 or open the completed EmployeeNew.cfm file from the CompletedFiles\Examples\Step06 folder. Listing 6.3 EmployeeNew.cfm Code [View full width] <!--- File: EmployeeNew.cfm Description: Form to insert new employee details Author: Created: ---> <!--- get department information to build CFSELECT menu ---> <CFQUERY NAME="qDepartments" DATASOURCE="Staff"> SELECT DepartmentID, DepartmentName FROM Departments ORDER BY DepartmentName </CFQUERY> <HTML> <HEAD> <TITLE>Add A New Employee</TITLE> </HEAD> <BODY> <H2>Add A New Employee</H2> <!--- set the action page for this form ---> <CFFORM ACTION="EmployeeInsert.cfm" METHOD="POST" > <TABLE BORDER="1" CELLSPACING="0" CELLPADDING="5" BORDERCOLOR="#000000"  BGCOLOR="#FFFFCC"> <TR> <TD>Employee ID</TD> <TD>will be assigned by database</TD> </TR> <TR> <TD>First Name:</TD> <TD><CFINPUT TYPE="Text" NAME="FirstName" MESSAGE="Please enter first name" REQUIRED="Yes" MAXLENGTH="50"></TD> </TR> <TR> <TD>Last Name:</TD> <TD><CFINPUT TYPE="Text" NAME="LastName" MESSAGE="Please enter last name" REQUIRED="Yes" MAXLENGTH="50"></TD> </TR> <TR> <TD>Department:</TD> <TD><CFSELECT NAME="DepartmentID" QUERY="qDepartments" VALUE="DepartmentID" DISPLAY="DepartmentName" REQUIRED="Yes"> </CFSELECT> </TD> </TR> <TR> <TD>Title:</TD> <TD><CFINPUT TYPE="Text" NAME="Title" REQUIRED="Yes" SIZE="30" MAXLENGTH="50"></TD> </TR> <TR> <TD>Email:</TD> <TD><CFINPUT TYPE="Text" NAME="Email" MESSAGE="Please enter email address" REQUIRED="Yes" SIZE="30" MAXLENGTH="50"></TD> </TR> <TR> <TD>Extension:</TD> <TD><CFINPUT TYPE="Text" NAME="Extension" MESSAGE="Extension must be a number" VALIDATE="integer" REQUIRED="Yes" SIZE="5" MAXLENGTH="5"></TD> </TR> <TR> <TD>Date Hired:</TD> <TD><CFINPUT TYPE="Text" NAME="DateHired" MESSAGE="Please enter date in mm/dd/ yyyy format" VALIDATE="date" REQUIRED="Yes"><FONT COLOR="##CC0000" SIZE="-1">MM/DD/YYYY</ FONT></TD> </TR> <TR> <TD><FONT SIZE="-1" COLOR="#FF0000">all fields are required</FONT></TD> <TD><INPUT TYPE="submit" VALUE="Add New Employee"></TD> </TR> </TABLE> </CFFORM> </BODY> </HTML> NOTE Single # symbols must be escaped when they occur inside a ColdFusion tag in the same manner they must be escaped within a <CFOUTPUT> block. -
Save this file as EmployeeNew.cfm in your Examples\Step06 folder. Your form should look similar to the one in Figure 6.4. Figure 6.4. The Add a New Employee form.  -
Next let's create our action page. Open your text editor and type the code in Listing 6.4 or open the completed EmployeeInsert.cfm file from the CompletedFiles\Examples\Step06 folder. Listing 6.4 EmployeeInsert.cfm Code <!--- File: EmployeeInsert.cfm Description: Inserts a new employee record in database Author: Created: ---> <!--- use SQL to insert a new employee record ---> <CFQUERY DATASOURCE="Staff"> INSERT INTO Employees (FirstName, LastName, DepartmentID, Title, Email, Extension, DateHired) VALUES ('#Trim(FORM.FirstName)#', '#Trim(FORM.LastName)#', #Val(FORM.DepartmentID)#, '#Trim(FORM.Title)#', '#Trim(FORM.Email)#', #Val(FORM.Extension)#, #CreateODBCDate(FORM.DateHired)#) </CFQUERY> <!--- create a 'Name' variable out of the First and Last name form fields ---> <CFSET Name = "#FORM.FirstName# #FORM.LastName#"> <!--- redirect users to a confirmation page ---> <CFLOCATION URL="InsertConfirmation.cfm?name=#URLEncodedFormat(Name)#"> -
Save this file as EmployeeInsert.cfm in your Examples\Step06 folder. Note that this file does not contain any HTML and will not actually display anything to users. It simply inserts the new record into the database and redirects the user to a confirmation page. -
Finally, let's finish up with the confirmation page. In your text editor, type the code in Listing 6.5 or open the completed InsertConfirmation.cfm file from the CompletedFiles\Examples\Step06 folder. Listing 6.5 InsertConfirmation.cfm Code <!--- File: InsertConfirmation.cfm Description: Display confirmation message after inserting employee Author: Created: ---> <HTML> <HEAD> <TITLE>Employee Addition Complete</TITLE> </HEAD> <BODY> <CFOUTPUT> <!--- provide feedback for successful update ---> <P><B>Successfully Updated The Employee Record For:<BR> #URL.Name#</B></P> <A HREF="EmployeeSearch.cfm">Return to employee search</A> </CFOUTPUT> </BODY> </HTML> -
Save this file as InsertConfirmation.cfm in your Examples\Step06 folder.
This is the page that will be displayed in the user's browser after the insert action has been completed. The user can hit the refresh button all day long on this page and not affect the database. Let's walk through the addition of a new employee. Follow the Insert New Employee Record link from the search page or browse to the EmployeeNew.cfm file. Enter information for a new employee into the form, as shown in Figure 6.5. Figure 6.5. Enter new employee information.  Click the Add New Employee button to submit the form. EmployeeInsert.cfm will run even though you will see no display. Your browser will then be redirected to the InsertConfirmation.cfm page, which is displayed in Figure 6.6. Figure 6.6. The confirmation page.  |
