I've worked at this for hours and I can't figure out why this doesn't work!
Here's the deal...
This is a function inside a cfc. I'm calling a function in another cfc that
returns a structure. One of the items in that structure is a query. I'm
trying to add 3 columns to the query using arrays and the QueryAddColumn
function. Then I make a copy of query using Duplicate(). However, when I
call for the copy in a Query of Queries I get this error:
Table named "records1SELECT" was not found in Memory. It is misspelled, or
the table is not defined.
If I comment out the three QueryAddColumn lines, it works fine! Why would
the copy work fine if the query not modified, but apparently not work if it
was?
Also, if I comment out everything from the query of queries on down and
change the function to output the original query instead of a boolean I can
see the additional columns added by the QueryAddColumns.
The entire code is listed below.
Thanks,
Jonathan
<cffunction name="LoadFormData" access="private" returntype="boolean"
output="no">
<cfargument name="LayoutID" type="numeric" required="true">
<cfargument name="applicationVars" type="struct"
required="true">
<cfargument name="sessionVars" type="struct"
required="true">
<cfset var i = "">
<cfset var sRecords = "">
<cfset var loopcount = 1>
<cfset var temp = "">
<!--- Run a query to get the layout data --->
<cfinvoke component="cms.extensions.CMSQueries"
method="GetLayouts" returnvariable="layoutsSELECT">
<cfinvokeargument name="RoleName"
value="#arguments.SessionVars.rolename#"/>
<cfinvokeargument name="DivisionID"
value="#arguments.SessionVars.DivisionID#"/>
<cfinvokeargument name="LayoutID"
value="#arguments.LayoutID#"/>
</cfinvoke>
<!--- Loop through Forms selected for layout --->
<cfloop index="i" list="#layoutsSELECT.Forms#"
delimiters=",">
<!--- Get the records from this table --->
<cfinvoke component="cms.extensions.CMSQueries"
method="GetRecords" returnvariable="sRecords">
<cfinvokeargument name="FormID"
value="#i#"/>
</cfinvoke>
<!--- Create Arrays that will be added to the query
--->
<cfset aIDColumn = ArrayNew(1)>
<cfset aTableName = ArrayNew(1)>
<cfset aDSNName = ArrayNew(1)>
<cfloop index="i" from="1"
to="#sRecords.FormRecords.recordcount#">
<cfset aIDColumn[#i#] = sRecords.IDColumn>
<cfset aTableName[#i#] =
sRecords.TableName1>
<cfset aDSNName[#i#] = sRecords.DSNName>
</cfloop>
<!--- Add the arrays to the query as a column --->
<cfset QueryAddColumn(sRecords.FormRecords,
"IDColumn", aIDColumn)>
<cfset QueryAddColumn(sRecords.FormRecords,
"TableName", aTableName)>
<cfset QueryAddColumn(sRecords.FormRecords,
"DSNName", aDSNName)>
<!--- Duplicate the query so we don't overwrite it
on the next loop --->
<cfset "records#loopcount#SELECT" =
Duplicate(sRecords.FormRecords)>
<!--- Set a variable with the name of the
Identifying column - this will be the first column the webmaster choose for
the index --->
<cfset "column#loopcount#" =
ListGetAt(sRecords.ColumnNameList, 1, ',')>
<!--- Set a variable with the name of the IDColumn
so we can use it in the query of queries below --->
<cfset "IDColumn#loopcount#" = sRecords.IDColumn>
<!--- Increase the loopcount by one if we are not on
the last loop --->
<cfif loopcount lt ListLen(layoutsSELECT.Forms,
',')>
<cfset loopcount = loopcount+1>
</cfif>
</cfloop>
<!--- Loop the number of times we had to loop above, joining
the records from the different querys --->
<cfquery name="allrecordsSELECT" dbtype="query">
<cfloop index="i" from="1" to="#loopcount#">
SELECT #Evaluate("IDColumn#i#")# AS ID,
#Evaluate("column#i#")# AS DisplayName, CMSUseOnlyFeatureSortOrder As
SortOrder
FROM records#i#SELECT
<cfif i lt loopcount>
UNION
</cfif>
</cfloop>
ORDER BY SortOrder
</cfquery>
<cfif allrecordsSELECT.recordcount>
<cfloop query="allrecordsSELECT">
<cfset
this.aLayout[allrecordsSELECT.currentRow]=StructNew()>
<cfloop list="#allrecordsSELECT.columnList#"
index="i" delimiters=",">
<cfset
StructInsert(this.aLayout[allrecordsSELECT.currentRow], i,
'#Evaluate("allrecordsSELECT.#i#")#')>
</cfloop>
</cfloop>
<cfreturn true>
<cfelse>
<cfreturn false>
</cfif>
</cffunction>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
