home Links Articles Books Past Meetings Photos SiteMap
The MDCFUG is sponsored by TeraTech. Visit us at www.TeraTech.com

Please send
comments/questions to

michael@
teratech.com

 

 

CPCUG Monitor

Creating dynamic websites with ColdFusion

Part 6: Handling ID fields

by Michael Smith

TeraTech http://www.teratech.com/

What is ColdFusion?

In this article we continue to look at what ColdFusion is and how you can use it for dynamic website creation. We cover ID field creation, using include files to save coding time, and how to write a deletion confirmation page to prevent accidents!

 

In case you missed previous articles that introduced ColdFusion, let me explain what it is. ColdFusion is a programming language based on standard HTML (Hyper Text Markup Language) that is used to write dynamic web pages. It lets you create pages on the fly that differ depending on user input, database lookups, time of day or what ever other criteria you dream up! ColdFusion pages consist of standard HTML tags such as <FONT SIZE="+2"> together with CFML (ColdFusion Markup Language) tags such as <CFSEARCH>, <CFIF> and <CFLOOP>. ColdFusion was introduced by Allaire in 1996 and is currently on version 4.

 

Why ID

Usually database tables contain an ID field that is used to refer to each record uniquely. The Ids are of four main types of ID:

1.      Autonumber - sequential numbers 1,2,3 etc

2.      Timestamp - unique date/time of record creation.

3.      Unique physical key - e.g. Social Security Number

4.      Random number

 

The problem we have is how to know what the ID value is for a new record. The SQL INSERT command does not return this information to us. For the physical key and random number methods, we can determine the ID before running the insert so this is ok. But for the autonumber and timestamp methods, we only know the next ID after inserting a new record. A catch 22!! Below we show you how to solve this problem for the autonumber - the timestamp code is almost identical.

 

Incrementing Unique ID’s after Inserting Data into a Database

1.      Using an AUTONUMBER field for the ID.  This data type allows the ID to be automatically assigned or incremented when a new record is inserted into that particular table.  Therefore, one can simply insert the new values for the other fields in the record to be added using an INSERT INTO query as follows:

 

<CFQUERY datasource = “databasename” name = “queryname”>

                              INSERT INTO tablename (fieldname1, fieldname2. . . fieldnameN)

                                          VALUES (value1, value2 . . . valueN)

            </CFQUERY>

 

      Although the ID in this case will not be explicitly assigned, Access will automatically assign a new ID to this additional record.  The main problem with using autonumber fields for ColdFusion applications (and database applications in general) is that it is hard to find the value of the new ID which is automatically assigned to the new record(s).  It can be located using

 

<CFQUERY datasource = “databasename” name = “queryname”>

                        SELECT MAX(idfield) AS newid FROM tablename

      </CFQUERY>

 

But this query can be slow to run and has problems when two users add records at the same time. Therefore, it is generally better to use the approach outlined in number 2 below when assigning ID values to new records.

 

2.      Using a Number field for the ID.  In this case you must explicitly assign a new, unique ID to the added record. 

 

<CFQUERY datasource = “databasename” name = “queryname”>

SELECT newid FROM NextID

      </CFQUERY>

 

<CFQUERY datasource = “databasename” name = “queryname”>

UPDATE NextID SET NextID = NextID + 1

      </CFQUERY>

     

<CFQUERY datasource = “databasename” name = “queryname”>

                              INSERT INTO tablename (idfield, fieldname1, . . . fieldnameN)

                                          VALUES (#queryname.newid#, value1, . . . valueN)

      </CFQUERY>

 

This example selects the current next ID from a simple one row, one column table called NextID and we set the value of the new ID to one greater than that maximum.  The second query then inserts this new record with the newly assigned ID value into the table. 

 

Alternatively, you can use the same SELECT max idea as before but add one to the result:

<CFQUERY datasource = “databasename” name = “queryname”>

                        SELECT MAX(idfield) + 1 AS newid FROM tablename

      </CFQUERY>

The problem with this scenario is that it fails when no records exist in the table initially.  In such a case, the maximum value for the ID field would be Null, and hence the new ID value would likewise be set to Null, rendering the INSERT query impossible.  To avoid this problem, a CFIF statement can be used to check the maximum ID value, and to set it to zero if there are no records in the table.  This is shown in the code below:

 

<CFQUERY datasource = “databasename” name = “queryname”>

                        SELECT MAX(idfield) + 1 AS newid FROM tablename

      </CFQUERY>

     

      <CFIF queryname.recordcount is 0>

            <CFSET newid2 = 0>

       <CFELSE>

            <CFSET newid2 = queryname.newid >

       </CFIF>

 

<CFQUERY datasource = “databasename” name = “queryname”>

                              INSERT INTO tablename (idfield, fieldname1, . . . fieldnameN)

                                          VALUES (#newid2#, value1, . . . valueN)

</CFQUERY>

 

In a production site you may also want to use <CFTRANSACTION> around the above code or put both SQL statements in a stored procedure.

 

Using the <CFINCLUDE> Tag

 

      The CFINCLUDE tag references another .html, .txt, or .cfm file which contains text, bitmaps, or other HTML code that will be incorporated in the current page.  It is advantageous to use such a tag to reference a file, which contains items that will be included on multiple pages within a web site.  For example, the ID creation code above might be used in many places in your site. Putting it in one place makes improving the code simpler, as you only have to edit it one time and not twenty!

 

Often an include template file will contains header, footer, bitmap, or background information which needs to appear on multiple pages.  Therefore, if the background or company logo needs to be updated or modified, it can be done only once in the template file and then all the pages referencing the file in a <CFINCLUDE> tag will automatically be updated.  An example of using this tag is shown below, where “header.cfm” is a file containing header information to be contained on all the web pages for a particular project, and similarly, “footer.cfm“ contains footer information to be carried across a group of pages.

 

      <HTML>

 

      <BODY>

<CFINCLUDE TEMPLATE = “header.cfm”>

     

                              additional HTML code for the contents of this page . . .

 

                  <CFINCLUDE TEMPLATE = “footer.cfm“>

</BODY>

 

</HTML>

 

 

 

Using a Delete Confirmation Page

 

      This page is used to confirm that a user wants to delete a certain record, which they have selected from another page, before actually performing the delete.  It acts similarly to a dialogue box that asks the user to confirm their decision to delete a file, except in straight HTML you can use pop up dialog boxes (you can use JavaScript, but that is beyond this article).  In this case, rather than having a link from a select page which directly points to another page that performs a DELETE query, the link from the select page points to the confirmation page, which then displays the record the user has chosen to delete.  The user can then confirm their decision by clicking on a link, which goes to a page where the DELETE query is performed, or they can cancel their decision and return to the select page. 

 

 

 

The following code demonstrates how this works:

 

Select Page – displays records in table and a link to a delete confirmation page for each record

 

<CFQUERY datasource="databasename" name="queryname">

SELECT IDfieldname, fieldname1, fieldname2, … fieldnameN  

FROM tablename;

</CFQUERY>

 

<CFOUTPUT>

  <center>  The tablename contains the following records: </center>

  <br><br>

  <table align="center">

    <tr> <td> fieldname1 </td>

<td> fieldname2 </td>

            ….

<td> fieldnameN </td>

<td> Delete Link </td></tr>

  </table           

 <br><br>

<CFLOOP query="queryname">

   <table align="center">

      <tr>

         <td align="left"># fieldname1#</td>

         <td align="left"># fieldname2#</td>

                                    ….

<td align="left">#fieldnameN#</td>

         <td> <a HREF = ”deleteconfirmationpage.cfm?linkvariablename=#IDfieldname#"> Delete:  Click Here to Delete this Record </a></td>

      </tr>

</table>

</CFLOOP>

</CFOUTPUT>

 

Delete Confirmation Page – displays record from select page which the user chose to delete

 

Are you sure you want to delete the record shown below?

 

<CFQUERY datasource="databasename" name="queryname">

SELECT IDfieldname, fieldname1, fieldname2, … fieldnameN

FROM tablename

WHERE IDfieldname=#url.linkvariablname#";

</CFQUERY>

<CFOUTPUT>

Field1:  #queryname.fieldname1# 

Field2:  #queryname.fieldname2#

                        ….

FieldN:  #queryname.fieldnameN#

<br><br>

<a href="deletepage.cfm?linkvariablename=#queryname.IDfieldname#"> Yes </a> <br>

<a href="selectpage.cfm"> No </a>

</CFOUTPUT>

 

Delete Confirmation Page – deletes the record

 

                  <CFQUERY datasource = “databasename” name = “queryname”>

                              DELETE  * FROM tablename

WHERE IDfieldname=#url.linkvariablname#";

                  </CFQUERY>

 

Note: For this intermediate page to work correctly it is necessary to both pass the unique id of the record to be deleted to the page and for the intermediate page to in turn pass this id to the action page that does the SQL delete on the record. This is because web pages are stateless and don't know variables, unless you pass them in!

 

Summary

In this article we learned how to control Identity field creation, how to save time with CFINCLUDE and how to control deletion in web sites.

 

To Learn More

You can download a free 30 day-evaluation version of ColdFusion from Allaire or request a free eval CD-ROM from the Allaire website http://www.allaire.com/ 

 

Allaire Corporation

1 Alewife Center

Cambridge, MA 02140

 

Tel: 617.761.2000 voice

Fax: 617.761.2001 fax

Toll Free: 888.939.2545

Email: sales@allaire.com

Web: www.allaire.com

 

ColdFusion Resources

Allaire also maintains an extensive knowledge base and tech support forums on their website.

CPCUG and TeraTech ColdFusion Conference http://www.cfconf.org/

TeraTech maintains a ColdFusion code cuttings called ColdCuts at http://www.teratech.com/ColdCuts/. This page also has links to about a dozen ColdFusion white papers in the CF Info Center.

The Maryland ColdFusion User Group meets the second Tuesday of each month at Backstreets Cafe, 12352 Wilkins Avenue, Rockville. See http://www.cfug-md.org/ for details and directions.

The DC ColdFusion User Group meets the first Wednesday each month at Figleaf , 16th and P St NW, Washington DC. See the DCCFUG page on http://www.figleaf.com/ for details and directions.

Bio

Michael Smith is president of TeraTech; an eleven-year-old Rockville Maryland based consulting company that specializes in ColdFusion, Database and Visual Basic development. You can reach Michael at michael@teratech.com or 301-424-3903.


Home | Links | Articles | Past Meetings | Meeting Photos | Site Map
About MDCFUG | Join | Mailing List |Forums | Directions |Suggestions | Quotes | Newbie Tips
TOP

Copyright © 1997-2017, Maryland Cold Fusion User Group. All rights reserved.
< >