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 then use either of two ways to use an updateable grid to make changes to your ColdFusion data sources.

Although using CFQUERY gives you complete control over interactions with your data source, CFGRIDUPDATE provides a much simpler interface for operations that do not require the same level of control.

Editing data in a CFGRID

To enable grid editing, you use the SELECTMODE="EDIT" attribute. When enabled, a user can edit cell data and insert or delete grid rows. When a CFFORM containing a CFGRID is submitted, data about changes to grid cells are stored in one-dimensional arrays you can reference like any other ColdFusion array.

Note To make the grid editable:
  1. Open the file grid1.cfm in Studio.
  2. Edit the file so that it appears as follows:
    <CFQUERY NAME="empdata" DATASOURCE="CompanyInfo">
        SELECT * FROM Employees
    </CFQUERY>
    
    <CFFORM NAME="GridForm"
        ACTION="handle_grid.cfm">
    
        <CFGRID NAME="employee_grid"
            HEIGHT=170
            WIDTH=400
            HSPACE=10
            VSPACE=6
            ALIGN="LEFT"
            SELECTCOLOR="white"
            SELECTMODE="edit"
            ROWHEADERS="YES"
            ROWHEADERWIDTH=25
            ROWHEADERALIGN="right"
            COLHEADERS="YES"
            QUERY="empdata"
            GRIDDATAALIGN="left"
            BGCOLOR="yellow"
            INSERT="YES"
            DELETE="YES"
            SORT="YES"
            MAXROWS=60>
    
            <CFGRIDCOLUMN NAME="Employee_ID"
                HEADER="Employee ID"
                WIDTH=80
                ITALIC="NO"
                HEADERALIGN="center"
                HEADERITALIC="NO"
                HEADERBOLD="YES"
                DISPLAY="NO">
    
            <CFGRIDCOLUMN NAME="LastName"
                HEADER="Last Name"
                WIDTH=80
                ITALIC="NO"
                HEADERALIGN="center"
                HEADERITALIC="NO"
                HEADERBOLD="YES"
                DISPLAY="YES"
                SELECT="YES">
    
            <CFGRIDCOLUMN NAME="Department_ID"
                HEADER="Department"
                WIDTH=240
                ITALIC="No"
                HEADERALIGN="center"
                HEADERITALIC="No"
                HEADERBOLD="Yes"
                BOLD="Yes"
                DISPLAY="Yes">
    
        </CFGRID>
    
    <INPUT TYPE="Submit" VALUE="Submit">
    
    </CFFORM>
    
  3. Save the file as grid2.cfm.
Note To update the data source with CFQUERY:
  1. Open a new file in 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><BR>
        </CFOUTPUT>
    
        <CFIF form.employee_grid.rowstatus.action[Counter] IS "D">
        
        <CFQUERY NAME="DeleteExistingEmployee" 
            DATASOURCE="CompanyInfo">
            DELETE FROM Employees
            WHERE 
    Employee_ID=#form.employee_grid.original.Employee_ID[Counter]#
        </CFQUERY>
    
    <CFELSEIF form.employee_grid.rowstatus.action[Counter] IS "U">
    
        <CFQUERY NAME="UpdateExistingEmployee"
            DATASOURCE="CompanyInfo">
            UPDATE Employees
            SET 
            LastName='#form.employee_grid.LastName[Counter]#',
            Department_ID=#form.employee_grid.Department_ID[Counter]#
            WHERE
            
    Employee_ID=#form.employee_grid.original.Employee_ID[Counter]#
        </CFQUERY>
    
    <CFELSEIF form.employee_grid.rowstatus.action[Counter] IS "I">
    
        <CFQUERY NAME="InsertNewEmployee"
            DATASOURCE="CompanyInfo">
            INSERT into Employees
            (Employee_ID, LastName, Department_ID)
            VALUES (#form.employee_grid.Employee_ID[Counter]#,
            '#form.employee_grid.LastName[Counter]#', 
    #form.employee_grid.Department_ID[Counter]#)
        </CFQUERY>
    
        </CFIF>
        </CFLOOP>
    </CFIF>
    
    </BODY>
    </HTML>
    
  3. Save the file as handle_grid.cfm.
  4. View grid2.cfm in your browser, make changes to the grid, and then submit them.
Note To update the data source with CFQUERY
  1. Open a new file in Studio.
  2. Modify the file so that it appears as follows:
    <CFGRIDUPDATE GRID="Employee_grid"
        DATASOURCE="CompanyInfo"
        TABLENAME="Employees"
        KEYONLY="NO">
    
  3. Save the file as handle_grid.cfm.
  4. View grid2.cfm in your browser, make changes to the grid, and then submit them.

How user edits are stored

The following arrays are created to keep track of edits to grid rows and cells:

Arrays Used to Store Grid Cell Edit Information 
Array reference Description
gridname.colname [ row_index ]
Stores the new value of an edited grid cell
gridname.Original.colname [ row_index ]
Stores the original value of the edited grid cell
gridname.RowStatus.Action [ row_index ]
Stores the edit type made against the edited grid cell.

For example, you have an updateable CFGRID called "mygrid" consisting of two displayable columns, col1, col2, and one hidden column, col3. When an end user selects and changes data in a row, arrays are created to store the original values for all columns as well as the new column values for rows that have been updated, inserted, or deleted.

mygrid.col1[ row_index ]
mygrid.col2[ row_index ]
mygrid.col3[ row_index ]
mygrid.original.col1[ row_index ]
mygrid.original.col2[ row_index ]
mygrid.original.col3[ row_index ]

Where row_index is the array index containing the grid data.

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

<CFSET edittype = mygrid.RowStatus.Action[1]><BR>
<CFSET new_value = mygrid.col2[1]><BR>
<CFSET old_value = mygrid.original.col2[1]>

Multi-row edits

The use of arrays to track changes allows ColdFusion to manage changes to more than one row in a CFGRID. ColdFusion coordinates entries in the arrays used to store edit type (Update, Insert, or Delete), with arrays that store original grid data and edited grid data. For each grid cell edit, an entry is created in the RowStatus array, and corresponding entries are made in the arrays that store the new cell value and the original cell value.