ColdFusion Day 59:

ColdFusion Day 59:

================

By default, the rows of the grid reflect the order in which records are returned in the query result set (in our case, the query is ordered by employee ID number).

However, grids can dynamically sort presented data based on the data in any column. To enable this feature, you need to enable sorting using the SORT attribute of the CFGRID tag. This attribute can be set to Yes or No (No is the default value) and when set to Yes causes ascending and descending sort buttons to be displayed, as shown in Figure 14.21.

To sort a column, click the column's header; this selects and highlights all elements in the column, as shown in Figure 14.22.

FIGURE 14.21: Grids can be sorted using sort buttons

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 1 | Danesh | Arman | $10000.00|

| 2 | Smith | John | $7500.00|

| 3 | Doe | Jane | $6200.00|

| 4 | Johnson | Mary | $9900.00|

| 5 | Danesh | Joe | $10000.00|

| 6 | Danesh | Kevin | $10000.00|

|================================================|

| ----------------- ------------------ |

| | A --> Z | | Z --> A | |

| ---------------- ------------------ |

=================================================

FIGURE 14.22: A selected column

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 1 | Danesh | Arman | $10000.00|

| 2 | Smith | John | $7500.00|

| 3 | Doe | Jane | $6200.00|

| 4 | Johnson | Mary | $9900.00|

| 5 | Danesh | Joe | $10000.00|

| 6 | Danesh | Kevin | $10000.00|

|================================================|

| ----------------- ------------------ |

| | A --> Z | | Z --> A | |

| ---------------- ------------------ |

=================================================

The next step is to click the appropriate sort button. For instance, to sort in descending order based on the data in the selected column, simply click the Z --> A button.

The buttons used to select ascending or descending orders for sorting can use custom text instead of the default A --> Z and Z --> A. Alternative text is specified using the SORTASCENDINGBUTTON attribute and the SORTDESCENDINGBUTTON attribute. Therefore, the CFGRID tag

produces a grid with the custom sort buttons shown in Figure 14.23.

FIGURE 14.23: Sort buttons can use customized labels

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 1 | Danesh | Arman | $10000.00|

| 2 | Smith | John | $7500.00|

| 3 | Doe | Jane | $6200.00|

| 4 | Johnson | Mary | $9900.00|

| 5 | Danesh | Joe | $10000.00|

| 6 | Danesh | Kevin | $10000.00|

|================================================|

| ----------------- ------------------ |

| ---------------- ------------------ |

=================================================

DATA SELECTION

The next step in working with grids is to move from browsing data to selecting data. You can specify three data selection modes by using the SELECTMODE attribute of the CFGRID tag. The three modes are:

Single Allows selection of a single cell in a grid.

Column Allows selection of an entire column in a grid.

Row Allows selection of an entire row in the grid.

Commonly, grid controls are used for selecting entire rows because this action represents the selection of a record from a database. Therefore, we will explain this in detail. refer to the ColdFusion documentation for more information on selecting single cells or columns.

The CFGRID tag for selecting entire rows requires the use of the SELECTMODE="Row" attribute. When the form containing this type of selection grid is submitted, the selected data becomes accessible through form variables that reflect the column names from the grid. each column's data for the selected record is reflected through the variable Form.GridName.ColumnName.

For example, consider the employee data examples. if you have a grid containing columns for all the fields in your table and the grid is named GridTest, then you can access the selected row's fields with the following five variables:

Form.GridTest.ID

Form.GridTest.LastName

Form.GridTest.FirstName

Form.GridTest.Gender

Form.GridTest.Salary

The last major application for grid control is data editing. When using SELECTMODE="Edit", you can allow users to edit the contents of a displayed grid and then process the information after the form is submitted, either by reflecting the changes back to the database used to create the grid or by performing other relevant actions.

When using this select mode, the user can highlight and change the values stored in the individual cells of the grid. Figure 14.24 shows a cell being edited.

In addition to simply updating existing content, you can allow users to add and delete rows from the grid and then have these changes reflected back to the database. The ability to add and delete rows is enabled by setting the INSERT and DELETE attributes to Yes. By default, they are No. Setting these attributes to Yes produces the results shown in Figure 14.25 with INSERT and DELETE buttons next to the sort buttons we discussed earlier.

FIGURE 14.24: Editing a grid cell

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 1 | Danesh | Arman | $10000.00|

| 2 | Smith | John | $7500.00|

| 3 | Doe | Jane | $6200.00|

| 4 | Johnson | Mary | $9900.00|

| 5 | Danesh | Joe | $10000.00|

| 6 | Danesh | Kevin | $10000.00|

|================================================|

| ----------------- ------------------ |

| ---------------- ------------------ |

=================================================

FIGURE 14.25: It is possible to insert and delete rows in a grid

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 1 | Danesh | Arman | $10000.00|

| 2 | Smith | John | $7500.00|

| 3 | Doe | Jane | $6200.00|

| 4 | Johnson | Mary | $9900.00|

| 5 | Danesh | Joe | $10000.00|

| 6 | Danesh | Kevin | $10000.00|

|================================================|

| ----------------- ------------------ --------------------- --------------------- |

| ---------------- ------------------ --------------------- --------------------- |

=================================================

When inserting a row, it is inserted as the last row of the grid.

WARNING The delete function acts without asking for confirmation. This can be problematic but not as dangerous as it sounds because the user has to submit the form to cause the deletion to be reflected back to the database.

As with sort buttons, it is possible to customize the INSERT and DELETE buttons using the INSERTBUTTON and DELETEBUTTON attributes. For instance, using INSERTBUTTON="Add Row" and DELETEBUTTON="Del. Row" in the CFGRID tag produces the buttons shown in Figure 14.26.

FIGURE 14.25: Customizing button labels

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 1 | Danesh | Arman | $10000.00|

| 2 | Smith | John | $7500.00|

| 3 | Doe | Jane | $6200.00|

| 4 | Johnson | Mary | $9900.00|

| 5 | Danesh | Joe | $10000.00|

| 6 | Danesh | Kevin | $10000.00|

|================================================|

| ----------------- ------------------ --------------------- --------------------- |

| ---------------- ------------------ --------------------- --------------------- |

=================================================

The primary purpose of editing grid data is to allow the user to directly edit data stored in a database. Given this, you need a way to reflect these changes back to the database when the form is submitted. this can be achieved in two ways:

Using the CFGRIDUPDATE tag

Using CFQUERY tags

Before looking at the process of moving user changes into the database, you need to consider the issue of key values. In order for our different methods of moving changes into the database to work, you need to be able to conclusively determine which records have been edited. The easiest way to do this is by being sure that you include the primary key of the table in question in your grid.

You included the primary key of your employee table in all the grid examples in this section. However, there may be compelling reasons to prevent display of a column while still including the values in the grid and the data submitted with the form. This is done by using the DISPLAY attribute of the CFGRIDCOLUMN tag. When the attribute is set to No, a column can be included in the grid but not displayed. By default, the DISPLAY attribute is set to Yes.

For instance, if you use

in producing your grid, you can produce a grid with the ID column hidden like the one in figure 14.27, but which includes the value of the ID fields from the database table.

FIGURE 14.27: You can hide the primary key from being displayed in the grid

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 1 | Danesh | Arman | $10000.00|

| 2 | Smith | John | $7500.00|

| 3 | Doe | Jane | $6200.00|

| 4 | Johnson | Mary | $9900.00|

| 5 | Danesh | Joe | $10000.00|

| 6 | Danesh | Kevin | $10000.00|

|================================================|

| ----------------- ------------------ --------------------- --------------------- |

| ---------------- ------------------ --------------------- --------------------- |

=================================================

Back Next