You usually use two application pages to insert data into a database:
You can create an insert form with standard HTML form tags or with cfform
tags (see "Creating 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.
The following procedure creates a form using standard HTML tags. The form looks like the following in your web browser:
<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> </td> <td><input type="Submit" value="Submit"> <input type="Reset" value="Clear Form"></td> </tr> </form> <!--- end html form ---> </table> </body> </html>
Note: The form will not work until you write an action page for it. For more information, see "Creating an action page to insert data".
If you use the cfinsert
tag in the action page to insert the data into the database, you should follow these rules for creating the form page:
cfinsert
inserts all of the form's fields into the database columns with the same names. For example, it puts 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
".
You can use the cfinsert
tag or the cfquery
tag to create an action page that inserts data into a database.
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.
<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 ---> <cfinsert datasource="CompanyInfo" tablename="Employee"> <h1>Employee Added</h1> <cfoutput>You have added #Form.FirstName# #Form.Lastname# to the employee database. </cfoutput> </body> </html>
Note: You might wish to compare views of the Employee table in the CompanyInfo data source before and after inserting values in the form.
ColdFusion inserts your values into the Employee table and displays a confirmation message.
The following table describes the code and its function:
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 Chapter 26, "Retrieving and Formatting Data".
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".
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="CompanyInfo"> 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>
ColdFusion inserts your values into the Employee table and displays a confirmation message.
The following table describes the highlighted code and its function:
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="CompanyInfo">
INSERT INTO Employee (Emp_ID,FirstName,LastName, Dept_ID,Contract) VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName#', #Form.Dept_ID#,'#Form.Contract#') </cfquery>