Platinum Edition Using HTML 4, XML, and Java 1.2:Using Cold Fusion

To access the contents, click the chapter and section titles.

Platinum Edition Using HTML 4, XML, and Java 1.2
(Publisher: Macmillan Computer Publishing)
Author(s): Eric Ladd
ISBN: 078971759x
Publication Date: 11/01/98

Table of Contents

NOTE: A query’s RecordCount property contains the number of records found when performing the query. You can access the value by referencing query_name.RecordCount, but be sure to enclose it in pound signs and <CFOUTPUT> tags if you want to write its value into your resulting HTML document.

This short template file should begin to give you an inkling of ColdFusion’s power. In just a few lines of code, you have taken the form input, used it to dynamically build a SQL statement, passed the SQL statement to a database engine, retrieved the query results, and generated an HTML table that presents the results in a readable format. If you’ve ever coded any CGI programs in a language such as Perl, think about how much code you would need to replicate the same functionality and compare it to Listing 34.1. You’ll very likely conclude that ColdFusion wins handily in terms of power and efficiency.

NOTE: Always use METHOD=“POST” with a form that uses a ColdFusion template as its ACTION attribute. Otherwise, the form data will be passed on the URL, in which case you would have to reference the URL object to get the submitted values.

Inserting Records into a Database Another common database operation is to insert a new record into a database table. Suppose, for example, you were gathering subscription information for an email-based newsletter. You would need to collect the subscriber’s name and email address so that you could send the newsletter whenever it is published. In support of this, you could write a form similar to the following:

<FORM ACTION=“subscribe.cfm” METHOD=“POST”>
<B>Name: </B>
<INPUT TYPE=“TEXT” NAME=“subscriber_name” SIZE=20>
<P>
<B>E-mail: </B>
<INPUT TYPE=“TEXT” NAME=“subscriber_email” SIZE=20>
<P>
<INPUT TYPE=“SUBMIT” VALUE=“Sign me up!”>
</FORM>

After you write the form, next you need to write the ColdFusion template named subscribe.cfm. The important thing for this template to do would be to insert the name and email address information into the database, but it would also be nice if the user received some kind of confirmation message to let them know that the subscription has been entered.

To handle the insertion of the new record, you can use the following code:

<CFQUERY DATASOURCE=“newsletter” NAME=“NewSubscriber”>
INSERT
INTO Subscribers (name,email)
VALUES (‘#Form.subscriber_name#’,‘#Form.subscriber_email#’)
</CFQUERY>

Note in the VALUES specification that you reference the subscriber_name and subscriber_email fields of the submitted form, and that by enclosing them in pound signs, you instruct ColdFusion to substitute the values passed in those fields. Thus, the resulting SQL statement for a user named Mabel Anderson with email address manderson@isp.net would look like

INSERT
INTO Subscribers (name,email)
VALUES (‘Mabel Anderson’,’manderson@isp.net’)

You can round out the template with some kind of message back to the user that repeats her information and confirms the insertion of her record into the database. Listing 34.2 shows one possible way of accomplishing this.

Listing 34.2 subscribe.cfm Code Listing

<CFQUERY DATASOURCE=“newsletter” NAME=“NewSubscriber”>
INSERT
INTO Subscribers (name,email)
VALUES (‘#Form.subscriber_name#’,’#Form.subscriber_email#’)
</CFQUERY>

<HTML>

<HEAD>
<TITLE>Subscription Confirmation</TITLE>
</HEAD>

<BODY BGCOLOR=“WHITE”>

<H1>Thank you, <CFOUTPUT>#Form.subscriber_name#</CFOUTPUT></H1>

Your subscription has been entered and will be sent to you each
month at <CFOUTPUT>#Form.subscriber_email#</CFOUTPUT>.

</BODY>

</HTML>

If you’re not that comfortable with SQL INSERT statements, ColdFusion can relieve you of some of the burden with the <CFINSERT> tag. <CFINSERT> will automatically generate the SQL call to insert submitted form data into a database—all you need to provide is the data source name and the name of the table where the data should be inserted. The one catch, however, is that the names of the form fields and the names of the corresponding columns in the database have to be an exact match. Looking back at the form that collects subscriber information, you can see that the form field names are subscriber_name and subscriber_email. However, the SQL in Listing 34.2 above tells you that the column names you’re inserting into are name and email. The names of the form fields and columns are different in this case, so you are not set up properly to use <CFINSERT>. If you abbreviated the form field names to name and email, then you would be able to successfully use <CFINSERT>. In that case, you could replace the <CFQUERY> code with

<CFINSERT DATASOURCE=“newsletter” TABLENAME=“Subscribers”>

and achieve the same result.

NOTE: <CFINSERT> also takes the FORMFIELDS attribute, which is set equal to the names of the fields you want to insert. If FORMFIELDS is not specified, ColdFusion tries to insert every form field that was submitted.

Updating an Existing Database Record A third common database operation is to make a change to an existing record or records. Again, the <CFQUERY> tag plays a pivotal role in doing the update.

Table of Contents

Products | Contact Us | About Us | Privacy | Ad Info | Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.