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 cfgridcolumndisplay="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.

2. Save the file and view it in your browser.

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 cfgridcolumntype attribute to control sorting order, to make the fields check boxes, or to display an image.

• Use the cfgridcolumnvalues 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 valuesvaluesDisplay 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.

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:

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.col2[1] and Form.mygrid.original.col2[1]) have the original, unchanged values.

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.

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.

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.