Creating the Update Page

I l @ ve RuBoard

1. Open productupdateform.cfm from the Lesson6\Start folder on the CD-ROM.

   Once again, you are using an HTML form to interact with the data from the database. This form should look very similar to the productinsertform.cfm template. This is because it involves the same pieces of product information as before. ColdFusion and SQL still have the same role as before and most of the form has been completed for you, with a few ultra -important pieces left out.

   

2. At the top of the page add the following CFML code:

    <cfquery name="productupdate" datasource="products"> 

   In order to update information you need to have the information. You are setting up the query to retrieve the information by giving it a name and specifying the database to query.

3. Now add the following SQL below that:

    select *  from tblProducts where productid = #url.productid# 

   You are selecting everything from the tblProducts table again, only this time, the WHERE clause has narrowed down the information returned by only selecting information associated with the productid that was passed in. Notice that because the productid field is an integer field, there are no single quotes around the productid variable.

   

4. Add the closing </cfquery> tag.

   You have now collected the specific information for the product that you want to update. Now you need to display that information. You can use the same form fields that you used to input the information in the first place to see and update it now.

5. Add the following CFML code before the <form> tag: <cfoutput query="productupdate"> , and add the corresponding </cfoutput> tag after the </form> tag at the bottom of the page.

   

   This creates the setup for outputting the information that was returned from the query. Now you'll make the existing values appear automatically in the text boxes.

6. Insert the ColdFusion variables returned from the query to the Value attributes for each one of the form fields.

   

   This populates the form fields with the values from the database. Make your edits and updates directly in these fields. When you submit the form ("Update Product"), the new value will be passed to the database, overwriting the old value.

7. After the last input tag, add the following hidden field:

    <input type="hidden" name="productid" value="#productid#"> 

   In order for you to make sure that you are updating the proper product's information on the productupdate.cfm template, you must know what product record to update. By passing the chosen product ID through a hidden form field, you will be able to reference it on the next template when performing the update.

   

8. Choose File > New and select Blank Document from the New Document window.

   Once again, the database workin this case, updating a recordis not seen by the user , so no HTML is necessary.

   

9. Add the following CFML code to the top of the page:

    <cfquery name="productupdate" datasource="products"> 

   The <cfquery> tag names the query and specifies the data source to work with. This is one of the most commonly used ColdFusion tags, so you should be very familiar with it.

10. Add the following SQL: update tblProducts

    By using the Update keyword, you are specifying that you are going to update information in the tblProducts table.

11. Continue by adding the following SQL after the previous:

     set   ProductName = '#form.ProductName#',        ProductDesc = '#form.ProductDesc#',       ProductPrice = '#form.ProductPrice#' 

    NOTE

    The indents in the SQL above are strictly for formatting purposes, to make the commands easier to see.

    When you update a record, you use the Set keyword, followed by the field you want to update, and then the value to update the field with. You only need to use the Set keyword once, and separate with commas any additional fields to be updated.

    

12. Add the clause where productid= #form.productid# after your last set statement.

    The WHERE clause makes sure that only the record with a specific productid is updated. If you were to leave off the WHERE clause, every record in the database would be updated with whatever information that was passed from the form, which of course would be a huge disaster.

    

13. Add the closing </cfquery> tag.

    

    You have now completed the query necessary to update a record!

    Once again, if you were to leave the user at the update page once the processing of the query is finished, they would see a blank screen. Sending them back to the productmenu.cfm template would be a good idea.

    NOTE

    There is a tag for updating the database called <cfupdate> that works very similarly to the <cfinsert> tag discussed earlier in this lesson. Like <cfinsert> , <cfupdate> needs the data source and table name specified, but you must also have the ID field of the database specified in a hidden field in the form. In our application, the field we need is the hidden productid field. Also as with the <cfinsert> tag, if you've got form fields that are not in the database, you'll get an error. In order to avoid this, you can use the Formfields attribute to specify what fields in the table to update. For example, to replace the SQL from above, you could use <cfupdate datasource="products" tablename="tblProducts"> .

14. Add the following CFML code after the query:

     <cflocation url="productmenu.cfm"> 

    The <cflocation> tag will take you to whatever location you have specified in the URL attribute. In this case, you are sending the user back to the productmenu.cfm template.

    

    Now that the code is finished, it's time that you put it to work.

15. Choose File > Save and save the current update file as productupdate.cfm in the Lesson6/Start folder on your local hard drive.

    Make sure that you have saved the other two files, productmenu.cfm and productupdateform.cfm to the Lesson6/Start folder as well.

    

I l @ ve RuBoard