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

- Create a page to which you pass the CFGRID form variables and in that page perform CFQUERY operations to update data source records.
- Pass grid edits to a page that includes the CFGRIDUPDATE tag, which passes data directly to the data source.

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.

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

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

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