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:

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:

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:

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.

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

  1. Open the file grid1.cfm in ColdFusion Studio.
  2. 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>
    
  3. 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.

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:

To update the data source with cfgridupdate

  1. Open a new file in ColdFusion Studio.
  2. Modify the file so that it appears as follows:
    <cfgridupdate grid="Employee_grid"
      datasource="CompanyInfo"
      tablename="Employee">
    
  3. Save the file as handle_grid.cfm.
  4. 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.

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:

  1. Open a new file in ColdFusion Studio.
  2. 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>
    
  3. Rename your existing handle_grid.cfm file if you wish to save it, then save this file as handle_grid.cfm.
  4. 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.