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:
cfgrid
form variables. In that page perform cfquery
operations to update data source records base on the form values returned by cfgrid
.
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 using the cfgrid
control is fairly straightforward, but here are a few tips:
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:
type
attribute to require numeric or string data, to make the fields check boxes, or to display an image.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.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.
ColdFusion creates the following arrays as Form variables to return edits to grid rows and cells:
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]>
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. |
grid1.cfm
in ColdFusion Studio.
<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>
grid2.cfm
.The following table describes the code and its function:
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:
<cfgridupdate grid="Employee_grid" datasource="CompanyInfo" tablename="Employee">
handle_grid.cfm
.grid2.cfm
in your browser, make changes to the grid, and then submit them.The following table describes the code and its function:
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.
<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>
grid2.cfm
in your browser, make changes to the grid, and then submit them.The following table describes the code and its function: