| Let's create the action page for our form. -
Open your text editor and enter the code in Listing 6.2 or open the completed EmployeeUpdate.cfm file from the CompletedFiles\Examples\Step06 folder. Listing 6.2 EmployeeUpdate.cfm Code <!--- File: EmployeeUpdate.cfm Description: Updates employee record in database Author: Created: ---> <!--- use SQL to update employee record ---> <CFQUERY NAME="qUpdateEmployee" DATASOURCE="Staff"> UPDATE Employees SET FirstName='#Trim(FORM.FirstName)#', LastName='#Trim(FORM.LastName)#', DepartmentID=#Val(FORM.DepartmentID)#, Title='#Trim(FORM.Title)#', Email='#Trim(FORM.Email)#', Extension=#Val(FORM.Extension)#, DateHired=#CreateODBCDate(FORM.DateHired)# WHERE EmployeeID=#FORM.EmployeeID# </CFQUERY> <HTML> <HEAD> <TITLE>Employee Details Updated</TITLE> </HEAD> <BODY> <CFOUTPUT> <!--- provide feedback for successful update ---> <P><B>Successfully Updated The Employee Record For:<BR> #FORM.FirstName# #FORM.LastName#</B></P> <A HREF="EmployeeSearch.cfm"> Return to employee search</A> </CFOUTPUT> </BODY> </HTML> -
Save the file as EmployeeUpdate.cfm in your Examples\Step06 folder.
After you have updated an employee's details, your screen should look similar to Figure 6.3. Figure 6.3. The EmployeeUpdate.cfm display.  Using <CFUPDATE> Now that we know how to use SQL, let's have a look at how we can update our database using just ColdFusion. As you can see, it is very important to get your SQL UPDATE statements just right, and there are plenty of opportunities to mess it up. Because using forms to update database content is such a common need, ColdFusion has encapsulated the process into one easy-to-use tag called <CFUPDATE>. The <CFUPDATE> tag has only two required attributes: DATASOURCE, which is the data source name (DSN) of the database you want to update, and TABLENAME, which is the name of the table you want to update. The <CFUPDATE> tag is quite simple to use. On the action page, you use the tag in place of <CFQUERY>. For example, at the top of our action page, we could simply use the following line of code: <CFUPDATE DATASOURCE="Staff" TABLENAME="Employees"> It doesn't get much simpler than that, but this simplicity comes with a few caveats. Namely, all form fields passed to this action page have to be named exactly the same as the field names in the database (which is a good idea anyway), and the primary key value of the row to be updated must be passed by the form. For more information on primary key values, see the sidebar "The Key to Primary Keys" earlier in this step. In the <CFUPDATE> tag just shown, notice that nowhere do we specify which fields in the database we would like updated. If we do not specify any fields, <CFUPDATE> will try to use every field passed by the form to update the database. If we do not want to use every field on the form in our update, we can use the FORMFIELDS attribute of the <CFUPDATE> tag. The FORMFIELDS attribute is a comma-delimited list of field names we want to use in the update. If we use the FORMFIELDS attribute, we are telling ColdFusion to only update the fields in the list rather than every field passed by the form. For example, if we wanted to update only an employee's first name and last name, we could use a <CFUPDATE> statement like this one: <CFUPDATE DATASOURCE="Staff" TABLENAME="Employees" FORMFIELDS="FirstName,LastName"> The <CFUPDATE> tag automatically creates the SQL statement behind the scenes and takes care of all those pesky formatting issues such as making sure the string fields have single quotes and numerical values do not. Which Method to Use Using either <CFUPDATE> or SQL to update your database will get the job done, so which should you choose? Using the <CFUPDATE> tag is quick and easy but lacks the flexibility of a custom SQL statement. The <CFUPDATE> tag can only use fields passed by a form to update the database, whereas a custom SQL statement can use not only form scope variables but variables from other scopes such as URL, Client, and Session as well. Here's my advice: If it is a simple update from a form or if you are not comfortable with SQL, <CFUPDATE> is probably a good choice. When you become a bit more comfortable with SQL, I think you will find that it's power and flexibility will make it your method of choice. We will mainly use SQL throughout the rest of this book, simply so that we gain some exposure to it and become comfortable using it. Learning a little SQL is well worth your time and will pay dividends when you begin working more with various database systems. For more information about learning SQL, visit the Bookshelf section of the www.LearnColdFusionMX.com web site. Inserting Records Inserting new records into a database is not all that different from doing an update, but there are a few additional considerations to keep in mind. The principal consideration is usually how we are going to handle the primary key value. (For more information on primary key values, see the sidebar "The Key to Primary Keys" earlier in this step.) A database insert is very similar to a database update. Information from a form is passed to an action page, and using either SQL or a tag called <CFINSERT>, we plug the values from the form into the database. When we did an update, however, the record we were dealing withand its primary key valuealready existed in the database. When performing a database insert, we are creating a new record and primary key value. For this operation to work smoothly, we must know how the primary key values are being generated. If we were using something like social security numbers as the primary key values for each of our employees, that would be a value that we would allow our users to type into the form and subsequently pass to the action page. In our Staff.mdb database, however, we are using a Microsoft Access autonumber field to automatically generate a primary key value, our EmployeeID number. Because the database is automatically generating the primary key value, it is not something we want our users to be able to fill in on a form. Our users will fill out all other employee details as required and will submit these to the action page. The database will then take care of assigning a new primary key value for this new record. Just as with a database update, we have to make sure we are passing the appropriate types of data (string, numeric, date, and so on) to the waiting database. Using Insert SQL As previously mentioned, we can insert new records into our database using the <CFQUERY> tag and SQL in a similar way to how we used SQL to update our database. The structure of an INSERT SQL statement is pretty straightforward, so let's have a look at an example. <CFQUERY NAME="qInsert" DATASOURCE="Staff"> INSERT INTO Employees (FirstName, LastName, Extension, DateHired) VALUES ('#Trim(FORM.FirstName)#', '#Trim(FORM.LastName)#', #Val(FORM.Extension)#, #CreateODBCDate(FORM.DateHired)#) </CFQUERY> The <CFQUERY> tag provides the DSN of the database into which we are going to be inserting information. Note that, as with our SQL UPDATE statement, we do not strictly require a NAME attribute for this query because it will only be inserting information into the database, and we will not be outputting any query variables on the action page. The SQL statement begins with the INSERT INTO keywords followed by the name of the database table we will be using. Following the table name is a list of the field names in that table, into which we will be inserting information. The next portion of the statement consists of the VALUES keyword followed by a list of variables that contain the values we'll be plugging into our database fields. Remember that databases are quite particular about SQL syntax. Note that the number of fields in the INSERT INTO portion of the SQL statement must be equal to the number of variables in the VALUES section of the statement. In addition, values in each list must be separated with commas (with no trailing comma after the last value in the list) and surrounded with parentheses. The syntax might vary slightly with different database systems, so check your database system's documentation if you are having problems. It is important to point out that nowhere do we indicate the value of the EmployeeID field for our newly created employee. Because we are using a Microsoft Access autonumber data type for this field, when we insert the other employee information, Access will generate a new EmployeeID number and insert it into the EmployeeID field for that employee automatically. Using <CFINSERT> ColdFusion also provides the capability to insert records into a database through the use of the <CFINSERT> tag (which should not be confused with the <CFINPUT> tag). The <CFINSERT> tag functions in much the same way as the <CFUPDATE> tag. It requires the name of the data source into which we want to insert data, as well as the name of the table within that data source. The following line of code illustrates the use of the <CFINSERT> tag: <CFINSERT DATASOURCE="Staff" TABLENAME="Employees"> Most of the rules that apply to the <CFUPDATE> tag also apply to <CFINSERT>. For instance, the form field names must be exactly the same as the database fields into which the action page will be inserting values. The major difference in use between the two tags is the handling of the primary key field. Although it must be passed via a form variable when using <CFUPDATE>, this is not the case with <CFINSERT>. In the case of our Employees table, we will use the form to pass all other information except the primary key value (EmployeeID) because our database is set up to generate this number automatically. However, if your database expects users to enter a primary key value (like a stock number), that value must also be passed to the action page as a form variable. As with <CFUPDATE>, you can limit the number of form variables that the <CFINSERT> tag will actually insert into the database by using the FORMFIELDS attribute. The following line of code demonstrates the use of the FORMFIELDS attribute to insert only the FirstName and LastName fields into the database. <CFINSERT DATASOURCE="Staff" TABLENAME="Employees" FORMFIELDS="FirstName,LastName"> | 
