This chapter describes how to insert, update, and delete data in a database with ColdFusion.
Contents
• Inserting Data ... 60
• Creating an HTML Insert Form... 60
• Creating an Action Page to Insert Data... 61
• Updating Data ... 62
• Creating an Update Form ... 63
• Creating an Action Page to Update Data ... 65
• Deleting Data... 66
• Requiring Users to Enter Values in Form Fields ... 67
Inserting Data
Inserting data into a database is usually done with two application pages:
• An insert form
• An insert action page
You can create an insert form with CFFORM tags (see “Creating Forms with the CFFORM Tag” on page 124) or with standard HTML form tags. When the form is submitted, 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 message for the end user.
Creating an HTML Insert Form
To create an insert form:
1. Create a new application page in Studio. 2. Edit the page so that it appears as follows:
<HTML> <HEAD>
<TITLE>Insert Data Form</TITLE> </HEAD>
<BODY>
<H2>Insert Data Form</H2>
<FORM ACTION="insertdata.cfm" METHOD="Post"> Employee ID:
<INPUT TYPE="text" NAME="Employee_ID" SIZE="4" MAXLENGTH="4"><BR> First Name:
<INPUT TYPE="text" NAME="FirstName" SIZE="35" MAXLENGTH="50"><BR> Last Name:
<INPUT TYPE="text" NAME="LastName" SIZE="10" MAXLENGTH="10"><BR> Department Number:
<INPUT TYPE="text" NAME="Department_ID" SIZE="4" MAXLENGTH="4"><BR>
Start Date:
<INPUT TYPE="text" NAME="StartDate" SIZE="16" MAXLENGTH="16"><BR> Salary:
<INPUT TYPE="text" NAME="Salary" SIZE="10" MAXLENGTH="10"><BR> Contractor:
<INPUT TYPE="checkbox" name="Contract" value="Yes" checked>Yes<BR><BR>
<INPUT TYPE="reset" NAME="ResetForm" VALUE="Clear Form"> <INPUT TYPE="submit" NAME="SubmitForm" VALUE="Insert Data"> </FORM>
Chapter 6: Updating Your Data 61
</HTML>
3. Save the file as insertform.cfm in the myapps directory. 4. View insertform.cfm in a browser.
Data Entry Form Notes and Considerations
Creating data entry fields for an HTML form is very simple:• You need only create the HTML form fields for each database field into which you want to insert data.
• The names of your form fields must be identical to the names of your database fields.
• You can use the full range of HTML input controls, including list boxes, radio buttons, checkboxes, and multi-line text boxes in your forms.
• ColdFusion uses the NAME attribute to map HTML form fields to the
corresponding database fields and inserts the data entered by the user into the appropriate database fields.
Creating an Action Page to Insert Data
There are two ways to create an action page to insert data into a database.
The CFINSERT tag is the easiest way to handle simple inserts from either a CFFORM or an HTML form.
For more complex inserts from a form submittal you can use a SQL INSERT statement in a CFQUERY tag instead of a CFINSERT tag. The SQL INSERT statement is more flexible because you can insert information selectively or use functions within the statement.
To create an insert action page with CFINSERT:
1. Create a new application page in Studio. 2. Enter the following code:
4 <CFINSERT DATASOURCE="CompanyInfo" TABLENAME="Employees"> <HTML> <HEAD> <TITLE>Input Form</TITLE> </HEAD> <BODY> <H1>Employee Added</H1> <CFOUTPUT>
You have added #Form.FirstName# #Form.LastName# to the Employees database.
</CFOUTPUT> </BODY> </HTML>
3. Save the page. as insertpage.cfm.
4. View insertform.cfm in a browser, enter values, and click the Submit button. 5. The data is inserted into the Employees table and the message appears.
To create an insert page with CFQUERY:
1. Create a new application page in Studio. 2. Enter the following code:
4 <CFQUERY NAME="AddEmployee" 4 DATASOURCE="CompanyInfoB">
4 INSERT INTO Employees (Fi’, ’#Form.LastName#’, 4 ’#Form.Phone#’)
4 </CFQUERY> <HTML> <HEADER>
<TITLE>Insert Action Page</TITLE> </HEADER>
<BODY>
<H1>Employee Added</H1> <CFOUTPUT>
You have added #Form.FirstName# #Form.LastName# to the Employees database.
</CFOUTPUT> </BODY> </HTML>
3. Save the page. as insertpage.cfm.
4. View isertform.cfm in a browser, enter values, and click the Submit button. 5. The data is inserted into the Employees table and the message appears.
Updating Data
Updating data in a database is usually done with two pages:
• 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 message for the end user that reports on the update completion.
Chapter 6: Updating Your Data 63
Creating an Update Form
An update form is similar to an insert form, but there are two key differences:
• An update form contains a reference to the primary key of the record that is being updated.
A primary key is a field or combination of fields in a database table that uniquely identifies each record in the table. For example, in a table of employee names and addresses, only the Employee_ID would be unique to each record.
• Because the purpose of an update form is to update existing data, the update form is usually 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.
To create an update form:
1. Create a new page in Studio.
2. Edit the page so that it appears as follows: <CFQUERY NAME="GetRecordtoUpdate"
DATASOURCE="CompanyInfo"> SELECT *
FROM Employees
WHERE Employee_ID = #URL.Employee_ID# </CFQUERY> <HTML> <HEAD> <TITLE>Update Form</TITLE> </HEAD> <BODY> <CFOUTPUT QUERY="GetRecordtoUpdate">
<FORM ACTION="UpdatePage.cfm" METHOD="Post"> <INPUT TYPE="Hidden" NAME="Employee_ID"
VALUE="#Employee_ID#"><BR> 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="Department_ID" VALUE="#Department_ID#"><BR>
Start Date:
<INPUT TYPE="text" NAME="StartDate" VALUE="#StartDate#"><BR> Salary:
<INPUT TYPE="text" NAME="Salary" VALUE="#Salary#"><BR> Contractor:
<INPUT TYPE="Submit" VALUE="Update Information"> </FORM>
</CFOUTPUT> </BODY> </HTML>
3. Save the page. as updatedorm.cfm. 4. View updateform.cfm in a browser.
Code Review
Code Description <CFQUERY NAME="GetRecordtoUpdate" DATASOURCE="CompanyInfo"> SELECT * FROM EmployeesWHERE Employee_ID = #URL.Employee_ID# </CFQUERY>
Query the CompanyInfo
datasource and return the records in which the employee ID matches what was entered in the URL.
<CFOUTPUT QUERY="GetRecordtoUpdate"> Display the results of the GetRecordtoUpdate query. <FORM ACTION="EmployeeUpdate.cfm" METHOD="Post"> Create a form whose variables will
be process on the
EmployeeUpdate.cfm action page. <INPUT TYPE="Hidden" NAME="Employee_ID"
VALUE="#Employee_ID#"><BR>
Use a hidden input field to pass the employee ID 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="Department_ID" VALUE="#Department_ID#"><BR> Start Date: <INPUT TYPE="text" NAME="StartDate" VALUE="#StartDate#"><BR>
Salary: <INPUT TYPE="text" NAME="Salary" VALUE="#Salary#"><BR>
Contractor: <INPUT TYPE="checkbox" name="Contract" value="Yes" checked>Yes<BR><BR>
<INPUT TYPE="Submit" VALUE="Update Information"> </FORM>
</CFOUTPUT>
Populate the fields of the update form.
Chapter 6: Updating Your Data 65
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.
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 CFUPDATE, you must include all of the fields that make up the primary key in your form submittal. The CFUPDATE tag automatically detects the primary key fields in the table you are updating and looks for them in the submitted form fields.
ColdFusion uses the primary key field(s) to select the record to update. It then updates the appropriate fields in the record using the remaining form fields submitted. 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.
To create an update page with CFUPDATE:
1. Create a new application page in Studio. 2. Enter the following code:
4 <CFUPDATE DATASOURCE="CompanyInfo" TABLENAME="Employees"> <HTML> <HEAD> <TITLE>Update Employee</TITLE> </HEAD> <BODY> <H1>Employee Added</H1> <CFOUTPUT>
You have updated the information for #Form.FirstName# #Form.LastName# in the Employees database.
</CFOUTPUT> </BODY> </HTML>
3. Save the page. as updatepage.cfm.
4. View updateform.cfm in a browser, enter values, and click the Submit button. 5. The data is updated in the Employees table and the message appears.
To create an update page with CFQUERY:
1. Create a new application page in Studio. 2. Enter the following code:
4 <CFQUERY NAME="UpdateEmployee" 4 DATASOURCE="CompanyInfo">
4 UPDATE Employees 4 SET Firstname=’#Form.Firstname#’, 4 LastName=’#Form.LastName#’, 4 Department_ID=’#Form.Department_ID#’ 4 StartDate=’#Form.StartDate#’> 4 Salary=#Form.Salary#> WHERE Employee_ID=#Employee_ID# </CFQUERY> <H1>Employee Added</H1> <CFOUTPUT>
You have updated the information for #Form.FirstName# #Form.LastName# in the Employees database.
</CFOUTPUT>
3. Save the page. as updatepage.cfm.
4. View updateform.cfm in a browser, enter values, and click the Submit button. 5. The data is updated into the Employees table and the message appears.
Code Review
Deleting Data
Deleting data in a database can be done with a single delete page. The delete page contains a CFQUERY tag with a SQL delete statement.
To delete one record from a database:
1. Open the file updateform.cfm in Studio.
2. Modify the file by changing the FORM tag so that it appears as follows: <FORM ACTION="deletepage.cfm" METHOD="Post">
3. Save the modified file as deleteform.cfm. 4. Create a new application page in Studio.
Code Description <CFQUERY NAME="UpdateEmployee" DATASOURCE="CompanyInfo"> UPDATE Employees SET Firstname=’#Form.Firstname#’, LastName=’#Form.LastName#’, Department_ID=’#Form.Department_ID#’ StartDate=’#Form.StartDate#’> Salary=#Form.Salary#> WHERE Employee_ID=#Employee_ID# </CFQUERY>
After the SET clause, you must name a table column. Then, you indicate a constant or expression as the value for the column.
Be sure to remember the WHERE statement. If you do not use it, If you do not use it, the SQL UPDATE statement will apply the new information to every row in the database.
Chapter 6: Updating Your Data 67
5. Enter the following code:
<CFQUERY NAME="DeleteEmployee" DATASOURCE="CompanyInfo"> DELETE FROM Employees
WHERE Employee_ID = #URL.EmployeeID# </CFQUERY>
<HTML> <HEAD>
<TITLE>Delete Employee Record</TITLE> </HEAD>
<BODY>
<H3>The employee record has been deleted.</H3> </BODY>
</HTML>
6. Save the page. as deletepage.cfm.
7. View deleteform.cfm in a browser, enter values, and click the Submit button. The employee is deleted from the Employees table and the message appears. To delete several records, you would specify a condition. The following example demonstrates deleting the records for everyone in the Sales department from the Employee table. The example assumes that there are several Employees in the sales department.
DELETE FROM Employees WHERE Department = ’Sales’
To delete all the records from the Employees table, you would use the following: DELETE FROM Employees
Note
Deleting records from a database is not reversible. Use delete statements carefully.Requiring Users to Enter Values in Form Fields
One of the limitations of HTML forms is the inability to define input fields as required. Because this is a particularly important requirement for database applications, ColdFusion provides a server-side mechanism for requiring users to enter data in fields.
To define an input field as required, use a hidden field that has a NAME attribute composed of the field name and the suffix "_required." For example, to require that the user enter a value in the FirstName field, use the syntax:
<INPUT TYPE="hidden" NAME="FirstName_required">
If the user leaves the FirstName field empty, ColdFusion rejects the form submittal and returns a message informing the user that the field is required. You can customize the contents of this error message using the VALUE attribute of the hidden field. For
example, if you want the error message to read "You must enter your first name," use the syntax:
<INPUT TYPE="hidden"
NAME="FirstName_required"
VALUE="You must enter your first name.">
Validating the Data That Users Enter in Form Fields
Another limitation of HTML forms is that you cannot validate that users input the type or range of data you expect. ColdFusion enables you to do several types of data validation by adding hidden fields to forms. The hidden field suffixes you can use to do validation are as follows:
Note
Adding a validation rule to a field does not make it a required field. You need to add a separate _required hidden field if you want to ensure user entry.Form Field Validation Using Hidden Fields Field Suffix Value Attribute Description
_integer Custom error message
Verifies that the user enters a number. If the user enters a floating point value, it is rounded to an integer.
_float Custom error message
Verifies that the user enters a number. Does not do any rounding of floating point values. _range MIN=MinValue
MAX=MaxValue
Verifies that the numeric value entered is within the specified boundaries. You can specify one or both of the boundaries separated by a space.
_date Custom error message
Verifies that a date has been entered and converts the date into the proper ODBC date format. Will accept most common date forms, for example, 9/1/98; Sept. 9, 1998).
_time Custom error message
Verifies that a time has been correctly entered and converts the time to the proper ODBC time format.
_eurodate Custom error message
Verifies that a date has been entered in a standard European date format and converts into the proper ODBC date format.
Chapter 6: Updating Your Data 69
To validate the data users enter in the Insert Form
1. Open the file insertform.cfm in Studio. 2. Modify the file so that it appears as follows:
<HTML> <HEAD>
<TITLE>Insert Data Form</TITLE> </HEAD>
<BODY>
<H2>Insert Data Form</H2>
<FORM ACTION="insertdata.cfm" METHOD="Post"> <INPUT TYPE="hidden"
NAME="DeptID_integer"
VALUE="The department ID must be a number."> <INPUT TYPE="hidden"
NAME="StartDate_date"
VALUE="Enter a valid date as the start date."> <INPUT TYPE="hidden"
NAME="Salary_float"
VALUE="The salary must be a number."> Employee ID: <INPUT TYPE="text" NAME="Employee_ID" SIZE="4" MAXLENGTH="4"><BR> First Name: <INPUT TYPE="text" NAME="FirstName" SIZE="35" MAXLENGTH="50"><BR> Last Name: <INPUT TYPE="text" NAME="LastName" SIZE="10" MAXLENGTH="10"><BR> Department Number: <INPUT TYPE="text" NAME="Department_ID" SIZE="4" MAXLENGTH="4"><BR> Start Date: <INPUT TYPE="text" NAME="StartDate" SIZE="16" MAXLENGTH="16"><BR> Salary: <INPUT TYPE="text" NAME="Salary" SIZE="10" MAXLENGTH="10"><BR> Contractor: <INPUT TYPE="checkbox" NAME="Contract" VALUE="Yes" CHECKED>Yes<BR><BR>
<INPUT TYPE="reset" NAME="ResetForm" VALUE="Clear Form"> <INPUT TYPE="submit" NAME="SubmitForm" VALUE="Insert Data"> </FORM> </HTML> 3. Save the file.
The VALUE attribute is optional. A default message displays if no value is supplied. When the form is submitted, ColdFusion scans the form fields to find any validation rules you specified. The rules are then used to analyze the user’s input. If any of the input rules are violated, ColdFusion sends an error message to the user that explains the problem. The user then must go back to the form, correct the problem and resubmit the form. ColdFusion will not accept form submission until the entire form is entered correctly.
Because numeric values often contain commas and dollar signs, these characters are automatically stripped out of fields with _integer, _float, or _range rules before they are validated and saved to the database.