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.

  1. 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>
  2. 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 in Inserting 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.

  1. 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>
  2. Save the page as insert_action.cfm.

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

  4. 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 Inserting data.

  1. In insert_action.cfm, replace the cfinsert tag with the following highlighted cfquerycode:

    <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>
  2. Save the page.

  3. View insert_form.cfm in your web browser and enter values.

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

 Adobe

Get help faster and easier

New user?