Using Information from Forms ColdFusion Help

Although it is nice to be able to dynamically populate, the real value of these form comes from being able to use the data in useful ways. When working with database information, for instance, you may want to use forms to insert new data into a data or to update an existing entry in a database table

You ~an do this in two ways

• Automatically add to or update the database with CFINSERT or CFUPDATE, A:. recommends these tags no longer be used but continues to support them for ward compatibility with earlier versions of Coldfusion.
• Manually build a SQL INSERT or UPDATE statement and use tFQlJERY to execu command.

Before diving into both these approaches, let’s consider the basics of accessing fc data. When you submit a form to a ColdFusi n template, a series of form variables created: one for each field in the form.

These form variables take the name of the fields in the original form and are aco as follows: Form. Field Name..Therefore in your employe data template if you. u the form to a ColdFusion template you could use -he following form variables in I template:

• form.LastName
• Form:Fi r’ifName
• Form.Gender
• Form.Salary

Let’s build a small example Extend the final version or out template including buttons to be submitted to another template called do form. cfm. ‘the ode is ShOW Listing 15.4

<CFPARAM NAME-‘URL,ID’ DEFAULT-‘l’>
<CFQUERY NAME-’employee’ DATASOURCE-‘EmployeeD~ta’>
SUEcr * .
,FROM Emp1oyees
WHERE ID-IURL.IO#
</CFQUERY>
.•<CFIF Employee.RecordCount greater than 0>
<CFOUTPUT QUERY-‘Employee’ MAXROWS-l>
<Hl>Employee Data: Employee #~D#</H1>
<FORM METHOD-POST AcrION-doform.cfm>
<TABLE BORDER-O CELLSPACING-S>
<TR>
</TR>
<TR>
<TO>Last Name<I.JD>
<TO><INPUT TYPE-text NAME-LastName SIZE-20 VALUE-~#LastName#’></TO>
<TO>F;rst Name</TO>
<TO><INPUT TYPE-text NAME-FirstName SIZE-20 VALUE-‘#FirstName#’></TO>
</TR>
<TR>
<TO>Gender</TO>
<TO>
<INPUT TYPE-radio NAME-Gender VALUE-‘F'<CFIF Gender ;s ‘F’> CHECKED</
CFIF»Female
<INPUT TYPE-radio NAME-Gender VALUE-‘W'<CFIF Gender is ‘W’> CHECKED</
CFIF»Male
</TO>
<fTR>
<TR>
<TO>Monthly Salary</TO>
<TO><INPUT TYPE-text ~~E-Salary SIZE-10 VALUE-‘#Salary#’></TO>
</TR>
<TR>
<TOX/TO>
<TO><INPUT TYPE-SUBMIT></TO>
</TR>
</TABLE>
</FORM>
</CFOUTPUT>
<CFELSE>
<Hl>Sorry</Hl>
The requested employee does not exist.
</CFIF>

FIGURE 15.9

FIGURE 15.9

Now you can build the template to which the form is submitted. To demonstrate how to access the information from the form fields, you will simply display the content from the form:

Form Submission</Hl>
You submitted a form with the following information:
<UL>
<CFOUTPUT>
<LI>Last Name: NForm.LastNameN
<LI>First Name: #Form.FirstNameN
<LI>Gender: #Form.Gender#
<LI>Salary: #Form.Salary#
</CFOliTPUT>
</UL>

Therefore when you submit the form you get a result that looks like Figure 15.10

FIGURE 15.10

FIGURE 15.10

Now that you have a grasp of how data from a form can be accessed in a template you willl<?Okat two approaches to moving data from a form into a database table

Using CFINSERT and CFUPDATE

In the first approach, you use CFINSERT and CFUPDATE to add and change information in the database. You learned about these tags in Chapter 10, “Using CFQUERY and SQL to Interact with the Database.” Let’s quickly review, though, the key requirements for using these tags:

• The names of the fields in the form being submitted must correspond to the names of the fields in the table being added to or updated.
• The form must contain a field corresponding to the primary key of the table being altered. This means that if you want to use your employee data for updating or inserting, you need to add a hidden field containing the employee ID. Listing 15.5 provides the code

<CFPARAM NAME-‘URL.ID’ DEFAULT-‘1’)
<CFQUERY NAME-‘Employee’ DATASOURCE-‘EmployeeData’)
SELECT *
FROM Employees
WHERE ID-#URL.ID#
</CFQUERY>
<CFIF Employee.RecordCount greater than 0>
<CFOUTPUT QUERY=’Employee’ MAXROWS=l>
<Hl>Employee Data: Employee #ID#</Hl>
<FORM METHOD-POST ACTION-doform.cfm>
<INPUT TYPE-hidden NAME-ID VALUE-#ID#>
<TABLE BORDER-O CELLSPACING-S>
<TR>
<TD>Last Name</TD>
<TD><INPUT TYPE-text NAME-LastName SIZE-20 VALUE-‘#LastName”></TD>
</TR>
<TR>
<TD>F;rst Name</TD>
<TO><INPUT TYPE-text NAME-FirstName SIZE-20 VALUE-“Fi~stName”></TD>
</TR>
<TR>
<TO>Gender</TD>
<TO>
<INPUT TYPE-radio NAME-Gender VALUE-‘F'<CFIF Gender is ‘F’> CHECKED</
CFIF»Female
<INPUT TYPE-radio NAME-Gender VALUE-‘M'<CFIF Gender is ‘M’> CHECKED</
CFIF>>Male
</TD>
</TR>
<TR>
<TD>Monthly Salary</TD>
<TD><INPUT TYPE-text NAME-Salary SIZE-lO VALUE-“SalarY”></TD>
</TR>
<TR>
<TO></TD>
<TO><INPUT TYPE-SUBMIT></TD>
</TR>
</TABLE>
</FORM>
</CFOUTPUT>
<CFELSE>
<HDSorry</Hl>
The requested employee does not exist.
</CFIF>

To fully leverage CFINSERTand CFUPDATEle,t’s put together a template that performs the following actions. The form is designed to submit its contents back to the same template that means your template will be designed to perform multiple tasks. Let’s review those tasks:
When the template is not loaded with a form submission, a blank form should be presented for inserting a new record in the database
• When an insertion form is submitted to the template, the record should be inserted and then displayed in a form for updating.
• When the template is loaded with a form submission requesting a particular record that record should be displayed in a form for updating.
• When an update form is submitted to the template the record should be updated and then displayed again in a form for updating.

For this to work, you first need to address a question: How will the template know which of these four actions to perform? You can use a URL attribute called Act; on and act in one of the following ways:

• If Act ion does not exist then your default action is to present a blank for insertion. You’ll call this action New.
• If Act; on is set to Insert. then you will use CFINSERT to create a new record based on the form submitted.
• If Act; on is set to Load then you will load the record identified by a form submission and display it for editing in a form.
• If Act; on is set to Update then you will use CFUPDATE to update a record based on the form submitted.

For all this to happen, you need to perform the following steps

1. Determine the action being requested.
2. Execute the necessary queries, updates, and insertions.
3. Set default form values.
4. Display the form

We’ll present each of these in turn.

Step 1: Determine the Action Being Requested

This is fairly simple. Using CFPARAMy ou can set the default value of Action to Newin case the parameter is not specified:

<CFPARAM NAME=’URL.Action’ DEFAULT=’New’>

This ensures that URL.Act; on always has a value so that step 2 can take place.

Step 2 Execute the Necessary Queries Updates and  nsertions

In this section you need to work through all four possible actions and you need to prepare for the following alternatives:

•Loading an existing record for editing
• Inserting a new record
• Updating an existing record

The fourth alternative displaying a form for inserting a new record doesn’t require any preparatory SQL queries or updating or inserting.

Loading an Existing Record

First you have to load existing records which is done when URL. Act; on is Load. Loading an existing record is simple: You need to use CFQUERY to select the record from the database assuming the 10 number of the record is in Form. ID (which as you will see later when you build the form is exactly where it will be). The query looks like this

<CFQUERY NAME=”TheRecord” DATASOURCE=”EmployeeData”)
SELECT *
FROM Employees
WHERE ID-#Form.ID#
</CFQUERY>

Inserting a New Record

Inserting a new record requires two steps:

1. Insert the record.
2. Recall the record in preparation for displaying it for further editing and updating.

First you insert the record using CFQUERY:

<CFQUERY NAME=’insertRecord” DATASOURCE-“EmployeeData’)
INSERT INTO Employees (LastName, FirstName, Gender, Salary)
VALUES (‘#Form.LastName#’, ‘#Form.FirstName#’, ‘#Form.Gender#’ ,#Form.Salary#)
</CFQUERY>

The syntax details for the CFQUERY tag can be found. in Chapter 10.

After you have inserted the record, you need to recall it with the following query:

<CFQUERY NAME-“TheRecord” DATSOURCE-“EmployeeData”>
SELE”CT ”
FROM
WHERE
AND
AND
AND
ORDER BY
</CFQUERY>

Of course it is entirely possible that more than one record has the same last name first name gender and salary. Therefore you have still more work to do: You need to check whether more than one record exists and, if it does, re-query based on the 10 number 0 the last record in the result (the one with the highest ID value). This is done as follows

Updating an Existing Record

Updating an existing record follows a pattern similar to that of inserting a new record:

1. Update the record.
2. Recall the record in preparation for displaying it for further editing and updating.

The logic is the same as for insertion: You change the value of URL .Act ion to ensure that the rest of the template behaves in the correct fashion.

Next you recall the record.This is easier than with insertion because you know the exact ID number of the record in question (it would have been submitted with the update form data):

<CFQUERY NAME-“TheRecord” DATASOURCE-“EmployeeData”)
SELECT *
FROM Employees
WHERE 1D-#Form.1D#
</CFQUERY>

Finally you reset URL Action

<CFSET URL.Action – “Load”)
The final result looks like this:
<!— Update the record —)
<CFQUERY NAME-‘updateRecord” DATASOURCE-“EmployeeData’)
UPDATE Employees
SET
LastName – ‘#Form.LastName#’,
FirstName – ‘#Form.FirstName#’,
Gender – ‘#Form.Gender#’,
Salary = #Form.Salary#
WHERE
ID – #Form.ID#
</CFQUERY>
<!— Recall the record —)
<CFQUERY NAME-‘TheRecord’ DATASOURCEa’EmployeeData’)
SELECT it
FROM Employees
WHERE ID&#Form.ID#
</CFQUERY>

Posted on November 17, 2015 in Forms

Share the Story

Back to Top
Share This