The cfgrid
tag creates a cfform
grid control that resembles a spreadsheet table and can contain data populated from a cfquery
or from other sources of data. As with other cfform
tags, cfgrid
offers a wide range of data formatting options as well as the option of validating user selections with a JavaScript validation script.
You can also do the following tasks with cfgrid
:
Users can sort the grid entries in ascending order by double-clicking any column header. Double-clicking again sorts the grid in descending order. You can also add sort buttons to the grid control.
When users select grid data and submit the form, ColdFusion passes the selection information as form variables to the application page specified in the cfform
action
attribute.
Just as the cftree
tag uses cftreeitem
, cfgrid
uses the cfgridcolumn
and cfgridrow
tags. You can define a wide range of row and column formatting options, as well as a column name, data type, selection options, and so on. You use the cfgridcolumn
tag to define individual columns in the grid or associate a query column with a grid column.
Use the cfgridrow
tag to define a grid that does not use a query as the source for row data. If a query attribute is specified in cfgrid
, the cfgridrow
tags are ignored.
The cfgrid
tag provides many attributes that control grid behavior and appearance. This chapter describes only the most important of these attributes. For detailed information on these attributes, see CFML Reference.
The following figure shows an example grid created using the cfgrid
tag:
The following table describes some navigating tips:
<cfquery name="empdata" datasource="CompanyInfo"> 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><input type="Submit" value="Submit"> </cfform>
Note: Use the cfgridcolumn
display="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 cfgrid
.
The following table describes the highlighted code and its function:
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.
To let users add or delete grid rows, you also have to set the insert
or delete
attributes in cfgrid
to Yes. Setting insert
or delete
to Yes causes the cfgrid
tag to display insert and delete buttons as part of the grid, as the following figure shows:
You can use a grid in 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.
The value
, valuesDisplay,
and valuesDelimiter
attributes of the cfgridcolumn
tag let you control the data that a user can enter into a cfgrid
cell in the following ways:
cfgrid
attribute selectmode="edit"
to edit cell contents.
type
attribute to control sorting order, 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 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 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, and the RowStatus.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] 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]
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
tag containing a cfgrid
tag, 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 code brevity, the following example handles only three of the fields in the Employee table. A more realistic example would include, at a minimum, all seven table 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.
<cfquery name="empdata" datasource="CompanyInfo"> SELECT * FROM Employee </cfquery> <cfform name="GridForm" action="handle_grid.cfm"> <cfgrid name="employee_grid" height=425 width=300 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>
The following figure shows the output of this code:
The following sections describe how to write handle_grid.cfm to process user edits to the grid.
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, use the cfgridupdate
tag to update your database. However, this tag does not provide the complete SQL control that cfquery
provides. In particular, using the cfgridupdate
tag, you can make the following changes:
<html> <head> <title>Update grid values</title> </head> <body> <h3>Updating grid using cfgridupdate tag.</h3> <cfgridupdate grid="employee_grid" datasource="CompanyInfo" tablename="Employee"> Click <a href="grid2.cfm">here</a> to display updated grid. </body> </html>
Note: To update a grid cell, modify the cell contents, then press Return.
The following table describes the highlighed 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 lets you 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= <cfqueryparam value="#Form.employee_grid.original.Emp_ID[Counter]#" CFSQLType="CF_SQL_INTEGER" > </cfquery> <cfelseif Form.employee_grid.rowstatus.action[counter] is "U"> <cfquery name="UpdateExistingEmployee" datasource="CompanyInfo"> UPDATE Employee SET LastName= <cfqueryparam value="#Form.employee_grid.LastName[Counter]#" CFSQLType="CF_SQL_VARCHAR" >, Dept_ID= <cfqueryparam value="#Form.employee_grid.Dept_ID[Counter]#" CFSQLType="CF_SQL_INTEGER" > WHERE Emp_ID= <cfqueryparam value="#Form.employee_grid.original.Emp_ID[Counter]#" CFSQLType="CF_SQL_INTEGER"> </cfquery> <cfelseif Form.employee_grid.rowstatus.action[counter] is "I"> <cfquery name="InsertNewEmployee" datasource="CompanyInfo"> INSERT into Employee (LastName, Dept_ID) VALUES (<cfqueryparam value="#Form.employee_grid.LastName[Counter]#" CFSQLType="CF_SQL_VARCHAR" >, <cfqueryparam value="#Form.employee_grid.Dept_ID[Counter]#" CFSQLType="CF_SQL_INTEGER" >) </cfquery> </cfif> </cfloop> </cfif> Click <a href="grid2.cfm">here</a> to display updated grid. </body> </html>
The following table describes the code and its function: