Adobe ColdFusion 8

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.

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

  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.