The cfgrid tag creates a cfform grid control that resembles a spreadsheet table and can contain data populated from a cfquery tag or from other sources of data. As with other cfform tags, the cfgrid tag offers a wide range of data formatting options, as well as the option of validating user selections with a JavaScript validation script.
You can also perform the following tasks with a cfgrid tag:

  • Sort data in the grid alphanumerically.
  • Update, insert, and delete data.
  • Display images in the grid.

Note: Flash format grids support a subset of the features available in applet format grids. For details on features supported in each format, see the cfgrid tag in the CFML Reference.

Users can sort the grid entries in ascending order by double-clicking any column header. Double-clicking again sorts the grid in descending order. In applet format, you can also add sort buttons to the grid control.
When users select grid data and submit the form, ColdFusion passes the selection information as form variables to the application page specified in the cfform action attribute.
Just as the cftree tag uses the cftreeitem tag, the cfgrid tag uses the cfgridcolumn and cfgridrow tags. You can define a wide range of row and column formatting options, as well as a column name, data type, selection options, and so on. You use the cfgridcolumn tag to define individual columns in the grid or associate a query column with a grid column. 
Use the cfgridrow tag to define a grid that does not use a query as the source for row data. If a query attribute is specified in the cfgrid tag, the cfgridrow tags are ignored.
The cfgrid tag provides many attributes that control grid behavior and appearance. Only the most important of these attributes are described here. For detailed information on these attributes, see the cfgrid tag in the CFML Reference.

Working with a data grid and entering data

The following image shows an example applet format grid created using a cfgrid tag.
The following table describes some navigating tips:

Action

Procedure

Sorting grid rows

Double-click the column header to sort a column in ascending order. Double-click again to sort the rows in descending order.

Rearranging columns

Click any column heading and drag the column to a new position.

Determining editable grid areas

When you click an editable cell, it is surrounded by a yellow box.

Determining noneditable grid areas

When you click a cell (or row or column) that you cannot edit, its background color changes. The default color is salmon pink.

Editing a grid cell

Double-click the cell. Press Return when you finish entering the data.

Deleting a row

Click any cell in the row and click the Delete button. (Not available in Flash format grids.)

Inserting a row

Click the Insert button. An empty row appears at the bottom of the grid. To enter a value in each cell, double-click the cell, enter the value, and click Return. (Not available in Flash format grids.)

Populate a grid from a query
  1. Create a ColdFusion page named grid1.cfm with the following contents:

    <cfquery name="empdata" datasource="cfdocexamples">
    SELECT * FROM Employee
    </cfquery>

    <cfform name="Form1" action="submit.cfm" >
    <cfgrid name="employee_grid" query="empdata"
    selectmode="single">
    <cfgridcolumn name="Emp_ID">
    <cfgridcolumn name="LastName">
    <cfgridcolumn name="Dept_ID">
    </cfgrid>
    <br>
    <cfinput name="submitit" type="Submit" value="Submit">
    </cfform>

Note: Use the cfgridcolumn display="No" attribute to hide columns that you want to include in the grid but not expose to an end user. You typically use this attribute to include columns such as the table's primary key column in the results returned by the cfgrid_ tag._

  1. Save the file and view it in your browser.
Reviewing the code

The following table describes the highlighted code and its function:

Code

Description

<cfgrid name="employee_grid" query="empdata"

Creates a grid named employee_grid and populate it with the results of the query empdata.If you specify a cfgrid tag with a query attribute defined and no corresponding cfgridcolumn attributes, the grid contains all the columns in the query.

selectmode="single">

Allows the user to select only one cell; does not allow editing. Other modes are row, column, and edit.

<cfgridcolumn name="Emp_ID">

Puts the contents of the Emp_ID column in the query results in the first column of the grid.

<cfgridcolumn name="LastName">

Puts the contents of the LastName column in the query results in the second column of the grid.

<cfgridcolumn name="Dept_ID">

Puts the contents of the Dept_ID column in the query results in the third column of the grid.

Creating an editable grid

You can build grids to allow users to edit data within them. Users can edit individual cell data, as well as insert, update, or delete rows. To enable grid editing, you specify selectmode="edit" in the cfgrid tag. 
You can let users add or delete grid rows by setting the insert or delete attributes in the cfgrid tag to Yes. Setting the insert and delete attribute to Yes causes the cfgrid tag to display Insert and Delete buttons as part of the grid.
You can use a grid in two ways to change your ColdFusion data sources:

  • Create a page to which you pass the cfgrid form variables. In that page, perform cfquery operations to update data source records based on the form values returned by the cfgrid tag.
  • Pass grid edits to a page that includes the cfgridupdate tag, which automatically extracts the form variable values and passes that data directly to the data source.
    Using the cfquery tag gives you complete control over interactions with your data source. The cfgridupdate tag provides a much simpler interface for operations that do not require the same level of control.
Controlling cell contents

You can control the data that a user can enter into a cfgrid cell in the following ways:

  • By default, a cell is not editable. Use the cfgrid attribute selectmode="edit" to edit cell contents.
  • Use the cfgridcolumn type attribute to control sorting order, to make the fields check boxes, or to display an image.
  • Use the cfgridcolumn values attribute to specify a drop-down list of values from which the user can choose. You can use the valuesDisplay attribute to provide a list of items to display that differs from the actual values that you enter in the database. You can use the valuesDelimiter attribute to specify the separator between values in the values valuesDisplay lists.
  • Although the cfgrid tag does not have a validate attribute, it does have an onValidate attribute that lets you specify a JavaScript function to perform validation.
    For more information on controlling the cell contents, see the attribute descriptions for the cfgridcolumn tag in the CFML Reference.
How user edits are returned

When a user inserts or deletes a row in a grid or changes any cells in a row and submits the grid, ColdFusion creates the following arrays as Form variables:

Array name

Description

gridname.colname

Stores the new values of inserted, deleted, or updated cells. (Entries for deleted cells contain empty strings.)

gridname.Original.colname

Stores the original values of inserted, deleted, or updated cells.

gridname.RowStatus.Action

Stores the type of change made to the grid rows: D for delete, I for insert, or U for update.

 

Note: The periods in these names are not structure separators; they are part of the text of the array name.

ColdFusion creates a gridname.colname array and a gridname.Original.colname array for each column in the grid. For each inserted, deleted, or changed row in the grid, ColdFusion creates a row in each of these arrays. 
For example, the following arrays are created if you update a cfgrid tag called mygrid that consists of two displayable columns (col1, col2) and one hidden column (col3):

Form.mygrid.col1
Form.mygrid.col2
Form.mygrid.col3
Form.mygrid.original.col1
Form.mygrid.original.col2
Form.mygrid.original.col3
Form.mygrid.RowStatus.Action

The value of the array index increments for each row that is added, deleted, or changed, and does not indicate a grid row number. All rows for a particular change have the same index in all arrays. Unchanged rows do not have entries in the arrays.
If the user changes a single cell in col2, the following array elements contain the edit operation, the edited cell value, and the original cell value:

Form.mygrid.RowStatus.Action[1]
Form.mygrid.col2[1]
Form.mygrid.original.col2[1]

If the user changes the values of the cells in col1 and col3 in one row and the cell in col2 in another row, the information about the original and changed values is in the following array entries:

Form.mygrid.RowStatus.Action[1]
Form.mygrid.col1[1]
Form.mygrid.original.col1[1]
Form.mygrid.col3[1]
Form.mygrid.original.col3[1]
Form.mygrid.RowStatus.Action[2]
Form.mygrid.col2[2]
Form.mygrid.original.col2[2]

The remaining cells in the arrays (for example, Form.mygrid.col21 and Form.mygrid.original.col21) have the original, unchanged values.

Example: editing data in a grid

The following example creates an editable grid. For code brevity, the example handles only three of the fields in the Employee table. A more realistic example would include, at a minimum, all seven table fields. It can also hide the contents of the Emp_ID column or display the Department name (from the Departmt table), instead of the Department ID.

Create the editable grid
  1. Create a ColdFusion page with the following content:

    <cfquery name="empdata" datasource="cfdocexamples">
    SELECT * FROM Employee
    </cfquery>

    <cfform name="GridForm"
    action="handle_grid.cfm">

    <cfgrid name="employee_grid"
    height=425
    width=300
    vspace=10
    selectmode="edit"
    query="empdata"
    insert="Yes"
    delete="Yes">

    <cfgridcolumn name="Emp_ID"
    header="Emp ID"
    width=50
    headeralign="center"
    headerbold="Yes"
    select="No">

    <cfgridcolumn name="LastName"
    header="Last Name"
    width=100
    headeralign="center"
    headerbold="Yes">

    <cfgridcolumn name="Dept_ID"
    header="Dept"
    width=35
    headeralign="center"
    headerbold="Yes">

    </cfgrid>
    <br>
    <cfinput name="submitit" type="Submit" value="Submit">
    </cfform>

  2. Save the file as grid2.cfm.
  3. View the results in your browser.

h7. Reviewing the code
The following table describes the code and its function:

Code

Description

 

<cfgrid name="employee_grid"
height=425
width=300
vspace=10
selectmode="edit"
query="empdata"
insert="Yes"
delete="Yes">

 

Populates a cfgrid control with data from the empdata query. Selecting a grid cell enables you to edit it. You can insert and delete rows. The grid is 425 X 300 pixels and has 10 pixels of space above and below it.

 

<cfgridcolumn name="Emp_ID"
header="Emp ID"
width=50
headeralign="center"
headerbold="Yes"
select="No">

 

Creates a 50-pixel wide column for the data in the Emp_ID column of the data source. Centers a header named Emp ID and makes it bold. Does not allow users to select fields in this column for editing. Since this field is the table's primary key, users should not be able to change it for existing records, and the DBMS should generate this field as an autoincrement value.

 

<cfgridcolumn name="LastName"
header="Last Name"
width=100
headeralign="center"
headerbold="Yes">

 

Creates a 100-pixel wide column for the data in the LastName column of the data source. Centers a header named Last Name and makes it bold.

 

<cfgridcolumn name="Dept_ID"
header="Dept"
width=35
headeralign="center"
headerbold="Yes">

 

Creates a 35-pixel wide column for the data in the Dept_ID column of the data source. Centers a header named Dept and makes it bold.

Updating the database with the cfgridupdate tag

The cfgridupdate tag provides a simple mechanism for updating the database, including inserting and deleting records. It can add, update, and delete records simultaneously. It is convenient because it automatically handles collecting the cfgrid changes from the various form variables, and generates appropriate SQL statements to update your data source.
In most cases, use the cfgridupdate tag to update your database. However, this tag does not provide the complete SQL control that the cfquery tag provides. In particular, the cfgridupdate tag has the following characteristics:

  • You can update only a single table.
  • Rows are deleted first, then rows are inserted, then any changes are made to existing rows. You cannot modify the order of changes.
  • Updating stops when an error occurs. It is possible that some database changes are made, but the tag does not provide any information on them.
Update the data source with the cfgridupdate tag
  1. Create a ColdFusion page with the following contents:

    <html>
    <head>
    <title>Update grid values</title>
    </head>
    <body>

    <h3>Updating grid using cfgridupdate tag.</h3>

    <cfgridupdate grid="employee_grid"
    datasource="cfdocexamples"
    tablename="Employee">

    Click <a href="grid2.cfm">here</a> to display updated grid.

    </body>
    </html>

  2. Save the file as handle_grid.cfm.
  3. View the grid2.cfm page in your browser, change the grid, and then submit them.

To update a grid cell, modify the cell contents, and then press Return.

h7. Reviewing the code
The following table describes the highlighted code and its function:

Code

Description

<cfgridupdate grid="employee_grid"

Updates the database from the Employee_grid grid.

datasource="cfdocexamples"

Updates the cfdocexamples data source.

tablename="Employee"

Updates the Employee table.

Updating the database with the cfquery tag

You can use the cfquery tag to update your database from the cfgrid changes. This tag provides you with full control over how the updates are made and lets you handle any errors that arise.

Update the data source with the cfquery tag
  1. Create a ColdFusion page with the following content:

    <html>
    <head>
    <title>Catch submitted grid values</title>
    </head>
    <body>

    <h3>Grid values for Form.employee_grid row updates</h3>

    <cfif isdefined("Form.employee_grid.rowstatus.action")>

    <cfloop index = "counter" from = "1" to =
    #arraylen(Form.employee_grid.rowstatus.action)#>

    <cfoutput>
    The row action for #counter# is:
    #Form.employee_grid.rowstatus.action[counter]#
    <br>
    </cfoutput>

    <cfif Form.employee_grid.rowstatus.action[counter] is "D">
    <cfquery name="DeleteExistingEmployee"
    datasource="cfdocexamples">
    DELETE FROM Employee
    WHERE Emp_ID=<cfqueryparam
    value="#Form.employee_grid.original.Emp_ID[counter]#"
    CFSQLType="CF_SQL_INTEGER" >
    </cfquery>

    <cfelseif Form.employee_grid.rowstatus.action[counter] is "U">
    <cfquery name="UpdateExistingEmployee"
    datasource="cfdocexamples">
    UPDATE Employee
    SET
    LastName=<cfqueryparam
    value="#Form.employee_grid.LastName[counter]#"
    CFSQLType="CF_SQL_VARCHAR" >,
    Dept_ID=<cfqueryparam
    value="#Form.employee_grid.Dept_ID[counter]#"
    CFSQLType="CF_SQL_INTEGER" >
    WHERE Emp_ID=<cfqueryparam
    value="#Form.employee_grid.original.Emp_ID[counter]#"
    CFSQLType="CF_SQL_INTEGER">
    </cfquery>

    <cfelseif Form.employee_grid.rowstatus.action[counter] is "I">
    <cfquery name="InsertNewEmployee"
    datasource="cfdocexamples">
    INSERT into Employee (LastName, Dept_ID)
    VALUES (<cfqueryparam
    value="#Form.employee_grid.LastName[counter]#"
    CFSQLType="CF_SQL_VARCHAR" >,
    <cfqueryparam value="#Form.employee_grid.Dept_ID[counter]#"
    CFSQLType="CF_SQL_INTEGER" >)
    </cfquery>

    </cfif>
    </cfloop>
    </cfif>

    Click <a href="grid2.cfm">here</a> to display updated grid.

    </body>
    </html>

     

  2. Rename your existing handle_grid.cfm file as handle_grid2.cfm to save it, and then save this file as handle_grid.cfm.
  3. View the grid2.cfm page in your browser, change the grid, and then submit them.

h7. Reviewing the code
The following table describes the code and its function:

Code

Description

 

<cfif isdefined("Form.employee_grid.rowstatus.action")>
<cfloop index = "counter" from = "1" to = #arraylen(Form.employee_grid.rowstatus.action)#>

 

If there is an array of edit types, changes the table. Otherwise, does nothing. Loops through the remaining code once for each row to be changed. The counter variable is the common index into the arrays of change information for the row being changed.

 

<cfoutput>
The row action for #counter# is:
#Form.employee_grid.rowstatus.action[counter]#
<br>
</cfoutput>

 

Displays the action code for this row: U for update, I for insert, or D for delete.

 

<cfif Form.employee_grid.rowstatus.action[counter] is "D">
<cfquery name="DeleteExistingEmployee"
datasource="cfdocexamples">
DELETE FROM Employee
WHERE Emp_ID=<cfqueryparam value="#Form.employee_grid.original.Emp_ID[counter]#"
CFSQLType="CF_SQL_INTEGER" >
</cfquery>

 

If the action is to delete a row, generates a SQL DELETE query specifying the Emp_ID (the primary key) of the row to be deleted.

 

<cfelseif Form.employee_grid.rowstatus.action[counter] is "U">
<cfquery name="UpdateExistingEmployee"
datasource="cfdocexamples">
UPDATE Employee
SET
LastName=<cfqueryparam
value="#Form.employee_grid.LastName[counter]#"
CFSQLType="CF_SQL_VARCHAR" >,
Dept_ID=<cfqueryparam
value="#Form.employee_grid.Dept_ID[counter]#"
CFSQLType="CF_SQL_INTEGER" >
WHERE Emp_ID=<cfqueryparam
value="#Form.employee_grid.original.Emp_ID[counter]#"
CFSQLType="CF_SQL_INTEGER">
</cfquery>

 

Otherwise, if the action is to update a row, generates a SQL UPDATE query to update the LastName and Dept_ID fields for the row specified by the Emp_ID primary table key.

 

<cfelseif Form.employee_grid.rowstatus.action[counter] is "I">
<cfquery name="InsertNewEmployee"
datasource="cfdocexamples">
INSERT into Employee (LastName, Dept_ID)
VALUES (<cfqueryparam
value="#Form.employee_grid.LastName[counter]#"
CFSQLType="CF_SQL_VARCHAR" >,
<cfqueryparam value="#Form.employee_grid.Dept_ID[counter]#"
CFSQLType="CF_SQL_INTEGER" >)
</cfquery>

 

Otherwise, if the action is to insert a row, generates a SQL INSERT query to insert the employee's last name and department ID from the grid row into the database. The INSERT statement assumes that the DBMS automatically increments the Emp_ID primary key. If you use the version of the cfdocexamples database that is provided for UNIX installations, the record is inserted without an Emp_ID number.

 

</cfif>
</cfloop>
</cfif>

 

Closes the cfif tag used to select among deleting, updating, and inserting.Closes the loop used for each row to be changed.Closes the cfif tag that surrounds all the active code.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy