Creating data grids with cfgrid

The cfgrid tag creates a cfform grid control that resembles a spreadsheet table and can contain data populated from a cfquery or from other sources of data. As with other cfform tags, cfgrid 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 do the following tasks with cfgrid:

Users can sort the grid entries in ascending order by double-clicking any column header. Double-clicking again sorts the grid in descending order. 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 cftreeitem, cfgrid 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 cfgrid, the cfgridrow tags are ignored.

The cfgrid tag provides many attributes that control grid behavior and appearance. This chapter describes only the most important of these attributes. For detailed information on these attributes, see CFML Reference.

Working with a data grid and entering data

The following figure shows an example grid created using the cfgrid tag:

Example grid created using the 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. You must press Return when you finish entering the data.
Deleting a row
Click any cell in the row and click the Delete button.
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.

To populate a grid from a query:

  1. Create a new ColdFusion page named grid1.cfm with the following contents:
    <cfquery name="empdata" datasource="CompanyInfo">
      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><input 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 cfgrid.

  2. Save the file and view it in your browser. The following figure shows the output of this code:

    Grid populated from a query

Reviewing the code

The following table describes the highlighted code and its function:
Code
Description
<cfgrid name="employee_grid"
  query="empdata"
Create 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">
Allow the user to select only one cell. Other modes are row, column, and edit.
<cfgridcolumn name="Emp_ID">
Put the contents of the Emp_ID column in the query results in the first column of the grid.
<cfgridcolumn name="LastName">
Put the contents of the LastName column in the query results in the second column of the grid.
<cfgridcolumn name="Dept_ID">
Put 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.

To let users add or delete grid rows, you also have to set the insert or delete attributes in cfgrid to Yes. Setting insert or delete to Yes causes the cfgrid tag to display insert and delete buttons as part of the grid, as the following figure shows:

Grid showing the insert and delete buttons

You can use a grid in two ways to make changes to your ColdFusion data sources:

Using cfquery 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

The value, valuesDisplay, and valuesDelimiter attributes of the cfgridcolumn tag let you control the data that a user can enter into a cfgrid cell in the following ways:

For more information on controlling the cell contents, see the attribute descriptions in CFML Reference.

How user edits are returned

ColdFusion creates the following arrays as Form variables to return edits to grid rows and cells:

Array reference
Description
gridname.colname[change_index]
Stores the new value of an edited cell.
gridname.Original.colname [change_index]
Stores the original value of the edited grid cell.
gridname.RowStatus.Action [change_index]
Stores the edit type made to the edited grid row: D for delete, I for insert, or U for update.

When a user selects and changes data in a row, ColdFusion creates arrays to store the following information for rows that are updated, inserted, or deleted:

For example, the following arrays are created if you update a cfgrid called "mygrid" consisting of two displayable columns, (col1, col2) and one hidden column (col3):

Form.mygrid.col1[ change_index ]
Form.mygrid.col2[ change_index ]
Form.mygrid.col3[ change_index ]
Form.mygrid.original.col1[ change_index ]
Form.mygrid.original.col2[ change_index ]
Form.mygrid.original.col3[ change_index ]
Form.mygrid.RowStatus.Action[ change_index ]

The value of change_index increments for each row that changes, and does not indicate the specific row number. When the user updates data or inserts or deletes rows, the action page gets one array for each changed column, and the RowStatus.Action array. The action page does not get arrays for unchanged columns.

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

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]

Editing data in cfgrid

To enable grid editing, specify the selectmode="edit" attribute. When enabled, a user can edit cell data and insert or delete grid rows. When the user submits a cfform tag containing a cfgrid tag, data about changes to grid cells gets returned in the one-dimensional arrays described in the preceding section. You can reference these arrays as you would any other ColdFusion array.

Note:   For code brevity, the following example handles only three of the fields in the Employee table. A more realistic example would include, at a minimum, all seven table fields. You might also consider hiding the contents of the Emp_ID column and automatically generating its value for new records, and displaying the Department name, from the Departmt table, in place of the Department ID.

To make the grid editable:

  1. Create a new ColdFusion page with the following contents:
    <cfquery name="empdata" datasource="CompanyInfo">
      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>
      <input type="Submit" value="Submit">
    </cfform>
    
  2. Save the file as grid2.cfm and view it in your browser.

    The following figure shows the output of this code:

    Editable grid

The following sections describe how to write handle_grid.cfm to process user edits to the grid.

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. Center a header named Emp ID and make 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 automincrement 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. Center a header named Last Name and make 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. Center a header named Dept and make it bold.

Updating the database with cfgridupdate

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 particularly 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 cfquery provides. In particular, using the cfgridupdate tag, you can make the following changes:

To update the data source with cfgridupdate:

  1. Create a file 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="CompanyInfo"
      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 grid2.cfm in your browser, make changes to the grid, and then submit them.

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

Reviewing the code

The following table describes the highlighed code and its function:
Code
Description
<cfgridupdate grid="employee_grid"
Update the database from the Employee_grid grid.
datasource="CompanyInfo"
Update the CompanyInfo data source.
tablename="Employee"
Update the Employee table.

Updating the database with cfquery

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

To update the data source with cfquery:

  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="CompanyInfo">
            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="CompanyInfo">
            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="CompanyInfo">
            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, then save this file as handle_grid.cfm.
  3. View grid2.cfm in your browser, make changes to the grid, and then submit them.

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, then change the table. Otherwise, do nothing. Loops through the remaining code once for each row to be changed. Counter 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, I, or D.
<cfif Form.employee_grid.rowstatus.action[counter] is "D">
  <cfquery name="DeleteExistingEmployee" 
    datasource="CompanyInfo">
    DELETE FROM Employee
    WHERE Emp_ID=#Form.employee_grid.original.Emp_ID[Counter]#
  </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="CompanyInfo">
    UPDATE Employee
    SET LastName='#Form.employee_grid.LastName[Counter]#',
      Dept_ID=#Form.employee_grid.Dept_ID[Counter]#
  WHERE Emp_ID=#Form.employee_grid.original.Emp_ID[Counter]#
  </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="CompanyInfo">
  INSERT into Employee (LastName, Dept_ID)
  VALUES
    ('#Form.employee_grid.LastName[Counter]#',
     #Form.employee_grid.Dept_ID[Counter]#)
  </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 assumes that the DBMS automatically increments the Emp_ID primary key. If you use the Dbase version of the CompanyInfo 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.

Comments