Updating with the CFGRIDUPDA TE Tag ColdFusion Help

Using the CFGRIDU PATE tag you can quickly and easily reflect changes-including edits additions and deletions-back to a dat?p>ise. This tag should be used in the template to which the form containing the grid is mg submitted.

The CFGRIDUP DATE tag makes the job of processing multiple changes by a user easy but it has some limitations:

• It is designed to update a single table. More complex grids may be built out of a query that draws data from multiple tables. CFGRIDUPDATE cannot update all these tables in a single tag.

• CFGRIDUPDATE is not ideally suited to handling changes that are likely to cause errors. ””’hen an error is encountered, all updating stops leaving some changes made but others undone without any  determine the state in which the data in the database has been left.
• With the CFGRIDUP DATE tag you have no control over the order in which changes are made: Row deletions happen first row insertions next, and finally updates to existing rows are executed.

FIGURE 15.37

FIGURE 15.37

Next in the submit. cfm template to which the form is being submitted you can reflect all user changes back to the employee database by using the following tag:

<CFGRIDUP DATE GRID -‘Grid Test’ DATA SOURCE-‘Employee Data’ TABLE NAME-‘Employees’)

The subject of how CFGRIDUPDATE handles updates to existing records needs to be addressed. As you know from using the SQL UPDATEstatement, to effectively update a single record in a database you need to be able to identify it uniquely by using one or
more fields in the record. Generally, this is done using the primary key that is usually unique for each record in the table.

However there may be times when identifying a record strictly by the primary key is insufficient. By default, CFGRIDUPDATE uses only the primary key value to identify the record being updated. However, you can alter this behavior with the KEY ONLY attribute which is NO by default. Setting it to YEScauses records being updated to be identified b} keys as well as by the original values of any fields that are being changed in the record.

The CFGRIDUPDATtaEg can also take other optional attributes, which may be needed i”lt times but which are not used in most cases:

DBTYPE Specifies which type of database is being used (OOB(,Oracl e73 for Orarle 7.3 native driver, Oracl e80 for Oracle 8 native driver, or Sybasell for Syba e Sy~t{T’ 11 native driver)
OBSERVER Overrides the specified database server for the data source when using native drivers
DBNAME Overrides the specified database name for the data’source when using native drivers
USERNAME Overrides the specified usemame for the data source when using an OOBC data source
PASSWORDO verrides the specified password for the data source when using an OOBC data source
TABLEOWNER Specifies the table owner for the data source that supports table ownership
TABLEQUALIFIER Specifies the table qualifier for data sources that support qualifiers PROVIDER Specifies the COM provider for OLE-DB
PROVIDERDSN Specifies the data source name for the COM provider for OLE-DB KEYONLYWhen set to YES, specifies that in the update action, the WHEREcriteria is limited to key values. When set to NO,indicates that key values plus the original values of changes fields are included in the WHEREcriteria. The default value is YES.

Grid Formatting

Now that you have finished with the heart of grids-creating them, populating them with useful information, and then allowing the user to work with the information-you -will turn to the cosmetic: attributes that enable you to control the appearance of grids.

Other Features

Some other features of grid controls warrant brief discussion:

• Associating URLs with grid items
• Including images in grid cells
• Manually populating grids

Associating URL swith Grid Items

When building grid controls you can associate a URL with the cells in a column by using the HREF attribute. You can do this in two ways: by specifying a URL or by specifying another column from the query result that contains URLs

In either case the entries in the particular column will be presented as clickable URLs and a URL attribute called CFGRID KEY will be added to the end of the URL containing information that is dependent on the selection mode being used in the grid. Table 15.2
shows possible selection modes and their effect on the CFGRIDKEYURL attribute.

TAB LE 1 5 . 2:

TAB LE 1 5 . 2:

When a user clicks a URL, the relevant value is then. passed to the template that can access it in the same way that any other URL attribute is accessed.

Entries in a grid are converted into URLs by using the HREF attribute of the CFGRID COLUMN tag. This attribute can specify an absolute URL, a relative URL, or the name of a query column containing U~Ls.

For instance if in our employee examples you have a template called employee deta i 1s. cfm that displays an employee’s details, then you can use HREF-employee deta i 1s . cfm” in the appropriate column’s CFGRID COLUMN tag to create the URLs. For instance, consider the following form template

<CFQUERYNAME-’employees’ DATASOURCE-‘EmployeeData’)
SELECT *
FROM Employees
ORDERBY ID
</CFQUERY>

<CFFORM ACTlON-‘submit.cfm’ NAME-‘Test’>
<CFGRlD NAME-‘GridTest’ QUERY-‘Employees’ SELECTMODE-‘ Row’ SORT-‘Yes’>
<CFGRlDCOLUMN NAME-‘lD’ HREF-’employeedetails.cfm’>
<CFGRlDCOLUMN NAME-‘LastName’ HEADER-‘Last Name’>
<CFGRlDCOLUMN NAME-‘FirstName’ HEADER-‘First Name’>
<CFGRlDCOLUMN NAME-‘Gender’>
<CFGRlDCOLUMN NAME-‘Salary’ HEADER-‘Monthly Salary’ DATAALIGN-‘Right’
NUMBERFORMAT-‘S_._.OO’>
</CFGRlD>
</CFFORM>

In this template the grid column for the employee ID uses the HREF tag to make it into a URL. The results look like those in Figure 15.38

FIGURE 15.38

FIGURE 15.38

If you look carefully you can see that each of the entries in the ID column is an underlined link as opposed to plain text. Each is a link to the employee detai 1s. cfm template

<Hl>Employee #<CFOUTPUT>#ListGetAt(URL.CFGRlDKEY.l)#</CFOUTPUT></Hl>
<STRONG>Last Name</STRONG>: <CFOUTPUT>#ListGetAt(URL.CFGRIDKEY.2)#</CFOUTPUT><BR> .
<STRONG>First Name</STRONG>: <CFOUTPUT>#ListGetAt(URL.CFGRIDKEY.3)#</CFOUTPUT><BR>
<STRONG>Gender</STRONG>: <CFOUTPUT>#ListGetAt(URL.CFGRlDKEY.4)#</CF.OUTPUT><BR>
<STRONG>Salary</STRONG>: S<CFOUTPUT>#ListGetAt(URL.CFGRlDKEY.5)#</CFOUTPUT>

Notice the use of List Get At here to obtain individual values from URL CFGRID KEY. Because this URL attribute contains a comma-separated list of values in the selected row you can treat it as a regular list variable and pull individual values out of it using Li stGetAt. Details of working with lists are covered in Chapter 14

When you click one of the links in the grid the employee deta i is . cfm template opens and displays the selected data as shown in Figure 15.39

FIGURE 15.39

FIGURE 15.39

Another use of the HREF attribute is to link a record in a grid to a URL stored with it in the database. For instance, if your employee database table had another column called Home Page containing the URL of the employee’s home page, you could link their records o their home page with the following CFGRID COLUM tag

<CFGRlDCOLUMN NAME-“lD” HREF-“HomePage”>

Because Home Page is a field in your query result now, the value of this field will be treated as the URL for the link. The CFGRID KEY attribute will still be appended to the URL.

Including Images in Grid Cells

When using grids, it is possible to have a cell’s contents interpreted as an image and displayed accordingly. ColdFusion does this by attempting to interpret the field’s content and display either a built-in image or a custom image accordingly.

If the specified field contains one of the following values, then a built-in image is displayed in the cell that matches the meaning suggested by the value: cd. computer

document element floppy folder remote. Figure 13.40 shows a grid demonstrating these images

FIGURE 15.40

FIGURE 15.40

FIGURE 15.41

FIGURE 15.41

Manually Populating Grids

The last aspect of grids you will look at is how to create grids from data other than query results. The CFGRIDROW tag enables data to be manually inserted into a grid.

There are three’steps to manually populating a grid.Pirst, you need to create your grid by using CFGRID. However because the grid will be manually populated no query needs to be specified:

<CFGRID NAME ••.G.ridTest .>.

Second you need to define the columns that will be displayed in the grid by using CFGRI DCOLUMN. For instance to create a two-column grid for displaying first and last names you could use

<CFGRIDCOlUMN NAME=’lastName’ HEADER=’last Name”>
<CFGRIDCOlUMN NAME~’FirstName’ HEAOER=”First Name’)

In this case the NAME attribute doesn’t specify the query column to display but rather specifies the column name for use after the form is submitted and it is necessary to process the selections in the grid.

Finally you need to populate the grid with rows of data For each row one CFGRID ROW tag should be used. The tag takes a single attribute called DATA which takes as its value

a comma-separated list of values. The list should contain the same number of elements as there are columns in the grid and entries in the list should reflect the order of columns in the grid.

For instance you can populate the grid you have just created with three rows as follows:

<CFGRIDROW DATA-‘Danesh,Arman’>
<CFGRIDROW DATAz’Smith,John’>
<CFGRIDROW DATA=’Doe,Jane”>

Put together the code for the grid looks~ike this:

<CFGRID NAME=’GridTest”>
<CFGRIDCOLUMN NAME=”LastName” HEADER=’Last Name’>
<CFGRIDCOLUMN NAMEz”FirstName’ HEADER-‘First Name’>
<CFGRIDROW DATA=’Danesh,Arman’>
<CFGRIDROW DATA-‘Smith,John’>
<CFGRIDROW DATA=’Doe,Jane’>
</CFGRID>

The resulting grid is shown in Figure 15.42

FIGURE 15.42

FIGURE 15.42

Using CFTREE to Create Trees

The final Java control you will look at is the CFTREE control. Using CFTREE you can build a Windows Explorer-like hierarchical tree like the one shown in Figure 15.43

FIGURE 15.43

FIGURE 15.43

Here you specify that the Name value from the query named Employees should be used to create an entry in the tree and that the entry is the root level of the tree. The IMG.

tag indicates that a folder icon should be used for the first level and a document icon for the sub-entries under first-level items.

This use of folder and document icons corresponds to the figure: The root item of the tree is labeled with the query name and this item has as its children the values of the Name field in the result set.

FIGURE 15.44

FIGURE 15.44

Posted on November 17, 2015 in Forms

Share the Story

Back to Top
Share This