Building Web Front-Ends

This chapter describes how to build application pages that enable users to add and update information stored in databases. During chapter practices, you will apply what you learn as you create a Web front-end for a back-end database.

Contents

Understanding Web Front-Ends 108
Adding Data 108
Validating Data 108
Inserting Data 111
Updating Data 113
Passing URL Variables 114
Creating Update Action Page
Development Considerations 120

Understanding Web Front-Ends

In addition to providing search interfaces, many Web applications also provide a front- end so that users can insert and update data in a database.

Web front-ends include forms that accepts user input and action pages that use that input in a query to either add or update a record in a database table.

Adding Data

Building pages to add data is very much like building a search interface but there are some additional considerations:

Database tables usually store required information and your users

will receive errors if they don’t enter that information.
Database fields may require specific data types and your users will

receive errors if they don’t enter data in the appropriate format.
You insert rather than select data during the action page query. To

accommodate these considerations, a Web front-end must:
Validate form field data so that the form variables will not pass to

the action page with inappropriate data.
Use the SQL INSERT statement within the action page query so that

INSERT instructions are passed to the database.

Validating Data

Validate each form field by:

Applying a validation rule that describes what data or data type to

test for.
Naming a procedure to perform if the validation fails.

You can validate input on either the client by adding JavaScript to the form page or the server using HTML and CFML. When you validate on the client, form fields are validated as you fill them in. When you validate on the server, form fields are validated after the form is submitted.

During this chapter, you will perform ColdFusion server-side form field validation using the hidden HTML input form control.

Hidden form control syntax

Add one hidden form control for each form field rule that you want

to validate.
The NAME attribute defines the name of the form field to validate

and the validation rule that ColdFusion should apply.
The VALUE attribute describes the message to send back to users when

validation fails.

ColdFusion validation rules

ColdFusion provides a variety of validation rules, for example:

_Float validates a numeric type.
_Date validates a date type .
_Required validates that a value has been entered.

Note It’s good programming practices to group all hidden form controls at the top of the page.

Validation usage examples

This code validates that a user entered data in the LastName form field:

This code validates that a user entered a numeric value in the Salary form field:

This code validates that a user entered a date value in the StartDate form field:

To validate form data:

Open SearchForm.cfm in HomeSite.
Save the page as InsertForm.cfm.
Title the page Chapter 10- Add Employee Form.
Edit the opening FORM tag’s ACTION attribute so that data will pass

to

InsertAction.cfm.

You will create InsertAction.cfm later in this procedure.

Modify the form page heading:

<H4>Employee Add Form</H4>

Modify the GetDepartments query so that it also retrieves Department_ID fields:

<CFQUERY NAME="GetDepartments" DATASOURCE="HRExpress"> SELECT Department_Name, Department_ID

FROM Departments

</CFQUERY>

Position your cursor directly after the opening FORM tag.
Add a hidden form control that assigns required to the FirstName

field and displays a message to the user if no data was entered for that form control:

Add a hidden form control that assigns required to the LastName
field and displays a message to the user if no data was entered for that form control:

Add a hidden form control that assigns required to the StartDate
field and displays a message to the user if no data was entered for that form control:

Add a hidden form control that validates a date format for the
StartDate field and displays a message to the user if they enter data in the wrong format:

Add a hidden form control that assigns required to the Salary field
and displays a message to the user if they don’t enter data:

Add a hidden form control that validates a float format for the
Salary field and displays a message to the user if they enter data in the wrong format:

Add a text control so that a user can enter an Employee’s FirstName:

<B>EMPLOYEE FIRST NAME</B><BR>

Change the select box NAME attribute from Department_Name to
Department_ID:

Delete the opening and closing tags for the OPTION VALUE="ALL" block

.
Modify the select box’s remaining OPTION tag to reference the

Department_ID column instead of Department_Name:

<OPTION VALUE="#Department_ID#"> #Department_Name#

</OPTION>

Add a text input control after the Department select box so that a
user can enter an Employee's start date:

<B>Employee Start Date(mm/dd/yy)</B><BR>

Add a text input control after the Employee Start Date field so that
a user can enter a new employee's salary:

<B>Employee Salary (75000)</B><BR>

Edit the Submit control’s VALUE attribute:

Save the page.
Create a new page.
Save it as InsertAction.cfm.

You will need to have this page created in order to test form validation. You will add the code needed for inserting data in the next procedure.

View the InsertForm.cfm in a browser.
Test the validation by submitting the form leaving different fields

empty.

http://localhost/CFDOCS/exampleapp/TutorialSolutions/Chapter10/ InsertForm.cfmClick here/a to see the how the form should look and validate.

http://localhost/CFDOCS/exampleapp/TutorialSolutions/Chapter10/ InsertForm.txtClick here/a to see InsertForm.cfm’s code.

Move on to the next procedure to build the action page that inserts the data into the database.

Note Almost all links will work at this time. You will learn how to define a checkbox value for the action page query during the next procedure.

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

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:

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:

Open InsertAction.cfm in HomeSite.
Title the page Chapter 10- ADD EMPLOYEE ACTION PAGE.
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>

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

Begin a query named InsertEmployee:

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#’)

End the query:

</CFQUERY>

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

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

http://localhost/CFDOCS/exampleapp/TutorialSolutions/Chapter10/ InsertForm.cfmClick here/a to see how the form and action page should work.

http://localhost/CFDOCS/exampleapp/TutorialSolutions/Chapter10/ InsertAction.txtClick here/a 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.

Updating Data

Updating data in a database requires that you build a Web front-end that:

Provides the user with a way to choose which record to update. Do
this by building a list of hyperlink choices.

You should already know how to use the anchor tag.

Passes the user’s selection value to a form page at the end of a
URL.

Do this by defining a URL variable in each anchor tag on the listing page. You’ll learn how to do this here.

Uses that URL variable to retrieve existing database information
that's

associated with a specific database record. Do this by generating dynamic SQL.

You learned how to do this in Chapter 5.

Prefills form fields with the record data and displays it back to

the user. You learned how to do this in Chapter 7.
Allows the user to update record information on the form. Like any

form, your users should be able to input data.
Passes the updated record information to the database. Do this using

an UPDATE statement in a query.

You’ll learn how to do this here.

Confirms that changes are made.

Do this by redirecting the user to a page where they can confirm the changes. You learned how to do this in Chapter 8.

Passing URL Variables

Pass the user’s selection to a new page within an HTML anchor tag. When you do this:

The value of the user’s selection is added to the end of the URL.
The selection value is referred to as a URL variable.
You can reference URL variables on the action page as you would any

other variables.

When working with URL variables:

Prefix URL variable’s with URL.
A URL variable’s scope is the page that it passes to.

Passing URL syntax

<A HREF="URL?URLVariable">LinkName</A>

Use a question mark to separate the URL variable from the URL
address.

Passing URL variable examples

To create a hyperlink list that allows users to select an employee to update, you would enter this code:

<A HREF="UpdateForm.cfm?Employee_id=18">Jeremy Allaire</A>

<A HREF="UpdateForm.cfm?Employee_id=19">John Allaire</A>

<A HREF="UpdateForm.cfm?Employee_id=20">Marcello Fabiano</A>

The UpdateForm.cfm page appears in the browser when the user clicks

on an employee name.
#URL.Employee_IDN# passes to that page.
Once passed, you can reference the URL variable in SQL statements.

To generate a hyperlink list so that users can select an employee to update, you enter this code on the listing page:

SELECT FirstName, LastName, Employee_ID FROM Employees

</CFQUERY>

<A HREF="UpdateForm.cfm?Employee_ID=#GetEmployees.Employee_ID#"> #GetEmployees.FirstName# #GetEmployees.LastName#</A><BR>

</CFOUTPUT>

The UpdateForm.cfm page appears in the browser when the user clicks

on an employee name.
#URL.Employee_ID# passes to that page.
Once passed, you can reference the URL variable to generate SQL.

Using URL variables examples

This update form’s query code retrieves database information by referencing the URL variable passed from the hyperlink listing page:

<CFQUERY NAME="GetEmployeeDetails" DATASOURCE="HRApp"> SELECT FirstName, LastName,

Department_ID, StartDate, Salary, Contract

FROM Employees

WHERE Employee_ID = #URL.Employee_ID#

</CFQUERY>

Prefix URL variables when referencing them in a SQL statement.
Once retrieved, you can use the data to populate the fields on the

form.
Each form control requires slightly different coding.

This code prefills the employee first name field using the VALUE attribute and dataset values:

<INPUT TYPE="Text" NAME="FirstName" SIZE="20" MAXLENGTH="50"

VALUE="<CFOUTPUT>#GetEmployeeDetails.FirstName#</CFOUTPUT>">

Surround the variable with pound signs within a CFOUTPUT block tag.
Place the CFOUTPUT tag inside the input tag.

This code prefills the department select box using conditional logic statements inside the OPTION tag:

Department<BR>

<OPTION VALUE="#Department_ID#"

<CFIF GetEmployeeDetails.Department_ID IS GetDepartments.Department_ID> SELECTED

</CFIF>>

#Department_Name#

</OPTION>

</CFOUTPUT>

</SELECT>

When a field value matches the employee’s department, that
department populates the selectbox.

This code prefills the contract checkbox using conditional logic inside the INPUT tag:

<INPUT TYPE="Checkbox" NAME="Contract" VALUE="Yes"

<CFIF GetEmployeeDetails.Contract IS "Yes">CHECKED

</CFIF>>Yes

If contract is true, the checkbox is enabled.

To build the hyperlink list to pass URL parameters:

Create a new application page in HomeSite.
Save the page as UpdateList.cfm.
Title the page Chapter 10 - Employee Update List.
Create a page heading after the opening BODY tag:

<H4>Employee Update Listing</H4>

Query the Employees table and retrieve FirstName, LastName, and
Employee_ID:

<CFQUERY NAME="GetEmployees" DATASOURCE="HRExpress"> SELECT FirstName, LastName, Employee_ID

FROM Employees

</CFQUERY>

Add another heading to instruct the user to select an employee:

<H4>Select an Employee to Update</H4>

Create a CFOUTPUT block that references the GetEmployees query:

</CFOUTPUT>

Insert an anchor tag within the CFOUTPUT block so that
GetEmployees.FirstName and GetEmployees.LastName will appear as a hyperlink for each employee:

<A HREF=>#GetEmployees.FirstName# #GetEmployees.LastName#</A>

</CFOUTPUT>

Assign an HREF attribute to the anchor tag so that when a user
selects an employee, the hyperlink passes the Employee_ID variable to the UpdateForm.cfm page:

Format the hyperlink list by adding a BR tag after the anchor tag.
Save the page.

http://localhost/CFDOCS/exampleapp/TutorialSolutions/Chapter10/ UpdateList.txtClick here/a to see UpdateList.cfm’s code.

Move on to the next procedure and create the update form.

To create the update form:

Open InsertForm.cfm in HomeSite.
Save the form as UpdateForm.cfm.
Title the page Chapter 10 - Update Employee Form.
Change the page heading to Update Employee Form:

<H4>Update Employee Form</H4>

After the opening BODY tag, create a query named GetEmployeeDetails
to retrieve data from the Employees table based on the URL parameter that will pass from the UpdateList.cfm page:

<CFQUERY NAME="GetEmployeeDetails" DATASOURCE="HRExpress"> SELECT FirstName, LastName,

Department_ID, StartDate, Salary,Contract FROM Employees

WHERE Employee_ID=#URL.Employee_ID#

</CFQUERY >

Change the FORM tag ACTION attribute to UpdateAction.cfm:

Add a hidden form control to pass the URL.Employee_ID to
UpdateForm.cfm:

<INPUT TYPE="HIDDEN" NAME="Employee_ID" VALUE="<CFOUTPUT> #URL.Employee_ID# </CFOUTPUT>">

Rename the submit button Update Employee:

Save the form.

Remain in the form and move on to the next procedure to prefill update form fields.

To prefill update form fields:

Modify the FirstName and LastName fields to populate them with
GetEmployeesDetails query data:

<INPUT TYPE="Text" NAME="FirstName" SIZE="20" MAXLENGTH="50"

VALUE="<CFOUTPUT>#GetEmployeeDetails.FirstName# </CFOUTPUT>">

<INPUT TYPE="Text" NAME="LastName" SIZE="20" MAXLENGTH="50"

VALUE="<CFOUTPUT>#GetEmployeeDetails.LastName# </CFOUTPUT>">

Modify the select box so that the selected Department_Name is based
on the GetEmployeeDetails query:

<OPTION VALUE="#Department_ID#"

<CFIF GetEmployeeDetails.Department_ID IS

GetDepartments.Department_ID> SELECTED

</CFIF>>

#Department_Name#

</OPTION>

</CFOUTPUT>

</SELECT>

Modify the StartDate and Salary fields to populate them with
GetEmployeeDetails query data:

<INPUT TYPE="Text" NAME="StartDate" SIZE="16" MAXLENGTH="16" VALUE="

<CFOUTPUT>#GetEmployeeDetails.StartDate#</CFOUTPUT>">

<INPUT TYPE="Text" NAME="Salary" SIZE="10" MAXLENGTH="10" VALUE="

<CFOUTPUT>#GetEmployeeDetails.Salary#</CFOUTPUT>">

Enable the checkbox based on GetEmployeeDetails query data:

<INPUT TYPE="Checkbox" NAME="Contract" VALUE="Yes" <CFIF GetEmployeeDetails.Contract IS "Yes">CHECKED

</CFIF>>

Save the page.

Move on to the next procedure to create an update action page.

Creating Update Action Page

The action page that you build to update data is very similar to the action page that you build to insert data. The update action page needs to include:

Conditional logic to check for the existence of checkbox and radio

button values to ensure you have all the values that you need for the update later on the page.
A query to perform the actual data update.
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 update 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 UPDATE SQL statement, and SET and WHERE clauses to describe:

The table that you want to update
Each table column by a variable that will set the update value
The record to update

SQL UPDATE syntax

<CFQUERY NAME="QueryName" DATASOURCE="DataSourceName"> UPDATE Table

SET

ColumnName=’UpdateValue’, ColumnName=’UpdateValue’,

...

WHERE PrimaryKey=UpdateValue

</CFQUERY>

The UPDATE statement describes the table to update.
The SET clause describes the values to update.
Separate multiple UpdateValue entries with commas (,).
Surround an UpdateValue with single quotes(’) when it is a string

value.
Do not surround an UpdateValue with single quotes when it is a

numeric value.
Use the WHERE clause so that the statement updates one record rather

than all records.
There is a one-to-one correspondence among columns and

UpdateValue(s).

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.

Updating data usage example

This code updates a particular employee in the Employees table:

UPDATE Employees SET

FirstName=’#Form.FirstName#’, LastName=’#Form.LastName#’, Contract=’#Form.Contract#’ WHERE

Employee_ID=#Form.Employee_ID#

</CFQUERY>

The update values are variables passed from a form page.
There is a one-to-one correspondence between the columns and the

values named in the UPDATE statement.

To build an update action page:

Create a new application page in HomeSite.
Save the page as UpdateAction.cfm.
Title the page Chapter 10 - Update Employee Action Page.
Set a local variable to yes if the Form.Contract variable was passed

from

UpdateForm.cfm and NO if it was not passed:

</CFIF>

Create a query named UpdateEmployee to update data in the HRExpress
database:

</CFQUERY>

Within the CFQUERY block, create a SQL statement to update data in
the Employees table using the information passed from UpdateForm.cfm:

UPDATE Employees

SET FirstName = ’#Form.FirstName#’,LastName = ’#Form.LastName#’, Department_ID = #Form.Department_ID#,

StartDate = #Form.StartDate#, Salary = #Form.Salary#, Contract = ’#ContractStatus#’

WHERE Employee_ID = #Form.Employee_ID#

After the CFQUERY block, redirect users to EmpList.cfm.

Save the page.
View UpdateList.cfm in a browser.
Select an employee to update.
Change information and submit the form.

UpdateAction.cfm updates the information in the database and redirects users to EmpList.cfm so that they can confirm the changes.

You have completed the HR Manager Application. Move on to the chapter summary.

Development Considerations

During this chapter, you learned about:

The characteristics of Web front-ends
Building insert forms
The characteristics of inserting data into databases
Building update listing, forms, and action pages
The characteristics of updating data in databases

Where to go from here

Move on to the next chapter to learn about the sample application.
Move on to the next section of this guide to learn about

administering your ColdFusion Server.

C HA PT ER 11

Building Web Front-Ends

Building Web Front-Ends

Understanding Web Front-Ends

Adding Data

Validating Data

Hidden form control syntax

Validation usage examples

To validate form data:

Title the page Chapter 10- Add Employee Form.

Modify the GetDepartments query so that it also retrieves Department_ID fields:

Position your cursor directly after the opening FORM tag.

Inserting Data

Building a query to insert data

SQL INSERT syntax

To build an insert action page:

Updating Data

Passing URL Variables

Passing URL syntax

Passing URL variable examples

Using URL variables examples

To build the hyperlink list to pass URL parameters:

To create the update form:

To prefill update form fields:

Creating Update Action Page

Building a query to update data

SQL UPDATE syntax

To build an update action page:

Development Considerations