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 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 message for the end user.

Creating an HTML insert form

The following procedure creates a form using standard HTML tags.

To create an insert form:

Create a new application page in ColdFusion Studio.

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="insertaction.cfm" method="post">

  Employee ID:

  <input type="text" name="Emp_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="35" maxlength="50"><br>

  Department Number:

  <input type="Text" name="Dept_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" value="Clear Form">

  <!-- Submit button -->

  <input type="Submit" value="Submit">

</form>



</body>

</html>

Save the file as insertform.cfm in the myapps directory.
View insertform.cfm in a browser.

Data entry form notes and considerations

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:

You only need to create HTML form fields for the database fields into which you want to insert data.
By default, cfinsert inserts all of the form's fields into the database table fields with the same names. For example, it puts the Form.Emp_ID value in the database Emp_ID field. The tag ignores any form fields with no corresponding database column name. You can also use the formfields attribute to specify the fields you want to insert.

Creating an action page to insert data

You can use the cfinsert tag or 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.

To create an insert action page with cfinsert:

Create a new application page in ColdFusion Studio.

Enter the following code:

<!--- Make the contract variable be No if it is not set (check box is

      empty) --->

<cfif not isdefined("Form.Contract")>

   <cfset Form.contract = "No">

</cfif>



<!--- Insert the new record --->

<cfinsert datasource="CompanyInfo" tablename="Employee">



<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>

Save the page as insertaction.cfm.
View insertform.cfm in a browser, enter values, and click the Submit button.
The data is inserted into the Employee table and the message displays.

Reviewing the code

The following table describes the code and its function:

Code

Description

<cfif not isdefined("Form.Contract")> <cfset Form.contract = "No"> </cfif>
If the user clears the Contractor check box, no value gets passed to the action page. The database field must have a value, so check the Form.contract variable and set it to No if it is not defined.

<cfinsert datasource="CompanyInfo" tablename="Employee">
Create a new row in the Employee table of the CompanyInfo database. Insert data from the form into database fields with the same names as the form fields.

<cfoutput>You have added #Form.FirstName# #Form.Lastname# to the employees database. </cfoutput>
Inform the user that the data was inserted into the database.

Code	Description
<cfif not isdefined("Form.Contract")> <cfset Form.contract = "No"> </cfif>	If the user clears the Contractor check box, no value gets passed to the action page. The database field must have a value, so check the Form.contract variable and set it to No if it is not defined.
<cfinsert datasource="CompanyInfo" tablename="Employee">	Create a new row in the Employee table of the CompanyInfo database. Insert data from the form into database fields with the same names as the form fields.
<cfoutput>You have added #Form.FirstName# #Form.Lastname# to the employees database. </cfoutput>	Inform the user that the data was 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 data database columns. You can use the hidden field validation functions for these fields to display a custom error message.

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 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 page with cfquery:

Rename (or delete) the insertaction.cfm page that you created in the previous section.
Create a new application page in ColdFusion Studio.

Enter the following code:

<!--- Make the contract variable be No if it is not set 

    (check box is empty) --->

<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>



<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>

Save the page as insertaction.cfm.
View insertform.cfm in a browser, enter values, and click Submit.
The data is inserted into the Employee table and the message displays.

Reviewing the code

The following table describes the highlighted code and its function:

Code

Description

<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>
Use a cfquery tag to insert a new row into the Employee table of the CompanyInfo Database. Specify each form field to be added. Because the form and database field names are identical, you do not have to specify the database field names in the query.

Developing ColdFusion Applications
Updating Your Database