Adding Data with ColdFusion - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

Adding Data with ColdFusion

Now that you learned all about forms and form data validation in the previous two chapters, it's time to combine the two so as to be able to add and update database table data.

Chapter 12, "ColdFusion Forms," to learn abou275 forms and how to use them within your ColdFusion applications.

Chapter 13, "Form Data Validation," for coverage of form field validation techniques and options.

When you created the movie search forms in Chapter 12, you had to create two templates for each search. One created the user search screen that contains the search form, and the other performs the actual search using the ColdFusion <cfquery> tag. ColdFusion developers usually refer to these as the <form> and action pages, because one contains the form and the other is the file specified as the <form> action.

Breaking an operation into more than one template is typical of ColdFusion, as well as all Web-based data interaction. As explained in Chapter 1, "Introduction to ColdFusion," a browser's connection to a Web server is made and broken as necessary. An HTTP connection is made to a Web server whenever a Web page is retrieved. That connection is broken as soon as that page is retrieved. Any subsequent pages are retrieved with a new connection that is used just to retrieve that page.

There is no real way to keep a connection alive for the duration of a complete processwhen searching for data, for example. Therefore, the process must be broken up into steps, and, as shown in Chapter 12, each step is a separate template.

Adding data via your Web browser is no different. You generally need at least two templates to perform the insertion. One displays the form you use to collect the data; the other processes the data and inserts the record.

Adding data to a table involves the following steps:

Creating an Add Record Form

Forms used to add data are no different from the forms you created to search for data. As seen in Listing 14.1, the form is created using form tags, with a form control for each row table column to be inserted. Save this file as insert1.cfm (in the 14 directory under ows). You'll be able to execute the page to display the form, but don't submit it yet (you have yet to create the action page).

Listing 14.1. insert1.cfmNew Movie Form

<!---
Name:        insert1.cfm
Author:      Ben Forta (ben@forta.com)
Description: Table row insertion demo
Created:     12/21/04
--->
<!--- Get ratings --->
<cfquery datasource="ows" name="ratings">
SELECT RatingID, Rating
FROM FilmsRatings
ORDER BY RatingID
</cfquery>
<!--- Page header --->
<cfinclude template="header.cfm">
<!--- New movie form --->
<form action="insert2.cfm" method="post">
<table align="center" bgcolor="orange">
<tr>
<th colspan="2">
<font size="+1">Add a Movie</font>
</th>
</tr>
<tr>
<td>
Movie:
</td>
<td>
<input type="Text"
name="MovieTitle"
size="50"
maxlength="100">
</td>
</tr>
<tr>
  <td>
Tag line:
</td>
<td>
<input type="Text"
name="PitchText"
size="50"
maxlength="100">
</td>
</tr
<tr>
<td>
Rating:
</td>
<td>
<!--- Ratings list --->
<select name="RatingID">
<cfoutput query="ratings">
<option value="#RatingID#">#Rating#</option>
</cfoutput>
</select>
</td>
</tr>
<tr>
<td>
Summary:
</td>
<td>
<textarea name="summary"
cols="40"
rows="5"
wrap="virtual"></textarea>
</td>
</tr>
<tr>
<td>
Budget:
</td>
<td>
<input type="Text"
name="AmountBudgeted"
size="10"
maxlength="10">
</td>
</tr>
<tr>
<td>
Release Date:
</td>
<td>
<input type="Text"
name="DateInTheaters"
size="10"
maxlength="10">
</td>
 </tr>
<tr>
<td>
Image File:
</td>
<td>
<input type="Text"
name="ImageName"
size="20"
maxlength="50">
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="Insert">
</td>
</tr>
</table>
</form>
<!--- Page footer --->
<cfinclude template="footer.cfm">

Chapters 12 and 13. This form uses form techniques and validation options described in both of those chapters; refer to them if necessary.

The file insert1.cfmand indeed all the files in this chapterincludes common header and footer files (header.cfm and footer.cfm, respectively). These files contain th260 page layout code, including any logos. They are included in each file (using <cfinclude> tags) to facilitate code reuse (and to keep code listings shorter and more manageable). Chapter 9, "CFML Basics."

Listing 14.2. header.cfmMovie Form Page Header

<!---
Name:        header.cfm
Author:      Ben Forta (ben@forta.com)
Description: Page header
Created:     12/21/04
--->
&l275>
<head>
<title>Orange Whip Studios - Intranet</title>
</head>
<body>
<table align="center">
<tr>
<td>
<img src="../images/logo_c.gif"  >
</td>
<td align="center">
<font size="+2">Orange Whip Studios<br>Movie Maintenance</font>
</td>
</tr>
</table>

Listing 14.3. footer.cfmMovie Form Page Footer

<!---
Name:        footer.cfm
Author:      Ben Forta (ben@forta.com)
Description: Page footer
Created:     12/21/04
--->
</body>
</html>

The <form> action attribute specifies the name of the template to be used to process the insertion; in this case it's insert2.cfm.

Each <input> (or <cfinput>, if used) field has a field name specified in the name attribute. These names correspond to the names of the appropriate columns in the Films table.

TIP

Dreamweaver users can take advantage of the built-in drag-and-drop features when using table and column names within your code. Simply open the Database tab in the Application panel, expand the data source, and then expand the tables item to display the list of tables within the data source. You can then drag the table name into your source code. Similarly, expanding the table name displays a list of the fields within that table, and those can also be dragged into your source code.

You also specified the size and maxlength attributes in each of the text fields. size is used to specify the size of the text box within the browser window. Without the size attribute, the browser uses its default size, which varies from one browser to the next.

The size attribute does not restrict the number of characters that can be entered into the field. size="50" creates a text field that occupies the space of 50 characters, but the text scrolls within the field if you enter more than 50 characters. To restrict the number of characters that can be entered, you must use the maxlength attribute. maxlength="100" instructs the browser to allow no more than 100 characters in the field.

The size attribute primarily is used for aesthetics and the control of screen appearance. maxlength is used to ensure that only data that can be handled is entered into a field. Without maxlength, users could enter more data than would fit in a field, and that data would be truncated upon database insertion (or might even generate database errors).

NOTE

You should always use both the size and maxlength attributes for maximum control over form appearance and data entry. Without them, the browser will use its defaultsand there are no rules governing what these defaults should be.

The RatingID field is a drop-down list box populated with a <cfquery> (just as you did in the last chapter).

The Add a Movie form is shown in Figure 14.1.

Processing Additions

The next thing you need is a template to process the actual data insertionthe ACTION page mentioned earlier. In this page use the SQL INSERT statement to add the new row to the Films table.

Chapter 7, "SQL Data Manipulation," for an explanation of the INSERT statement.

As shown in Listing 14.4, the <cfquery> tag can be used to pass any SQL statementnot just SELECT statements. The SQL statement here is INSERT, which adds a row to the Films table and sets the values in seven columns to the form values passed by the browser.

Listing 14.4. insert2.cfmAdding Data with the SQL INSERT Statement

<!---
Name:        insert2.cfm
Author:      Ben Forta (ben@forta.com)
Description: Table row insertion demo
Created:     12/21/04
--->
<!--- Insert movie --->
<cfquery datasource="ows">
INSERT INTO Films(MovieTitle,
PitchText,
AmountBudgeted,
RatingID,
Summary,
ImageName,
DateInTheaters)
VALUES('#Trim(FORM.MovieTitle)#',
'#Trim(FORM.PitchText)#',
#FORM.AmountBudgeted#,
#FORM.RatingID#,
'#Trim(FORM.Summary)#',
'#Trim(FORM.ImageName)#',
#CreateODBCDate(FORM.DateInTheaters)#)
</cfquery>
<!--- Page header --->
<cfinclude template="header.cfm">
<!--- Feedback --->
<cfoutput>
<h2>New movie '#FORM.MovieTitle#' added</h2>
</cfoutput>
<!--- Page footer --->
<cfinclude template="footer.cfm">

Listing 14.4 is pretty self-explanatory. The <cfquery> tag performs the actual INSERT operation. The list of columns into which values are to be assigned is specified, as is the matching VALUES list (these two lists must match exactly, both the columns and their order).

Each of the values used is from a FORM field, but some differences do exist in how the fields are used:

• All string fields have their values enclosed within single quotation marks.

• The two numeric fields (AmountBudgeted and RatingID) have no single quotation marks around them.

• The date field (DateInTheaters) is formatted as a date using the CreateODBCDate() function.

It's important to remember that SQL is not typeless, so it's your job to use quotation marks where necessary to explicitly type variables.

TIP

ColdFusion is very good at handling dates, and can correctly process dates in all sorts of formats. But occasionally a date may be specified in a format that ColdFusion can't parse properly. In that case, it will be your responsibility to format the date so ColdFusion understands it. You can do this using the DateFormat() function or the ODBC date function CreateODBCDate() (or the CreateODBCTime() and CreateODBCDateTime() functions). Even though ColdFusion uses JDBC database drivers, the ODBC format generated by the ODBC functions is understood by ColdFusion and will be processed correctly. Listing 14.4 demonstrates the use of the CreateODBCDate() function.

NOTE

Notice that the <cfquery> in Listing 14.4 has no name attribute. name is an optional attribute and is necessary only if you need to manipulate the data returned by <cfquery>. Because the operation here is an INSERT, no data is returned; the name attribute is therefore unnecessary.

Save Listing 14.4 as insert2.cfm, and then try submitting a new movie using the form in insert1.cfm. You should see a screen similar to the one shown in Figure 14.2.