Building the company add action template

an article added by: Mark Dresher at 03222008


In: Categories » Computers and technology » Programming » Building the company add action template

In this article, you create the template that puts your form data into the database. Create a file named CompanyAddAction.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.

<cfquery name=”InsertCompany”
 datasource=”#Request.MainDSN#”>
INSERT INTO Company(
   CompanyName,
   Address,
   City,
   State,
   ZipCode,
   Comments
   )
   VALUES (
   ‘#Trim(Form.CompanyName)#’,
   ‘#Trim(Form.Address)#’,
   ‘#Trim(Form.City)#’,
   ‘#Trim(Form.State)#’,
   ‘#Trim(Form.ZipCode)#’,
   <cfif Len(Trim(Form.Comments)) GT
   0>’#Trim(Form.Comments)#’<cfelse>NULL</cfif>
   /cfquery>
   <cflocation url=”Finished.cfm?msg=#URLEncodedFormat(‘#Form.CompanyName#
 has been added to the database.’)#”>

CompanyAddAction.cfm inserts the new company into the database (the CFQUERY call) and then redirects the user to a page that tells him what just happened (the CFLOCATION call). Compared to the other two templates that you’ve already created, Finished.cfm is much simpler because it just displays the contents of a variable—in this case, the message that tells the user that he successfully inserted a company into the database. Create a file named Finished.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.

<cfparam name=”URL.msg” default=”This page did not receive a message.”>
   <html>
   <head>
   <title>ColdFusion MX Bible</title>
   <link rel=”stylesheet” href=”styles.css”>
   </head>
   <body>
   <h1>Action Finished</h1>
 <cfoutput>#URL.msg#</cfoutput>
</body>
 </html>

Only two ColdFusion tags are in this file: CFPARAM and CFOUTPUT. The other tags are HTML used for visual layout. CFPARAM makes sure that a variable exists before attempting to use it. CFPARAM uses two attributes: Name and Default. Name is the variable that you want to ensure exists. If the variable doesn’t exist, CFPARAM creates it and assigns it the value specified by Default. If the variable does already exist, then ColdFusion uses its existing value. CFOUTPUT tells ColdFusion to replace anything between pound signs (such as the #URL.msg# in our example) with the value of the expression—in this case, the message contained in URL.msg. Now that you’ve created all three templates in this process, you can take them for a spin! Point your Web browser to http://<yourserver>/CFMXBible/Ch02/CompanyAddForm.cfm. Fill in the form with data about a fictitious company and then click the Submit button. If everything works correctly, you see the Finished page, and it tells you that your company was added to the database. Congratulations! You’ve finished the first part of your first ColdFusion application! Add a few more companies to the database before proceeding to the next part. Now take a look at what happened in the company add form, company add action, and finished pages. After you pointed your Web browser to http://<yourserver>/CFMXBible/Ch02/CompanyAddForm.cfm, your browser requested that template, which ColdFusion executed and sent back to your browser. Point your browser back to that same address and choose View➪Source from your browser’s menu bar. The HTML that you see is what ColdFusion sent back to your browser after executing the ColdFusion code. If you compare CompanyAddForm.cfm in HomeSite to the HTML that you get from the View➪Source command, you see that they are different. ColdFusion processes a CFFORM tag into an HTML FORM tag plus a library of JavaScript validation functions. Similarly, ColdFusion processes the CFINPUT tags into HTML INPUT tags that make calls to these JavaScript validation functions. Creating and calling this validation logic manually is difficult and error-prone, so using CFFORM and CFINPUT is a simple way to make the validation logic work correctly with a minimum of fuss. HTML INPUT tags have Size and Maxlength attributes. Size specifies how many characters (on average) are visible in the input field whenever they’re displayed in the browser, and Maxlength specifies the maximum number of characters that can go in the input field. CFINPUT tags can define more than sizes and maximum lengths, as follows:

If Required = Yes and the field does not contain any text when the user submits the form, the user receives a JavaScript warning message and the form is not submitted.

Validate tells CFINPUT what data format the field can accept.

If the Required or Validate tests fail, the user sees the Message in a JavaScript alert. Make sure that you make the message descriptive of which field failed. Try submitting the form without entering anything into the required form fields. The page does not submit; instead, it warns you that you must enter a value into the field. You must have JavaScript enabled in your browser for CFFORM’s validation functionality to work correctly.

Now look at CompanyAddAction.cfm, which took the company data from CompanyAddForm.cfm and created a new company record in the database. The CFQUERY call tells the database to insert a new company into the database. The SQL statement in the CFQUERY has two clauses: INSERT INTO, which tells the database which table and columns will receive the data, and VALUES, which passes the company data to those columns. CFQUERY has two attributes: Name and Datasource. Name tells ColdFusion what to name this query, and Datasource tells the ColdFusion how to connect to the database. Notice in the CFQUERY call that the Datasource is #Request.MainDSN#—the variable that you set in Application.cfm. Application.cfm runs before every request made by your application, so Request.MainDSN is already defined when ColdFusion processes this CFQUERY tag. Notice that every variable in the CFQUERY looks as follows:

‘#Trim(Form.CompanyName)#’

The single quotes treat the data that you submit as a string rather than as a number, which doesn’t use single quotes around its value. Inside the single quotes, the expression is surrounded by pound signs, which tell ColdFusion to evaluate the expression. Trim() takes whatever value was passed in to the function and returns that value with all leading and trailing whites pace (spaces, tabs, carriage returns, and so on) removed. Always use Trim() around string data before putting it into a database. In this case, you’re trimming the variable Form.CompanyName. Form. tells ColdFusion to get the variable from the form that was submitted to this template, and CompanyName tells ColdFusion the variable (form field) to get. The first five variables in CompanyAddAction.cfm come from required fields on CompanyAddForm.cfm. The sixth variable, Form.Comments, however, was not required. What should CompanyAddAction.cfm put into the database if the user didn’t enter any comments? The template could put an empty string into the database, but a NULL value would be more appropriate. A NULL is a special value that means “indeterminate” or “undefined.” You want to use NULL for the Comments field because the Comments weren’t empty—they were not entered. To put a NULL value into the database, use NULL instead of ‘#Trim(Form.Comments)#’ in CFQUERY, as follows:

<cfif Len(Trim(Form.Comments)) GT 0>
   ‘#Trim(Form.Comments)#’
   <cfelse>
   NULL
   </cfif>

You build this portion of the SQL statement on-the-fly by using a simple CFIF construct. First, you use two ColdFusion functions, Len() and Trim(), to determine whether the user entered anything into the Comments field. If the length of Form.Comments without any surrounding white space is greater than zero, you can put Form.Comments directly into the database by using the syntax that you’ve already learned in “Building the company add action template” earlier in this article. If the trimmed length of Form.Comments is zero, however, put NULL into the database. Notice the difference between the two syntaxes: If you’re putting Form.Comments into the database, you use single quotes, pound signs, and a call to Trim(). If you’re inserting a NULL value, however, you use NULL by itself without single quotes or pound signs. NULL is not a string; it is a special enumerated constant in SQL that represents a NULL value. CompanyAddAction.cfm redirects the user to a different template after inserting a company into the database. If the user clicks the Refresh button after he sees the success message on Finished.cfm, ColdFusion executes Finished.cfm a second time, but the database is untouched because Finished.cfm doesn’t make any database calls. Consider what would happen if CompanyAddAction.cfm displayed the success message directly instead of redirecting the user. If the user clicked the Refresh button, ColdFusion would execute CompanyAddAction.cfm again, which would resubmit the form data and insert the company into the database again. CFLOCATION stops processing the current page and redirects the user to the location specified in its URL attribute (Finished.cfm). Following is the CFLOCATION call in CompanyAddAction.cfm:

<cflocation url=”Finished.cfm?msg=#URLEncodedFormat(
   ‘#Form.CompanyName# has been added to the database.’)#”>

The URL to which the user is redirected is Finished.cfm. The question mark tells ColdFusion that everything following it is a parameter or a collection of parameters. Take, for example, the following URL parameter:

msg=#URLEncodedFormat(‘#Form.CompanyName# has been added to the
   database.’)#

It is interpreted as follows: The parameter’s name is msg, meaning that Finished.cfm receives a variable named URL.msg. The parameter’s value is the result of the call to URLEncodedFormat(). URLEncodedFormat() is a ColdFusion function that takes a string and returns the string with all special characters escaped for safe use in the URL. (Escaping a string means that all characters other than letters or numbers are replaced with a special code sequence—for example, a space character becomes %20.) After Finished.cfm receives URL.msg, ColdFusion converts the escaped string back into plain text so that Finished.cfm can display it. Now to build a company list. This short exercise requires you to build a single template. Create a file named CompanyList.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file.

<cfquery name=”GetCompanies”
   datasource=”#Request.MainDSN#”>
   SELECT
   CompanyID,
   CompanyName,
   Address,
   City,
   State,
   ZipCode,
   Comments
   FROM
   Company
   ORDER BY
   CompanyName ASC
   </cfquery>
   <html>
   <head>
   <title>ColdFusion MX Bible</title>
   <link rel=”stylesheet” href=”styles.css”>
   </head>
   <body>
   <h1>Company List</h1>
   <table>
   <tr>
   <td><b>ID</b></td>
   <td><b>Name</b></td>
   <td><b>Address</b></td>
   <td><b>City</b></td>
   <td><b>State</b></td>
   <td><b>ZIP Code</b></td>
   </tr>
   <cfoutput query=”GetCompanies”>
   <tr>
   <td>#CompanyID#</td>
 <td>#CompanyName#</td>
<td>#Address#</td>
   <td>#City#</td>
   <td>#State#</td>
   <td>#ZipCode#</td>
   </tr>
   </cfoutput>
   </table>
   </body>
 </html>

Point Your web browser to http://<yourserver>/CFMXBible/Ch02/CompanyList.cfm. You should see a list of the companies that you’ve added to the database, sorted by the company name. Just two ColdFusion tags are at work on this page, and you’ve seen both of them already. These two tags behave differently, however, in CompanyList.cfm than they did in CompanyAddAction.cfm and Finished.cfm, as the following list describes:

In CompanyAddAction.cfm, CFQUERY told the database to INSERT a new company. In CompanyList.cfm, CFQUERY selects information from the database.

In Finished.cfm, CFOUTPUT evaluated and output a single variable (the URL.msg parameter). In CompanyList.cfm, CFOUTPUT outputs the results of a database query.

legal notice

Our website is not responsible for the information contained by this article. Web-articles is a free articles resource.
Suggestion: If you need fresh, daily updated content for your website, feel free to use our service. Click here for more information.

Useful tools and features

Link to this article from your page    Send this article to you or to a friend
If you like this article (tutorial), please link to it from your web page using the information above.

related articles

1. Adding a New Employee to the Database
The CFQUERY in CompanyDeleteAction.cfm uses a DELETE statement with two SQL clauses: DELETE and WHERE. DELETE tells the database the table from which to delete a record, and WHERE tells the database which record to delete. You can watch the company delete process in action. Point your Web browser to http://<yourserver>/CFMXBible/Ch02/CompanyGetDeleteForm.cfm and enter a CompanyID. (To get a valid ID, go to the company list and pick a number from the ID column.) Click Submit to see the chosen company’s informatio...

2. Modifying an Employee in the Database
The DateFormat() function around the DateOfBirth column in the codereturns the employee’s birth date reformatted according to a display mask. DateOfBirth normally comes back from the database in the following format: 2002-01-01 00:00:00.0 That format is not very user-friendly. Calling DateFormat() with a mask of “mm/dd/yyyy” returns the date as follows: 01/01/2002 This version is, of course, more natural and easy to read. The same is true for the employee edit process...

3. Removing an Employee From the Database
The user must have the capability to remove employees from the database. The employee delete process is a simple combination of techniques that you have already learned, such as retrieving a record from the database, displaying that record in a template, and so on. The first page in this process is nearly identical to the employee get edit form. Create a file named EmployeeGetDeleteForm.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file. <html> <head> <ti...

4. Making direct links to the forms
Say that you want to modify or delete a company. Right now, you need to remember the company’s ID, go back to the launch pad, click Company Edit, and enter the company ID, all just to get to the edit form. Wouldn’t you rather click a company in the list and go directly to the edit form? ... <table> <tr> <td><b>ID</b></td> <td><b>Name</b></td> <td><b>Address</b></td> <td><b>City</b></t...

5. Using a Custom Tag
Custom tags are reusable, developer-authored extensions to the ColdFusion language. The custom tag that you create in the following parts displays today’s date in a familiar format. Create a file named TodaysDate.cfm inside the Ch02 directory, type the code into the file’s editing window, and save the file. <cfoutput>#DateFormat(Now(), “ddd, mmm d, yyyy”)#</cfoutput> Now() returns the current date and time, and DateFormat() reformats the date. To call your custom tag, open index.cfm, a...

6. Creating your physical data model
Physical data models are extracted from their logical counterparts by mapping logical objects to physical objects supported by the target database platform. The following table shows the relationship between logical and physical objects. In many cases, your physical data model almost mirrors your logical data model because entities typically map directly to the tables that store them. Exceptions to this rule are logical data models that contain nonspecific relationships and entity subtypes. Although entity subtypes are an advanced top...

7. Understanding All Relational Result Sets
This article can help you better understand complicated SQL containing multitable joins, group-related clauses, and aggregate functions—by far the most problematic topics for most database developers. You also learn the correct way to handle database exceptions and incorporate them as actual functionality in your ColdFusion application. You learn, too, how to increase performance by caching queries in memory for fast access. You can memorize SQL clauses and Bachus-Naur forms until you’re blue in the face, ...

8. How and when to use Group by Having and Distinct
After learning how SQL works internally, you can better learn how to put the more complicated aspects of it to use. First we need to clarify the difference between GROUP BY and DISTINCT. Quite a bit of confusion exists over these two clauses, because they often produce the same query results but for very different reasons. In fact, GROUP BY and DISTINCT are not related in any way whatsoever. The GROUP BY clause produces as its intermediate work product a table of rows representing each group, and a sort of “invisible third dimen...

9. Query Caching
Query caching places the results of a database query into ColdFusion Server’s memory where it can be instantly retrieved without querying the database. Query caching is one of the most useful tools for enhancing performance in your ColdFusion applications; unfortunately, it is also one of the least used. A few caveats are involved in query caching, but all in all, it is a very straightforward technique to employ. You should consider caching only queries that fit the following criteria: The query has a significant ...

10. Views
It doesn't matter how many tables a relational join traverses, regardless of whether it contains a GROUP BY clause (or anything else for that matter), all query result sets manifest themselves as one or more rows that contain an identical collection of one or more columns. So, in a way, query results are virtual tables based on underlying physical tables of data. Now imagine if you could take a query statement and define it as a formal database object that could be accessed just as a table can. Well, you can—that is ...