| Now that we have the form page out of the way, we can look at the action page. The action page we create will need to be able to take the information passed to it by our form and use it to update a particular record in our database. Using ColdFusion, there are two ways we can accomplish this. One way is to use a SQL statement to update the database. Instead of using the SELECT keyword, we will use another keyword called (wait for it) UPDATE. The other way to affect a database update is to use the <CFUPDATE> tag. Because we all want to be clever little monkeys, we are going to look at both methods. Update SQL In previous steps, we used SQL SELECT statements to select database records that we wanted to pull out and view. We also can utilize SQL statements to put information into our database. This is accomplished using the SQL UPDATE keyword. A SQL UPDATE consists of three elements. The first is the UPDATE keyword, which indicates which table you want to update. The second is the SQL SET keyword followed by the fields you want to update and the new values you want those fields to adopt. Finally, the most important element is the WHERE clause, which identifies which record you want to update. The following code shows an example of a SQL UPDATE statement: <CFQUERY NAME="qUpdate" DATASOURCE="Staff" > UPDATE Employees SET FirstName='#Trim(FORM.FirstName)#', LastName='#Trim(FORM.LastName)#', Extension=#Val(FORM.Extension)#, DateHired=#CreateODBCDate(FORM.DateHired)# WHERE EmployeeID=#FORM.EmployeeID# </CFQUERY> NOTE The NAME attribute is not strictly required with this <CFQUERY> because it is used only to update the database and we won't be referring to any values from this query anywhere else in our template. However, it is not a bad idea to give the query a descriptive name to help other developers (and yourself ) identify the purpose of the code. The preceding SQL block would take incoming form variables and plug them into their corresponding fields in the Employees table, where the EmployeeID field value matches an EmployeeID passed by the form. A big warning here for new players: If you leave off the WHERE clause, this statement will update every record in the table. This means that if you ran this SQL statement without a WHERE clause, everyone in the company would end up with the same name, extension number, and hire date. A very big oops! Databases are very persnickety about what information they will accept. If a particular field is set up to take date information, it is going to accept only properly formatted date information. If the field is set up to take numeric data, it is going to accept only numeric data. The moral of the story is that we have to get our UPDATE statements (and INSERT statements, which we will see shortly) just right. One thing to keep in mind here is that although ColdFusion is a typeless language, SQL is not; this means that ColdFusion's capability to automatically evaluate the context of an operation and convert strings to numbers (and vice versa) does not apply to SQL statements. With this revelation in mind, let's have a look at some of the finer points of the UPDATE statement previously shown. Let's have a look at the first two SET statements. Notice that we are using the Trim() function to get rid of any unnecessary whitespace before we insert the values into the database. Also note that the values are surrounded by single quotation marks; this indicates that the value will be passed as a string value. The database expects a numerical value for the Extension field. To make sure that all goes smoothly and that we give the database what it expects, we use the Val() function to make sure that ColdFusion converts the value in the FORM.Extension variable into a number before handing it over to the database. Also note that #Val(FORM.Extension)# is not surrounded by single quotes; this indicates that the value will be passed as a number rather than as a string. Most databases are very finicky about accepting date information and demand that it is in just the right format. ColdFusion comes to the rescue once again. We use a wonderful little ColdFusion function called CreateODBCDate() to make sure that the date value in the FORM.DateHired variable is in a format that can safely be used in SQL statements. There you gothe finer points of update SQL. Oh yeah, there's one more thing that I still slip up on from time to time. Each pair of field name/new values in the SET portion of our statement needs to be separated by a comma, but don't put a comma after the last pair; otherwise, the whole SQL statement will break. It's always the little things that get you. |