CFLib.org – Common Function Library Project

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

Last updated October 18, 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>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson