QuerySetCell

QuerySetCell(query, column_name, value [, row_number ])

<!--- This example shows the use of QueryAddRow and QuerySetCell --->

<!--- start by making a query --->
<cfquery name = "GetCourses" datasource = "cfsnippets">
  SELECT Course_ID, Descript 
  FROM Courses
</cfquery>
<p>The Query "GetCourses" has <cfoutput>#GetCourses.RecordCount#</cfoutput> rows.

<cfset CountVar = 0>
<cfloop CONDITION = "CountVar LT 15">
  <cfset temp = QueryAddRow(GetCourses)>
  <cfset CountVar = CountVar + 1>
  <cfset Temp = QuerySetCell(GetCourses, "Number", 100*CountVar)>
  <cfset CountVar = CountVar + 1>
  <cfset Temp = QuerySetCell(GetCourses, "Descript", 
"Description of variable #Countvar#")>
</cfloop>

<P>After the QueryAddRow action, the query has
  <CFOUTPUT>#GetCourses.RecordCount#</CFOUTPUT> 
  records. 
  <CFOUTPUT query="GetCourses"> 
  <PRE>#Course_ID# #Course_Number# #Descript#</pre> </cfoutput>

Comments

Jeremy Eagan said on Aug 9, 2004 at 1:34 PM :

I tried to use QuerySetCell using a column name that is alowed in a query but isn't allowed in QuerySetCell apparently. The name was AVERAGE_CYCLE$$1. I guess is didn't like the dollar signs even though the dollar sign is alowed in a query name. Does anyone know if any other character is allowed in both. I need something to distinguish each Column name that normally won't show up. I was going to remove the $$1 later and used them to label the value in a table. I didn't want the distingisher to show up so in the table so I replace it with a empty string before writing to the table. So I have 3 Average Cycles values in my query.

halL said on Aug 10, 2004 at 1:42 PM :

In ColdFusion MX, query column names must conform to the following rules:
They must start with a letter and can only include letters, numbers, and underscores.

Jeremy Eagan said on Aug 10, 2004 at 2:33 PM :

Must is too stong of a word. Because the version I have ( 6,0,0,48097) will let you place a dollar sign in the query column name. Either a query of query or a query from a SQL server. It may not be supported but it is allowed. I can run the following code with not errors and it works fine.

<cfquery name="test" datasource="NonConTest">
SELECT Defects.program as Test$
FROM Defects
group by Defects.program
</cfquery>

<cfquery name="test2" dbtype="query">
select Test$ as Another$Test
from test
group by Test$
</cfquery>

halL said on Aug 10, 2004 at 2:45 PM :

ColdFusion MX is inconsistent in what it allows. Some code paths, such as thuose used by the querynew and querysetcell functions, test the variable name and reject names that don't conform to the limited rules.
Other code paths as shown by don't do this checking.

I expect, but have not confirmed, that any CF query function tests its parameters for conformance with the tight rules, while you can follow looser rules (possibly just the DB's naming rules) in any SQL statements in a cfquery tag.