macromedia.coldfusion.advanced_techniques
[Top] [All Lists]

cfquery dynamic return columns and fields to a table

Subject: cfquery dynamic return columns and fields to a table
From: "CR_COOL" <webforumsuser@xxxxxxxxxxxxxx>
Date: Tue, 29 Jul 2008 19:58:39 +0000 (UTC)
Newsgroups: macromedia.coldfusion.advanced_techniques

Problem in the following code...

 <!--- Gets all information with in a table from the SQL DB - columns type and 
name of a table from the SQL Database --->
 <cfinvoke 
  component="com.sql"
  method="getTableListofFields"
  returnvariable="getTableListofFieldsRet">
        <cfinvokeargument name="tableName" value="#qryLookup.TableName#"/>
 </cfinvoke>

 <!--- Lookup all the fields names and labels that will be use the present the 
user --->
 <cfquery name="qryDefinedFields" datasource="ExtranetServices">
   SELECT *
   FROM [hq-sa].tb_form_builder
   WHERE request_id = <cfqueryparam value="#qryLookup.request_id#" 
cfsqltype="cf_sql_numeric"> 
 </cfquery>
  
 <!--- Using the #qryLookup.TableName# as a variable / dynamic get the table 
with its values --->
 <cfquery name="qryTable" datasource="ExtranetServices">
 SELECT *
 FROM #qryLookup.TableName#
 WHERE ticket_id=<cfqueryparam cfsqltype="cf_sql_numeric" 
value="#attributes.ticket_id#">
 </cfquery>


 <cfoutput>
  <table width="100%" border="0" cellpadding="2" cellspacing="0">
 <!--- List all column names found in the database --->
   <cfloop query="getTableListofFieldsRet">
       <tr>
 <!--- List all fields that are found to build the form --->
       <cfloop query="qryDefinedFields">
 <!--- Compare both queries and find the equales --->
       <cfif #getTableListofFieldsRet.COLUMN_NAME# EQ 
#qryDefinedFields.field_name#>
        <th style="width:150px; text-align:right;" width="150">
 <!--- Display Label Name --->
 #qryDefinedFields.field_label#</th>
        <td>
        <!--- Display the value/s from the qryTable that correspond to the 
#getTableListofFieldsRet.COLUMN_NAME#  --->

 <!--- This does not work, can any one help --->
 # qryTable. #getTableListofFieldsRet.COLUMN_NAME# # 
        </td>
        </cfif>
        </cfloop>
       </tr>
       </cfloop>
  </table>
 </cfoutput>


<Prev in Thread] Current Thread [Next in Thread>