CFLib.org – Common Function Library Project

querySetRow(query, columnlist, valuelist[, rownumber][, delimiter][, trimElements])

Last updated October 19, 2005

author

Ell Cord

Version: 1 | Requires: CF6 | Library: DataManipulationLib

Description:
This function allows you to set the values for multiple columns in the specified query row, with a single function call. If a row number is not specified, the column values will be appended to the end of the query as a new row. Useful for development. Similar to using QueryAddRow() plus multiple QuerySetCell() calls.

Return Values:
Returns void..

Example:

<!--- add (3) new rows to a query --->
<cfset myQuery = QueryNew("UserID,FirstName,LastName,UserName")>
<cfset QuerySetRow(myQuery, "UserID,FirstName,LastName,UserName", "1,Amelia,Jones,ajones")>
<cfset QuerySetRow(myQuery, "UserID,FirstName,LastName,UserName", "2,Roberta,Freeman,rfreeman")>
<cfset QuerySetRow(myQuery, "UserID,FirstName,LastName,UserName", "3,Harriet, Adams ,hadams")>
<cfdump var="#myQuery#">

<!--- update the column values for row number (2) --->
<cfset QuerySetRow(myQuery, "FirstName,UserName", "Alfred,AAfreeman", 2)>
<cfdump var="#myQuery#">

Parameters:

Name Description Required
query Query to manipulate. Yes
columnlist List of columns to update. Yes
valuelist Values for the new data. Yes
rownumber Row number to modify. If not specified, row is added to end of query. No
delimiter Delimiter for columnlist and valuelist. Defaults to a comma. No
trimElements If true, trims all values. Defaults to true. No

Full UDF Source:

<!---
 Sets the values for one or more columns in the specified query row.
 
 @param query      Query to manipulate. (Required)
 @param columnlist      List of columns to update. (Required)
 @param valuelist      Values for the new data. (Required)
 @param rownumber      Row number to modify. If not specified, row is added to end of query. (Optional)
 @param delimiter      Delimiter for columnlist and valuelist. Defaults to a comma. (Optional)
 @param trimElements      If true, trims all values. Defaults to true. (Optional)
 @return Returns void.. 
 @author Ell Cord (lunareclipse13@earthlink.net) 
 @version 1, October 18, 2005 
--->
<cffunction name="querySetRow" returntype="void" output="false">
    <cfargument name="query" type="query" required="true" />
    <cfargument name="columnList" type="string" required="true" />
    <cfargument name="valuesList" type="string" required="true" />
    <cfargument name="rowNumber" type="numeric" required="false" default="0" />
    <cfargument name="delimiter" type="string" required="false"  default="," />
    <cfargument name="trimElements" type="boolean" required="false"  default="true" />
    
    <cfset var i            = 0>
    <cfset var col           = "">
    <cfset var value    = "">
    
    <cfif arguments.rowNumber gt 0 and arguments.rowNumber gt arguments.query.recordCount>
        <cfthrow type="InvalidArgument" message="Invalid rowNumber [#arguments.rowNumber#]. The specified query contains [#arguments.query.RecordCount#] records.">
    </cfif>    
    <cfif ListLen(arguments.columnList, arguments.delimiter) NEQ ListLen(arguments.valuesList, arguments.delimiter)>
        <cfthrow type="InvalidArgument" message="[columnList] and [valuesList] do not contain the same number of elements.">
    </cfif>    
    
    <cfscript>
        //by default, append new row to end of query
        if (val(arguments.rowNumber) lt 1) {
            QueryAddRow(arguments.query, 1);
            rowNumber = arguments.query.recordCount;
        }
        
        //set values for each column
        for (i = 1; i lte ListLen(arguments.columnList, arguments.delimiter); i = i + 1) {
            if (arguments.trimElements) {    
                col   = Trim(ListGetAt(arguments.columnList, i, arguments.delimiter));    
                value = Trim(ListGetAt(arguments.valuesList, i, arguments.delimiter));    
            }
            else {
                col   = ListGetAt(arguments.columnList, i, arguments.delimiter);    
                value = ListGetAt(arguments.valuesList, i, arguments.delimiter);    
            }
            query[col][arguments.rowNumber] = value;
        }
    </cfscript>
</cffunction>

Search CFLib.org


Latest Additions

Raymond Camden added
QueryDeleteRows
November 04, 2017

Leigh added
nullPad
May 11, 2016

Raymond Camden added
stripHTML
May 10, 2016

Kevin Cotton added
date2ExcelDate
May 05, 2016

Raymond Camden added
CapFirst
April 25, 2016

Created by Raymond Camden / Design by Justin Johnson