ColdFusion Day 60:

ColdFusion Day 60:

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

Using the CFGRIDUPDATE tag, you can quickly and easily reflect changes - including edits, additions, and deletions - back to a database. This tag should be used in the template to which the form containing the grid is being submitted.

The CFGRIDUPDATE 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 handle changes that are likely to cause errors. When an error is encountered, all updating tops, leaving some changes made but others undone without any easy way to determine the state in which the data in the database has been left.

* With the CFGRIDUPDATE 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.

At its most basic, three attributes are required to make CFGRIDUPDATE work:

GRID Name of the grid to be processed.

DATASOURCE Data source containing the table to be altered.

TABLENAME Name of the table to update.

Therefore, if you use the employee example, the following template creates a form with a grid for editing employee data:

SELECT *

FROM Employees

ORDER BY ID

</CFQUERY>

</CFGRID>

<BR>

</CFFORM>

This template produces the form and grid shown in Figure 14.28.

FIGURE 14.28: A form with a grid ready for editing and submission

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

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

| 1 | Danesh | Arman | M | $10000.00|

| 2 | Smith | John | M | $7500.00|

| 3 | Doe | Jane | F | $6200.00|

| 4 | Johnson | Mary | F | $9900.00|

| 5 | Danesh | Joe | M | $10000.00|

| 6 | Danesh | Kevin | M | $10000.00|

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

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

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

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

| | Submit Query | |

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

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

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:

The subject of how CFGRIDUPDATE handles updates to existing records needs to be addressed. As you know from using the SQL UPDATE statement, 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 KEYONLY attribute, which is No by default. Setting it to Yes causes records being updated to be identified by keys as well as by the original values of any fields that are being changed in the record.

The CFGRIDUPDATE tag can also take other optional attributes, which may be needed at times but which are not used in most cases:

DBTYPE Specifies which type of database is being used (ODBC, ORACLE73 for Oracle 73 native driver,

ORACLE80 for Oracle 8 native driver, or Sybase11 for SybaseSystem11 native driver).

DBSERVER Overrides the specified database server for the datasource when using native drivers.

DBNAME Overrides the specified database name for the datasource when using native drivers.

USERNAME Overrides the specified username for the data source when using an ODBC data source.

PASSWORD Overrides the specified password for the data source when using an ODBC 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.

If you are doing your development and testing against Microsoft Access databases as you are doing in this book, it is unlikely that you will need to use any of these attributes. If you are working against databases that support these features (such as Oracle or SQL Server), consult the documentation for the database system to clarify such subjects as table ownership and table qualifiers.

UPDATING WITH CFQUERY TAGS

Although the CFGRIDUPDATE tag is easy to use, at times it is beneficial to work through your own CFQUERY tags to handle the deletions, insertions, and updates. Among other benefits, using CFQUERY tags make it possible to build more complex SQL statements and effectively handle grid data from multiple tables in a database.

To work manually with the data provided by a grid when a form is submitted, you first need to understand how to access information from an editable grid.

When an editable grid is submitted, three types of arrays become available in the receiving template, as indicated in the following list.

Form.GridName.ColumnName[RowIndex] Reflects the new values of cells from a particular column.

Form.GridName.Original.ColumnName[RowIndex] Reflects the original value of cells for a particular column.

Form.GridName.RowStatus.Action[RowIndex] Reflects the type of change made to a particular row. Possible values are U for updates, I for Inserts, and D for deletions.

By combining the information from these three arrays it is possible to do the following:

* Determine edits made to particular cells

* Determine which records are new

* Determine which records need deleting

* Build SQL statements to execute all the changes

For instance, in our employees example, if Form.GridTest.RowStatus.Action[1] is set to update and Form.GridTest.LastName[1] differs in value from Form.GridTest.Original.LastName[1], then we know that the user has edited the last name field of the record in the first row of the grid. You can then build an effective SQL query to update the record:

UPDATE Employees

SET LastName=' #Form.GridTest.LastName[1]# '

WHERE ID=#Form.GridTest.Original.ID[1]#

AND LastName=' #Form.GridTest.Original.LastName[1]# '

To further highlight how to build those queries, the following template generates the necessary updates to our employee database using CFQUERY tags and SQL statements:

DELETE FROM Employees

WHERE ID=#Form.GridTest.Original.ID[Row]#

</CFQUERY.

</CFCASE>

UPDATE Employees

SET LastName= ' #Form.GridTest.LastName[Row}# ' ,

FirstName= ' #Form.GridTest.FirstName[Row}# ' ,

Gender= ' #Form.GridTest.Gender[Row}# ' ,

Salary= ' #Form.GridTest.Salary[Row}# '

WHERE ID=#Form.GridTest.Original.ID[Row] #

</CFQUERY>

</CFCASE>

INSERT INTO Employees (LastName, FirstName, Gender, Salary)

VALUES (' #Form.GridTest.LastName[Row]# ' , ' #Form.GridTest.FirstName[Row]# ' , ' #Form.GridTest.Gender[Row]# ' , ' #Form.GridTest.Salary[Row]#)

</CFQUERY>

</CFCASE>

</CFSWITCH>

</CFLOOP>

The concept here is really quite simple. You loop through all the rows from the grid by using ArrayLen, which determines how many rows are in the grid by testing Form.GridTest.RowStatus.Action. Within the loop, you use a switchcase construct to handle all three possible actions: delete, update, and insert.

Back Next