Developing ColdFusion MX Applications with CFML
Building Dynamic Forms

Creating data grids with cfgrid

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:

• Sort data in the grid alphanumerically
• Update, insert, and delete data
• Display images in the grid

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.

Working with a data grid and entering data

The following figure shows an example grid created using the cfgrid tag:

The following table describes some navigating tips:

Action	Procedure
Sorting grid rows	Double-click the column header to sort a column in ascending order. Double-click again to sort the rows in descending order.
Rearranging columns	Click any column heading and drag the column to a new position.
Determining editable grid areas	When you click an editable cell, it is surrounded by a yellow box.
Determining noneditable grid areas	When you click a cell (or row or column) that you cannot edit, its background color changes. The default color is salmon pink.
Editing a grid cell	Double-click the cell. You must press Return when you finish entering the data.
Deleting a row	Click any cell in the row and click the Delete button.
Inserting a row	Click the Insert button. An empty row appears at the bottom of the grid. To enter a value in each cell, double-click the cell, enter the value, and click Return.

To populate a grid from a query:

1. Create a new ColdFusion page named grid1.cfm with the following contents:

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

2. Save the file and view it in your browser. The following figure shows the output of this code:

   

Reviewing the code

The following table describes the highlighted code and its function:

Code	Description
<cfgrid name="employee_grid" query="empdata"	Create a grid named "employee_grid" and populate it with the results of the query "empdata". If you specify a cfgrid tag with a query attribute defined and no corresponding cfgridcolumn attributes, the grid contains all the columns in the query.
selectmode="single">	Allow the user to select only one cell. Other modes are row, column, and edit.
<cfgridcolumn name="Emp_ID">	Put the contents of the Emp_ID column in the query results in the first column of the grid.
<cfgridcolumn name="LastName">	Put the contents of the LastName column in the query results in the second column of the grid.
<cfgridcolumn name="Dept_ID">	Put the contents of the Dept_ID column in the query results in the third column of the grid.

Creating an editable 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.

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:

• Create a page to which you pass the cfgrid form variables. In that page perform cfquery operations to update data source records base on the form values returned by cfgrid.
• Pass grid edits to a page that includes the 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.

Controlling cell contents

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:

• By default, a cell is not editable. Use the cfgrid attribute selectmode="edit" to edit cell contents.
• Use the type attribute to control sorting order, to make the fields check boxes, or to display an image.
• Use the 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.
• While 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.

How user edits are returned

ColdFusion creates the following arrays as Form variables to return edits to grid rows and cells:

Array reference	Description
gridname.colname[change_index]	Stores the new value of an edited cell.
gridname.Original.colname [change_index]	Stores the original value of the edited grid cell.
gridname.RowStatus.Action [change_index]	Stores the edit type made to the edited grid row: D for delete, I for insert, or U for update.

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:

• The original values for all columns
• The new column values
• The type of change

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]

Editing data in cfgrid

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.

To make the grid editable:

1. Create a new ColdFusion page with the following contents:

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

2. Save the file as grid2.cfm and view it in your browser.

   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.

Reviewing the code

The following table describes the code and its function:

Code	Description
<cfgrid name="employee_grid" height=425 width=300 vspace=10 selectmode="edit" query="empdata" insert="Yes" delete="Yes">	Populates a cfgrid control with data from the empdata query. Selecting a grid cell enables you to edit it. You can insert and delete rows. The grid is 425 X 300 pixels and has 10 pixels of space above and below it.
<cfgridcolumn name="Emp_ID" header="Emp ID" width=50 headeralign="center" headerbold="Yes" select="No">	Creates a 50-pixel wide column for the data in the Emp_ID column of the data source. Center a header named Emp ID and make it bold. Does not allow users to select fields in this column for editing. Since this field is the table's primary key, users should not be able to change it for existing records and the DBMS should generate this field as an automincrement value.
<cfgridcolumn name="LastName" header="Last Name" width=100 headeralign="center" headerbold="Yes">	Creates a 100-pixel wide column for the data in the LastName column of the data source. Center a header named Last Name and make it bold.
<cfgridcolumn name="Dept_ID" header="Dept" width=35 headeralign="center" headerbold="Yes">	Creates a 35-pixel wide column for the data in the Dept_ID column of the data source. Center a header named Dept and make it bold.

Updating the database with cfgridupdate

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:

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

To update the data source with cfgridupdate:

1. Create a file ColdFusion page with the following contents:

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

2. Save the file as handle_grid.cfm.
3. View grid2.cfm in your browser, make changes to the grid, and then submit them.

Note:   To update a grid cell, modify the cell contents, then press Return.

Reviewing the code

The following table describes the highlighed code and its function:

Code	Description
<cfgridupdate grid="employee_grid"	Update the database from the Employee_grid grid.
datasource="CompanyInfo"	Update the CompanyInfo data source.
tablename="Employee"	Update the Employee table.

Updating the database with cfquery

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.

To update the data source with cfquery:

1. Create a ColdFusion page with the following content:

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

2. Rename your existing handle_grid.cfm file as handle_grid2.cfm to save it, then save this file as handle_grid.cfm.
3. View grid2.cfm in your browser, make changes to the grid, and then submit them.

Reviewing the code

The following table describes the code and its function:

Code	Description
<cfif isdefined   ("Form.employee_grid.rowstatus.action")>   <cfloop index = "Counter" from = "1" to =   #arraylen(Form.employee_grid.rowstatus.action)#>	If there is an array of edit types, then change the table. Otherwise, do nothing. Loops through the remaining code once for each row to be changed. Counter is the common index into the arrays of change information for the row being changed.
<cfoutput>   The row action for #Counter# is:   #Form.employee_grid.rowstatus.action[Counter]#   <br> </cfoutput>	Displays the action code for this row: U, I, or D.
<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>	If the action is to delete a row, generates a SQL DELETE query specifying the Emp_ID (the primary key) of the row to be deleted.
<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>	Otherwise, if the action is to update a row, generates a SQL UPDATE query to update the LastName and Dept_ID fields for the row specified by the Emp_ID primary table key.
<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>	Otherwise, if the action is to insert a row, generates a SQL INSERT query to insert the employee's last name and department ID from the grid row into the database. The INSERT assumes that the DBMS automatically increments the Emp_ID primary key. If you use the Dbase version of the CompanyInfo database that is provided for UNIX installations, the record is inserted without an Emp_ID number.
</cfif>  </cfloop> </cfif>	Closes the cfif tag used to select among deleting, updating, and inserting. Closes the loop used for each row to be changed. Closes the cfif tag that surrounds all the active code.

Copyright © 2002, Macromedia Inc. All rights reserved.

Comments