Creating an Updateable Grid  
 
 

You can build grids so that end users are allowed to edit data. These edits can be the basis for changes you make to a data source either by forming queries with CFQUERY or by using the CFGRIDUPDATE tag, which passes edits made to grid data directly to your data source.

Individual cell data can be edited and rows can be inserted, deleted or updated. You enable this facility by specifying SELECTMODE="EDIT" in the CFGRID tag and by enabling the INSERT or DELETE attributes in CFGRID. A grid can now provide data source management, offer data input and review, as well as display tabular data in a grid format.

There are essentially two ways to use an updateable grid to make changes to your ODBC data sources. You can create a page to which you pass the CFGRID form variables and in that page perform CFQUERY operations to update data source records. Or you can pass grid edits to a page that includes the CFGRIDUPDATE tag, which passes data directly to the data source. Although using CFQUERY gives you complete control over interactions with your data source, CFGRIDUPDATE provides a much simpler interface for operations that do not require the same level of control.

 
 
  Editing data in a CFGRID  
 
 

To enable grid editing, you use the SELECTMODE="EDIT" attribute. When enabled, a user can edit cell data and insert or delete grid rows. Users change the content of a cell by clicking on it and editing its contents using simple editing operations. When enabled users can select a row and delete it, or select a row and insert a new row. When a CFFORM containing a CFGRID is submitted, data about changes to grid cells are stored in one-dimensional arrays you can reference like any other ColdFusion array.

The following arrays are created to keep track of edits to grid rows and cells:

Arrays Used to Store Grid Cell Edit Information
Array reference
Description
gridname.colname [ row_index ] Stores the new value of an edited grid cell
gridname.Original.colname [ row_index ] Stores the original value of the edited grid cell
gridname.RowStatus.Action [ row_index ] Stores the edit type made against the edited grid cell.

For example, you have an updateable CFGRID called "mygrid" consisting of two displayable columns, col1, col2, and one hidden column, col3. When an end user selects and changes data in a row, arrays are created to store the original values for all columns as well as the new column values for rows that have been updated, inserted, or deleted.

mygrid.col1[ row_index ]
mygrid.col2[ row_index ]
mygrid.col3[ row_index ]
mygrid.original.col1[ row_index ]
mygrid.original.col2[ row_index ]
mygrid.original.col3[ row_index ]

Where row_index is the array index containing the grid data.

If the end user makes a change to a single cell in col2, you can reference the edit operation, the original cell value, and the edited cell value in the following arrays:

<CFSET edittype = mygrid.RowStatus.Action[1]><BR>
<CFSET new_value = mygrid.col2[1]><BR>
<CFSET old_value = mygrid.original.col2[1]>
 
 
  Specifying alternate text for the Insert or Delete buttons  
 

If you want the Insert or Delete buttons in an updateable grid to use text other than "Insert" or "Delete," you can specify alternate text in the INSERTBUTTON and DELETEBUTTON attributes.

 
 
  Multi-row edits  
 

The use of arrays to track changes allows ColdFusion to manage changes to more than one row in a CFGRID. ColdFusion coordinates entries in the arrays used to store edit type (Update, Insert, or Delete), with arrays that store original grid data and edited grid data. For each grid cell edit, an entry is created in the RowStatus array, and corresponding entries are made in the arrays that store the new cell value and the original cell value.

 
 
  Sorting grid data  
 

The CFGRID SORT attribute allows you to include sort buttons in your grid control. When enabled, sort buttons are automatically added to the grid. When clicked, data is sorted in the selected column. ColdFusion sorts columns either as text or as numeric data.

The following CFGRID attributes are available for defining various options relating to sorting data:

  • PICTUREBAR -- When Yes, an image button is substituted for the Sort text button.
  • SORTASCENDINGBUTTON -- You can specify the text to use for the Sort Descending button if you don't want to use the default, which is "A -> Z".
  • SORTDESCENDINGBUTTON -- You can specify the text to use for the Sort Descending button if you don't want to use the default, which is "Z <- A".
 
 
  Note  
 

Users must first select a column before clicking a sort button.

 
 
  Example: Editable grid  
 

This grid example demonstrates an updateable grid, in which the SELECTMODE attribute is "Edit" and the INSERT and DELETE attributes are "yes." When the form is submitted, the handle_grid.cfm page displays the type of edits that were made to the data source, Update, Delete, or Insert and interacts with the data source directly to perform the corresponding actions.

Grid.cfm

<HTML>
<HEAD>
    <TITLE>Simple Update Grid Example</TITLE>
</HEAD>

<CFQUERY NAME="CourseList" 
    DATASOURCE="cfsnippets">
    SELECT * FROM Courses
</CFQUERY>

<BODY BGCOLOR="#FFFFFF">

<CFFORM NAME="GridForm"
    ACTION="handle_grid.cfm">

<CFGRID NAME="course_grid"
        HEIGHT=170
        WIDTH=400
        HSPACE=10
        VSPACE=6
        ALIGN="RIGHT"
        SELECTCOLOR="white"
        SELECTMODE="edit"
        ROWHEADERS="YES"
        ROWHEADERWIDTH=25
        ROWHEADERALIGN="right"
        COLHEADERS="YES"
        QUERY="CourseList"
        GRIDDATAALIGN="left"
        BGCOLOR="green"
        INSERT="YES"
        DELETE="YES"
        SORT="YES"
        MAXROWS=60>

    <CFGRIDCOLUMN NAME="course_id"
        HEADER="Course ID"
        WIDTH=80
        ITALIC="NO"
        HEADERALIGN="center"
        HEADERITALIC="NO"
        HEADERBOLD="YES"
        DISPLAY="NO">

<CFGRIDCOLUMN NAME="number"
        HEADER="Course ##"
        WIDTH=80
        ITALIC="NO"
        HEADERALIGN="center"
        HEADERITALIC="NO"
        HEADERBOLD="YES"
        DISPLAY="YES"
        SELECT="YES">

    <CFGRIDCOLUMN NAME="description"
        HEADER="Description"
        WIDTH=240
        ITALIC="No"
        HEADERALIGN="center"
        HEADERITALIC="No"
        HEADERBOLD="Yes"
        BOLD="Yes"
        ITALIC="Yes"
        DISPLAY="Yes">

    </CFGRID>

<!--- 
<H3>Editable Grid</H3>

This is a grid that is populated <BR>
from a query. The select mode is <BR>
update. The description column<BR>
is presented in Bold and Italic.<BR><BR> --->

<INPUT TYPE="Submit" VALUE=" Push me... "> <BR>

</CFFORM>

</BODY>
</HTML>

The grid and action button look like this:

Changes you make in the course list are reflected in the Courses table in the cfsnippets data source. You can view the table in Studio by going to the DB tab and opening the data source.

Handle_grid.cfm

<HTML>
<HEAD>
    <TITLE>Catch submitted grid values</TITLE>
</HEAD>
<BODY>

<H3>Grid values for FORM.Course_grid row updates</H3>

<CFIF IsDefined("form.course_grid.rowstatus.action")>

    <CFLOOP INDEX = "Counter" FROM = "1" TO =
        #ArrayLen(form.course_grid.rowstatus.action)#>

    <CFOUTPUT>
    The row action for #Counter# is:
    #form.course_grid.rowstatus.action[Counter]#
    <BR><BR>
    </CFOUTPUT>

    <CFIF form.course_grid.rowstatus.action[Counter] IS "D">
    
    <CFQUERY NAME="InsertNewCourse" 
        DATASOURCE="cfsnippets">
        DELETE from courses
        WHERE course_id=#form.course_grid.original.course_id[Counter]#
    </CFQUERY>

<CFELSEIF form.course_grid.rowstatus.action[Counter] IS "U">

    <CFQUERY NAME="UpdateExistingCourse"
        DATASOURCE="cfsnippets">
        UPDATE courses
        SET description='#form.course_grid.description[Counter]#' ,
        "Number"='#form.course_grid.number[Counter]#'
        WHERE
        course_id=#form.course_grid.original.course_id[Counter]#
    </CFQUERY>

<CFELSEIF form.course_grid.rowstatus.action[Counter] IS "I">

    <CFQUERY NAME="InsertNewCourse"
        DATASOURCE="cfsnippets">
        INSERT into courses
        ("Number", description)
        VALUES ('#form.course_grid.number[Counter]#',
        '#form.course_grid.description[Counter]#')
    </CFQUERY>

    </CFIF>
    </CFLOOP>
</CFIF>

</BODY>
</HTML>
 
 
  Using CFGRIDUPDATE  
 
 

The CFGRIDUPDATE tag allows you to perform updates to a data source directly from a CFGRID. You don't need to form a CFQUERY to perform updates, CFGRIDUPDATE handles the entire transaction, taking grid cell edit information directly from the CFGRID tag. It's a much simpler, but slightly more abstracted method for updating data from a grid control.

In the earlier example, data from an edited grid was passed to a page that used CFQUERY tags in a CFIF construct to update a data source. For many updates, CFGRIDUPDATE is easier.

For example, the following code accepts grid update data from a grid named "Courses" originating in a separate page.

<CFGRIDUPDATE GRID="Courses"
    DATASOURCE="CF 4.0 Examples"
    TABLENAME="Courses"
    KEYONLY="NO">

The arrays that store information about changes to CFGRID data (as well as original grid cell values) are passed to CFGRIDUPDATE when the form is submitted. CFGRIDUPDATE uses these arrays to build the SQL necessary to perform the data source updates.

The edits originating in the Courses grid could consist of multiple cell edits, row inserts, and row deletions. CFGRIDUPDATE passes all of these edits to the specified data source, saving you the task of having to craft CFQUERY statements to do the same work. Very handy.

 
 
  The KEYONLY attribute  
 

CFGRIDUPDATE includes the KEYONLY attribute, which allows you to force ColdFusion to compare the original value of the updated fields with the data in the corresponding table field. If they are the same, that is, if no other process has changed the data since the grid was edited, the update passes. If the comparison fails an error is generated. Use KEYONLY="No" when you want to be sure that no other process has updated the same data. Use KEYONLY="Yes" if no other process can potentially change the same data.

 
 
  Embedding images in a grid  
 
 

The CFGRIDROW tag allows you to place images in a grid cell. You do this by first using the TYPE="IMAGE" attribute in a CFGRIDCOLUMN tag to tell ColdFusion that you want data in the current column to be interpreted as an image. You can use one of the built-in image names ColdFusion provides (same as those for CFTREEITEM) or specify an image file of your choice. The built-in image names are as follows:

Built-in Image Names 
Image
Example
cd

computer

document

element

floppy

folder

fixed

remote

Here's part of a CFGRID showing the CFGRIDCOLUMN tag using TYPE="IMAGE" to define the column that will contain images, and the CFGRIDROW tags that populate each column with row data.

...
<CFGRIDCOLUMN
    NAME="dept_name"
    HEADER="Dept"
    SELECT="NO"
    DATAALIGN="Center"
    WIDTH=40
    TYPE=IMAGE>

<CFGRIDCOLUMN
    NAME="emp_lname"
    HEADER="Name">

<CFGRIDROW DATA="folder,Jones">
<CFGRIDROW DATA="document,Smith">
...
 
 
  Note  
 

In this example, commas are used to separate the image name that appears in the first column from the data that appears in the second column.

 
 
  Using your own images in a grid  
 

When you want to use your own image files instead of the built-in ColdFusion images, you need to specify the relative path to the directory where the image file can be found, as well as the image file name itself.

When you specify the relative path to the images you want to use, note that the path is relative to the location of the Java class that enables the CFGRID control. Ordinarily, this will be:

web_root\cfide\classes\images

You can specify a location relative to this directory, or you can simply place your files in the cfide\classes\images folder and use the image name without an extension, just as you would use one of the built-in image names.

In the following code chunk, the CFGRIDROW tags use external images found in the web_root\images directory.

...
<CFGRIDCOLUMN
    NAME="dept_name"
    HEADER="Dept"
    SELECT="NO"
    DATAALIGN="Center"
    WIDTH=40
    TYPE=IMAGE>

<CFGRIDCOLUMN
    NAME="emp_lname"
    HEADER="Name">

<CFGRIDROW DATA="..\..\..\images\icon1.gif,Jones">
<CFGRIDROW DATA="..\..\..\images\icon2.gif,Smith">
...


 
 
BackUp LevelNext
 
 

allaire     AllaireDoc@allaire.com
    Copyright © 1998, Allaire Corporation. All rights reserved.