ColdFusion 9.0 Resources

Updating Your Database

Adobe ColdFusion lets you insert, update, and delete information in a database.

About updating your database

ColdFusion was originally developed as a way to readily interact with databases. You can quickly insert, update, and delete the contents of your database by using ColdFusion forms, which are typically a pair of pages. One page displays the form with which your end user enters values; the other page performs the action (insert, update, or delete).

Depending on the extent and type of data manipulation, you can use CFML with or without SQL commands. If you use SQL commands, ColdFusion requires a minimal amount of SQL knowledge.

Inserting data

You usually use two application pages to insert data into a database:

An insert form
An insert action page

You can create an insert form with standard HTML form tags or with cfform tags (see Creating custom forms with the cfform tag). When the user submits the form, form variables are passed to a ColdFusion action page that performs an insert operation (and whatever else is called for) on the specified data source. The insert action page can contain either a cfinsert tag or a cfquery tag with a SQL INSERT statement. The insert action page should also contain a confirmation message for the end user.

Creating an HTML insert form

The following procedure creates a form using standard HTML tags.

Create a ColdFusion page with the following content:

<html> 
<head> 
<title>Insert Data Form</title> 
</head> 
 
<body> 
<h2>Insert Data Form</h2> 
 
<table> 
<!--- begin html form;  
put action page in the "action" attribute of the form tag. ---> 
<form action="insert_action.cfm" method="post"> 
<tr> 
    <td>Employee ID:</td> 
    <td><input type="text" name="Emp_ID" size="4" maxlength="4"></td> 
</tr> 
<tr> 
    <td>First Name:</td> 
    <td><input type="Text" name="FirstName" size="35" maxlength="50"></td> 
</tr> 
<tr> 
    <td>Last Name:</td> 
    <td><input type="Text" name="LastName" size="35" maxlength="50"></td> 
</tr> 
<tr> 
    <td>Department Number:</td> 
    <td><input type="Text" name="Dept_ID" size="4" maxlength="4"></td> 
</tr> 
<tr> 
    <td>Start Date:</td> 
    <td><input type="Text" name="StartDate" size="16" maxlength="16"></td> 
</tr> 
<tr> 
    <td>Salary:</td> 
    <td><input type="Text" name="Salary" size="10" maxlength="10"></td> 
</tr> 
<tr> 
    <td>Contractor:</td> 
    <td><input type="checkbox" name="Contract" value="Yes" checked>Yes</td> 
</tr> 
<tr> 
    <td>&nbsp;</td> 
    <td><input type="Submit" value="Submit">&nbsp;<input type="Reset" 
value="Clear Form"></td> 
</tr> 
</form> 
<!--- end html form ---> 
</table> 
 
</body> 
</html>

Save the file as insert_form.cfm in the myapps directory under your web_root and view it in your web browser.

Note: The form does not work until you write an action page for it. For more information, see Creating an action page to insert data.

Data entry form notes and considerations

If you use the cfinsert tag in the action page to insert the data into the database, follow these rules for creating the form page:

Create HTML form fields for only the database columns into which you insert data.
By default, cfinsert inserts all of the form’s fields into the database columns with the same names. For example, it places the Form.Emp_ID value in the database Emp_ID column. The tag ignores form fields that lack corresponding database column names.

Note: You can also use the formfields attribute of the cfinsert tag to specify which fields to insert; for example, formfields="prod_ID,Emp_ID,status".

Creating an action page to insert data

You can use the cfinsert tag or the cfquery tag to create an action page that inserts data into a database.

Creating an insert action page with cfinsert

The cfinsert tag is the easiest way to handle simple inserts from either a cfform or an HTML form. This tag inserts data from all the form fields with names that match database field names.

Create a ColdFusion page with the following content:

<html> 
<head> <title>Input form</title> </head> 
 
<body> 
<!--- If the Contractor check box is clear,  
    set the value of the Form.Contract to "No" ---> 
<cfif not isdefined("Form.Contract")> 
    <cfset Form.Contract = "N"> 
</cfif> 
 
<!--- Insert the new record ---> 
<cfinsert datasource="cfdocexamples" tablename="EMPLOYEE"> 
 
<h1>Employee Added</h1> 
<cfoutput> You have added #Form.FirstName# #Form.Lastname# to the employee database. 
</cfoutput> 
 
</body> 
</html>

Save the page as insert_action.cfm.
View insert_form.cfm in your web browser and enter values.

Note: You might want to compare views of the Employee table in the cfdocexamples data source before and after inserting values in the form.
Click Submit.

ColdFusion inserts your values into the Employee table and displays a confirmation message.

Reviewing the code

The following table describes the code and its function:

Code	Description
<cfif not isdefined("Form.Contract")> <cfset Form.Contract = "N"> </cfif>	Sets the value of `Form.Contract` to `No` if it is not defined. If the Contractor check box is unchecked, no value is passed to the action page; however, the database field must have some value.
<cfinsert datasource="cfdocexamples" tablename="EMPLOYEE">	Creates a row in the Employee table of the cfdocexamples database. Inserts data from the form into the database fields with the same names as the form fields.
<cfoutput>You have added #Form.FirstName# #Form.Lastname# to the employee database.</cfoutput>	Informs the user that values were inserted into the database.

Note: If you use form variables in cfinsert or cfupdate tags, ColdFusion automatically validates any form data it sends to numeric, date, or time database columns. You can use the hidden field validation functions for these fields to display a custom error message. For more information, see Introduction to Retrieving and Formatting Data.

Creating an insert action page with cfquery

For more complex inserts from a form submittal, you can use a SQL INSERT statement in a cfquery tag instead of using a cfinsert tag. The SQL INSERT statement is more flexible because you can insert information selectively or use functions within the statement.

The following procedure assumes that you have created the insert_action.cfm page, as described in Creating an insert action page with cfinsert.

In insert_action.cfm, replace the cfinsert tag with the following highlighted cfquery code:

<html> 
<head> 
    <title>Input form</title> 
</head> 
 
<body> 
<!--- If the Contractor check box is clear), set the value of the Form.Contract  
    to "No" ---> 
<cfif not isdefined("Form.Contract")> 
    <cfset Form.Contract = "No"> 
</cfif> 
 
<!--- Insert the new record ---> 
<cfquery name="AddEmployee" datasource="cfdocexamples"> 
    INSERT INTO Employee 
    VALUES (#Form.Emp_ID#, '#Form.FirstName#', 
    '#Form.LastName#', #Form.Dept_ID#, 
    '#Form.StartDate#', #Form.Salary#, '#Form.Contract#') 
</cfquery> 
 
<h1>Employee Added</h1> 
<cfoutput>You have added #Form.FirstName# #Form.Lastname# to the employee database. 
</cfoutput> 
 
</body> 
</html>

Save the page.
View insert_form.cfm in your web browser and enter values.
Click Submit.

ColdFusion inserts your values into the Employee table and displays a confirmation message.

Reviewing the code

The following table describes the highlighted code and its function:

Code

Description

<cfquery name="AddEmployee" datasource="cfdocexamples"> 
INSERT INTO Employee VALUES (#Form.Emp_ID#, 
'#Form.FirstName#', '#Form.LastName#', 
#Form.Dept_ID#, '#Form.StartDate#', 
#Form.Salary#, '#Form.Contract#') 
</cfquery>

Inserts a new row into the Employee table of the cfdocexamples database. Specifies each form field to be added.

Because you are inserting data into all database fields in the same left-to-right order as in the database, you do not have to specify the database field names in the query.

Because #From.Emp_ID#, #Form.Dept_ID#, and #Form.Salary# are numeric, they do not need to be enclosed in quotation marks.

Inserting into specific fields

The preceding example inserts data into all the fields of a table (the Employee table has seven fields). There might be times when you do not want users to add data into all fields. To insert data into specific fields, the SQL statement in the cfquery must specify the field names following both INSERT INTO and VALUES. For example, the following cfquery omits salary and start date information from the update. Database values for these fields are 0 and NULL, respectively, according to the database’s design.

<cfquery name="AddEmployee" datasource="cfdocexamples"> 
    INSERT INTO Employee 
        (Emp_ID,FirstName,LastName, 
        Dept_ID,Contract)     
    VALUES  
        (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName#',  
        #Form.Dept_ID#,'#Form.Contract#') 
</cfquery>

Updating data

You usually use the following two application pages to update data in a database:

An update form
An update action page

You can create an update form with cfform tags or HTML form tags. The update form calls an update action page, which can contain either a cfupdate tag or a cfquery tag with a SQL UPDATE statement. The update action page should also contain a confirmation message for the end user.

Creating an update form

The following are the key differences between an update form and an insert form:

An update form contains a reference to the primary key of the record that is being updated.

A primary key is a fields in a database table that uniquely identifies each record. For example, in a table of employee names and addresses, only the Emp_ID is unique to each record.
An update form is populated with existing record data.

The easiest way to designate the primary key in an update form is to include a hidden input field with the value of the primary key for the record you want to update. The hidden field indicates to ColdFusion which record to update.

Create a ColdFusion page with the following content:

<html> 
<head> 
<title>Update Form</title> 
</head> 
 
<body> 
<cfquery name="GetRecordtoUpdate" datasource="cfdocexamples"> 
    SELECT * FROM Employee 
    WHERE Emp_ID = #URL.Emp_ID# 
</cfquery> 
 
 
<cfoutput query="GetRecordtoUpdate"> 
<table> 
<form action="update_action.cfm" method="Post"> 
    <input type="Hidden" name="Emp_ID" value="#Emp_ID#"><br> 
<tr> 
    <td>First Name:</td>  
    <td><input type="text" name="FirstName" value="#FirstName#"></td> 
</tr> 
<tr> 
    <td>Last Name:</td> 
    <td><input type="text" name="LastName" value="#LastName#"></td> 
</tr> 
<tr> 
    <td>Department Number:</td> 
    <td><input type="text" name="Dept_ID" value="#Dept_ID#"></td> 
</tr> 
<tr> 
    <td>Start Date:</td> 
    <td><input type="text" name="StartDate" value="#StartDate#"></td> 
</tr> 
<tr> 
    <td>Salary:</td> 
    <td><input type="text" name="Salary" value="#Salary#"></td> 
</tr> 
<tr> 
    <td>Contractor:</td> 
    <td><cfif #Contract# IS "Yes"> 
        <input type="checkbox" name="Contract" checked>Yes 
    <cfelse> 
        <input type="checkbox" name="Contract">Yes 
    </cfif></td> 
</tr> 
<tr> 
    <td>&nbsp;</td> 
    <td><input type="Submit" value="Update Information"></td> 
</tr> 
</form> 
</table> 
</cfoutput> 
 
</body> 
</html>

Save the file as update_form.cfm.
View update_form.cfm in your web browser by specifying the page URL and an Employee ID; for example, enter the following: http://localhost/myapps/update_form.cfm?Emp_ID=3

Note: Although you can view an employee’s information, code an action page before you can update the database. For more information, see Creating an action page to update data.

Reviewing the code

The following table describes the code and its function:

Code	Description
<cfquery name="GetRecordtoUpdate" datasource="cfdocexamples"> SELECT * FROM Employee WHERE Emp_ID = #URL.Emp_ID# </cfquery>	Queries the cfdocexamples data source and returns records in which the employee ID matches what was entered in the URL that called this page.
<cfoutput query="GetRecordtoUpdate"> ... </cfoutput>	Makes available as variables the results of the GetRecordtoUpdate query in the form created in subsequent lines.
<form action="update_action.cfm" method="Post"> ... </form>	Creates a form whose variables are processed on the update_action.cfm action page.
<input type="Hidden" name="Emp_ID" value="#Emp_ID#"><br>	Uses a hidden input field to pass the Emp_ID (primary key) value to the action page.
First Name: <input type="text" name="FirstName" value="#FirstName#"><br> Last Name: <input type="text" name="LastName" value="#LastName#"><br> Department Number: <input type="text" name="Dept_ID" value="#Dept_ID#"><br> Start Date: <input type="text" name="StartDate" value="#StartDate#"><br> Salary: <input type="text" name="Salary" value="#Salary#"><br>	Populates the fields of the update form. This example does not use ColdFusion formatting functions. As a result, start dates look like 1985-03-12 00:00:00 and salaries do not have dollar signs or commas. The user can replace the information in any field using any valid input format for the data.
Contracto r: < cfif #Contract# IS "Yes"> <input type="checkbox" name="C ontract" checked>Yes<br> <cfelse> <input type="checkbox" name="Contract"> Yes <br> </cfif> <br> <input type="Submit" value="Update Information"> </form> </cfoutput>	The Contract field requires special treatment because a check box appears and sets its value. The cfif structure puts a check mark in the check box if the Contract field value is `Yes`, and leaves the box empty otherwise.

Creating an action page to update data

You can create an action page to update data with either the cfupdate tag or cfquery with the UPDATE statement.

Creating an update action page with cfupdate

The cfupdate tag is the easiest way to handle simple updates from a front-end form. The cfupdate tag has an almost identical syntax to the cfinsert tag.

To use the cfupdate tag, include the primary key fields in your form submittal. The cfupdate tag automatically detects the primary key fields in the table that you are updating and looks for them in the submitted form fields. ColdFusion uses the primary key fields to select the record to update (therefore, you cannot update the primary key value itself). It then uses the remaining form fields that you submit to update the corresponding fields in the record. Your form only needs to have fields for the database fields that you want to change.

Create a ColdFusion page with the following content:

<html> 
<head> 
    <title>Update Employee</title> 
</head> 
<body> 
<cfif not isdefined("Form.Contract")> 
    <cfset form.contract = "N"> 
<cfelse> 
    <cfset form.contract = "Y"> 
</cfif> 
 
<cfupdate datasource="cfdocexamples" tablename="EMPLOYEE"> 
 
<h1>Employee Updated</h1> 
<cfoutput> 
You have updated the information for #Form.FirstName# #Form.LastName# in the employee 
    database. 
</cfoutput> 
 
</body> 
</html>

Save the page as update_action.cfm.
View update_form.cfm in your web browser by specifying the page URL and an Employee ID; for example, enter the following: http://localhost/myapps/update_form.cfm?Emp_ID=3
Enter new values in any of the fields, and click Update Information.

ColdFusion updates the record in the Employee table with your new values and displays a confirmation message.

Reviewing the code

The following table describes the code and its function:

Code	Description
<cfif not isdefined("Form.Contract")> <cfset Form.contract = "N"> <cfelse> <cfset form.contract = "Y"> </cfif>	Sets the value of `Form.Contract` to `No` if it is not defined, or to `Yes` if it is defined. If the Contractor check box is unchecked, no value is passed to the action page; however, the database field must have some value.
<cfupdate datasource="cfdocexamples" tablename="EMPLOYEE">	Updates the record in the database that matches the primary key on the form (Emp_ID). Updates all fields in the record with names that match the names of form controls.
<cfoutput> You have updated the information for #Form.FirstName# #Form.LastName# in the employee database. </cfoutput>	Informs the user that the change was made successfully.

Creating an update action page with cfquery

For more complicated updates, you can use a SQL UPDATE statement in a cfquery tag instead of a cfupdate tag. The SQL UPDATE statement is more flexible for complicated updates.

The following procedure assumes that you have created the update_action.cfm page as described in Creating an update action page with cfupdate.

In update_action.cfm, replace the cfupdate tag with the following highlighted cfquery code:

<html> 
<head> 
    <title>Update Employee</title> 
</head> 
<body> 
<cfif not isdefined("Form.Contract")> 
    <cfset form.contract = "No"> 
<cfelse> 
    <cfset form.contract = "Yes"> 
</cfif> 
 
<!--- cfquery requires date formatting when retrieving from  
Access. Use the left function when setting StartDate to trim 
the ".0" from the date when it first appears from the  
Access database ---> 
    <cfquery name="UpdateEmployee" datasource="cfdocexamples"> 
    UPDATE Employee 
    SET FirstName = '#Form.Firstname#', 
        LastName = '#Form.LastName#', 
        Dept_ID = #Form.Dept_ID#, 
        StartDate = '#left(Form.StartDate,19)#', 
        Salary = #Form.Salary# 
    WHERE Emp_ID = #Form.Emp_ID# 
</cfquery> 
 
<h1>Employee Updated</h1> 
<cfoutput> 
You have updated the information for  
#Form.FirstName# #Form.LastName#  
in the employee database. 
</cfoutput> 
</body> 
</html>

Save the page.
View update_form.cfm in your web browser by specifying the page URL and an Employee ID; for example, enter the following: http://localhost/myapps/update_form.cfm?Emp_ID=3
Enter new values in any of the fields, and click Update Information.

ColdFusion updates the record in the Employee table with your new values and displays a confirmation message.

When the cfquery tag retrieves date information from a Microsoft Access database, it displays the date and time with tenths of seconds, as follows:

Deleting data

You use a cfquery tag with a SQL DELETE statement to delete data from a database. ColdFusion has no cfdelete tag.

Deleting a single record

To delete a single record, use the table’s primary key in the WHERE condition of a SQL DELETE statement. In the following procedure, Emp_ID is the primary key, so the SQL Delete statement is as follows:

DELETE FROM Employee WHERE Emp_ID = #Form.Emp_ID#

You often want to see the data before you delete it. The following procedure displays the data to be deleted by reusing the form page used to insert and update data. Any data that you enter in the form before submitting it is not used, so you can use a table to display the record to be deleted instead.

In update_form.cfm, change the title to “Delete Form” and the text on the submit button to “Delete Record”.
Change the form tag so that it appears as follows:
```
<form action="delete_action.cfm" method="Post">
```
Save the modified file as delete_form.cfm.

Create a ColdFusion page with the following content:

<html> 
<head> 
<title>Delete Employee Record</title> 
</head> 
<body> 
 
<cfquery name="DeleteEmployee" 
    datasource="cfdocexamples"> 
        DELETE FROM Employee 
        WHERE Emp_ID = #Form.Emp_ID# 
</cfquery> 
 
<h1>The employee record has been deleted.</h1> 
<cfoutput> 
You have deleted #Form.FirstName# #Form.LastName# from the employee database. 
</cfoutput> 
</body> 
</html>

Save the page as delete_action.cfm.
View delete_form.cfm in your web browser by specifying the page URL and an Employee ID; for example, enter the following: http://localhost/myapps/delete_form.cfm?Emp_ID=3.Click Delete Record

ColdFusion deletes the record in the Employee table and displays a confirmation message.

Reviewing the code

The following table describes the code and its function:

Code	Description
<cfquery name="DeleteEmployee" datasource="cfdocexamples"> DELETE FROM Employee WHERE Emp_ID = #Form.Emp_ID# </cfquery>	Deletes the record in the database whose Emp_ID column matches the Emp_ID (hidden) field on the form. Since the Emp_ID is the table’s primary key, only one record is deleted.
<cfoutput> You have deleted #Form.FirstName# #Form.LastName# from the employee database. </cfoutput>	Informs the user that the record was deleted.

Deleting multiple records

You can use a SQL condition to delete several records. The following example deletes the records for everyone in the Sales department (which has Dept_ID number 4) from the Employee table:

DELETE FROM Employee WHERE Dept_ID = 4

To delete all the records from the Employee table, use the following code:

DELETE FROM Employee

Important: Deleting records from a database is not reversible. Use DELETE statements carefully.

Updating Your Database

Contents [Hide]

About updating your database

Inserting data

Creating an HTML insert form

Data entry form notes and considerations

Creating an action page to insert data

Creating an insert action page with cfinsert

Reviewing the code

Creating an insert action page with cfquery

Reviewing the code

Inserting into specific fields

Updating data

Creating an update form

Reviewing the code

Creating an action page to update data

Creating an update action page with cfupdate

Reviewing the code

Creating an update action page with cfquery

Deleting data

Deleting a single record

Reviewing the code

Deleting multiple records