Adobe ColdFusion 8

Updating the database with the cfquery tag

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.

Update the data source with the cfquery tag

  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="cfdocexamples">
                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="cfdocexamples">
                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="cfdocexamples">
                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, and then save this file as handle_grid.cfm.
  3. View the grid2.cfm page 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, changes the table. Otherwise, does nothing. Loops through the remaining code once for each row to be changed. The counter variable 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 for update, I for insert, or D for delete.
<cfif Form.employee_grid.rowstatus.action[counter]    is "D">  <cfquery name="DeleteExistingEmployee"    datasource="cfdocexamples">   DELETE FROM Employee   WHERE Emp_ID=<cfqueryparam    value="#Form.employee_grid.original.Emp_ID     [counter]#"    CFSQLType="CF_SQL_INTEGER" >  </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="cfdocexamples">   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>

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="cfdocexamples">   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>

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 statement assumes that the DBMS automatically increments the Emp_ID primary key. If you use the version of the cfdocexamples 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.