Creating an Updateable 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 and enable the insert or delete attributes in cfgrid.

You can use an updateable grid in either of two ways to make changes to 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 base on the form values returned by cfgrid.
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 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.

Navigating and entering data in a grid

Navigating and using the cfgrid control is fairly straightforward, but here are a few tips:

To sort grid rows so that a column is in ascending order by double-clicking the column header. Double-clicking again sorts the rows in descending order.
To rearrange the columns, click any column heading and drag the column to a new position.
When you click a cell (or row or column) that you cannot edit, its background color changes. The default color is a salmon pink.
When you click a cell that you can edit, it is surrounded by a yellow box.
To edit a cell, Double-click it. You must press Return when you finish entering the data.
To delete a row, click any cell in the row and click the Delete button.
To insert 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.

Controlling cell contents

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

By default, a cell is an editable text field.
Use the type attribute to require numeric or string data, to make the fields check boxes, or to display an image.
Use the values attribute to specify a drop-down list of values from which the user can chose. 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.
While cfgrid 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 in the 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.

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:

The original values for all columns
The new column values
The type of change

For example, the following arrays are created if you an 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, plus the RowStatsus.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]><BR>
Form.mygrid.col1[1]><BR>
Form.mygrid.original.col1[1]>
Form.mygrid.col3[1]><BR>
Form.mygrid.original.col3[1]>
Form.mygrid.RowStatus.Action[2]><BR>
Form.mygrid.col2[2]><BR>
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 containing a cfgrid, 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 the sake of code brevity, the following example handles only three of the fields in the Employee table. A more realistic example would, at a minimum, include all seven of the table's 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:

Open the file grid1.cfm in ColdFusion Studio.

Edit the file so that it appears as follows:

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

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

  <cfgrid name="employee_grid"
    height=300
    width=250
    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>

Save the file as grid2.cfm.

Reviewing the code

The following table describes the code and its function:

Code

Description

<cfgrid name="employee_grid" height=300 width=250 vspace=10 selectmode="edit" query="empdata" insert="Yes" delete="Yes">
Populate a cfgrid control with data from the empdata query. Selecting a grid cell enables you to edit it. Rows can be inserted and deleted. The grid is 300 X 250 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">
Create 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.
Do 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">
Create 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">
Create 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.

Code	Description
<cfgrid name="employee_grid" height=300 width=250 vspace=10 selectmode="edit" query="empdata" insert="Yes" delete="Yes">	Populate a `cfgrid` control with data from the empdata query. Selecting a grid cell enables you to edit it. Rows can be inserted and deleted. The grid is 300 X 250 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">	Create 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. Do 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">	Create 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">	Create 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, you should use cfgridupdate to update your database. However, this tag does not provide the complete SQL control that cfquery provides. In particular:

It can update only a single table.
You have no control over the order of changes. Rows are deleted first, then rows are inserted, then any changes are made to existing rows.
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.

To update the data source with cfgridupdate

Open a new file in ColdFusion Studio.

Modify the file so that it appears as follows:

<cfgridupdate grid="Employee_grid"
  datasource="CompanyInfo"
  tablename="Employee">

Save the file as handle_grid.cfm.
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

<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.

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 allows you to handle any errors that arise.

To update the data source with cfquery:

Open a new file in ColdFusion Studio.

Modify the file so that it appears as follows:

<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=#Form.employee_grid.original.Emp_ID
[Counter]#
      </cfquery>

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

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

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

</body>
</html>

Rename your existing handle_grid.cfm file if you wish to save it, then save this file as handle_grid.cfm.
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 the table needs changing. Otherwise, do nothing. Loop 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>
Display 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, generate 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, generate 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, generate a SQL INSERT query to insert the Employee's last name and department ID from the grid row into the database. The Insert operation 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>
Close the cfif tag used to select among deleting, updating, and inserting.
Close the loop used for each row to be changed.
Close the cfif tag that surrounds all the active code.

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 the table needs changing. Otherwise, do nothing. Loop 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>	Display 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, generate 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, generate 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, generate a SQL INSERT query to insert the Employee's last name and department ID from the grid row into the database. The Insert operation 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>	Close the `cfif` tag used to select among deleting, updating, and inserting. Close the loop used for each row to be changed. Close the `cfif` tag that surrounds all the active code.

LiveDocs comments are not longer enabled for ColdFusion 5.0. Please use one of the following resources instead.

Version 5.0

Developing ColdFusion Applications
Building Dynamic Forms