Inserting Data

The action page that you build to insert data needs to include:

• Local variables that set values for all checkboxes and radio buttons when they are to be inserted into required fields.
• A query to perform the actual data insertion.
• Some code to redirect users to a listing page so that they can confirm the changes that they make to the database.

You'll use the CFLOCATION tag to redirect users after the query insertion.

Building a query to insert data

The CFQUERY tag supports a number of SQL statements that you use to insert, update, and delete records in a table.

For example, you'll use the INSERT statement to insert data into a database.

SQL INSERT syntax

<CFQUERY NAME="QueryName" DataSource="DataSourceName">
    INSERT INTO Table
    (ColumnName, ColumnName,..)
    VALUES
    ('ValueToInsert','ValueToInsert',..)
</CFQUERY>

• The INSERT statement describes the table and the columns to insert data into.
• The VALUES clause describes the values to insert.
• Separate multiple ValueToInsert entries with commas (,).
• Surround a ValueToInsert with single quotes(') when it is a string value.
• Do not surround a ValueToInsert with single quotes when it is a numeric value.

Inserting data usage example

This code inserts an employee's last name and first name into the Employees table:

<CFQUERY NAME="AddEmployees" DATASOURCE="HRApp">
    INSERT INTO Employees
    (FirstName, LastName)
    VALUES
    ('#Form.FirstName#','#Form.LastName#')
</CFQUERY>

• The values that are inserted are variables passed from a form page.
• There is a one-to-one correspondence between the columns and the values named in the INSERT statement.

Note:

The purpose of this guide is to get you up and running with ColdFusion Express. Allaire suggests that you purchase a book on SQL to learn how to use it efficiently.

To build an insert action page:

1. Open InsertAction.cfm in HomeSite.
2. Title the page Chapter 10- ADD EMPLOYEE ACTION PAGE.
3. Set a local variable, ContractStatus, to a value of YES if the FORM.Contract variable exists on the action page and set it to NO if the variable does not exist:

<CFIF IsDefined("Form.Contract") IS "Yes">
    <CFSET ContractStatus="Yes">
<CFELSE>
    <CFSET ContractStatus="No">
</CFIF>

This ensures that a contract value always passes to the database.

4. Begin a query named InsertEmployee:

<CFQUERY NAME="InsertEmployee" DATASOURCE="HRExpress">

5. Add a SQL INSERT statement to the query to insert the variables passed from the INSERTFORM.CFM page and the local variable ContractStatus INTO the EMPLOYEES table:

INSERT 
INTO Employees
(FirstName, LastName, Department_ID, StartDate, Salary, Contract)
VALUES
('#Form.FirstName#','#Form.LastName#', 
#Form.Department_ID#,#Form.StartDate#, #Form.Salary#, 
'#ContractStatus#')

6. End the query:

</CFQUERY>

7. After the query, redirect the user to EmpList.cfm.

<CFLOCATION URL="EmpList.cfm">

8. Save the page.
9. Test InsertAction.cfm by adding employees to the database.

Click here to see how the form and action page should work.

Click here to see InsertAction.cfm's code.

Move on in this chapter to learn about updating data.

Note:

Almost all links will work at this time.

Copyright © 1999, Allaire Corporation. All rights reserved.