2 Replies Latest reply on Dec 7, 2012 8:30 AM by BreakawayPaul

    cfloop/cfoutput problem

    BreakawayPaul Level 2

      After many years of dire predictions from myself about using MS Access for a production website, my office has finally bitten the bullet and provided me with MSSQL Enterprise.  FINALLY I no longer have to append disclaimers and apologies to my requests for assistance (unless it's about my sloppy coding).

       

      One of the changes in this switch is that my 14 or so existing Access databases have been "squished" into one huge MSSQL database.  The resulting database has around 115 tables, and with no MDB file to open up and look at, I decided to build a "table browser" for my coders to use.  Most of the information that we change frequently already has a web interface, but some of the tables we rarely touch.  Still, I wanted a way to "look around" inside the database.

       

      The page I've built basically creates an unordered list of table names in a scrolling left column.  When you click a table name, it expands into a nested list of row names. This all works quite well.  Code is as follows:

      <cfquery name="GetTables" datasource="hep">

      USE hep

      SELECT name, max_column_id_used AS cols, object_id

      FROM sys.Tables

      </cfquery>

      <ul>

      <cfoutput query="GetTables">

      <li><a href="dbmanage.cfm?table=#name###a#object_id#" name="a#object_id#">#name#</a> (#cols#)

      <cfif #URL.table# eq #name#>

      <ul>

      <cfquery name="GetColumns" datasource="hep">

      SELECT data_type, column_name

      FROM Information_Schema.Columns

      WHERE table_name = <cfqueryparam value="#URL.table#" cfsqltype="cf_sql_varchar">

      </cfquery>

      <cfloop query="GetColumns">

      <li><a href="dbmanage.cfm?table=#URL.table#&amp;column=#column_name#">#column_name#</a> (#data_type#)</li>

      </cfloop>

      </ul>

      </cfif>

      </li>

      </cfoutput>

      </ul>

       

      The next thing I'm going for is that when you click a table name, the contents of that table appear in the right column.  I started by using <cfdump> for this, but the results of that are rather hard to manipulate, so I've started coding my own:

       

      <cfquery name="GetRows" datasource="hep">

      SELECT *

      FROM #URL.table#

      </cfquery>

      <table class="dbtable">

      <tr><cfloop query="GetColumns"><th><cfoutput>#GetColumns.column_name#</cfoutput></th></cfloop></tr>

      <tr><cfloop query="GetColumns"><td><cfoutput><strong>#GetColumns.data_type#</strong></cfoutput></td>< /cfloop></tr>

      <cfloop query="GetColumns"><tr>

          <cfloop query="GetRows"><td><cfoutput>#evaluate(GetColumns.column_name)#</cfoutput></td>

          </cfloop></tr>

      </cfloop>

      </table>

      </cfif>

       

      The problem with the above code is that it builds the table in the wrong direction.  Easy to fix, right?  I mean, I just switch the loops around, like this:

       

      <cfloop query="GetRows"><tr>

          <cfloop query="GetColumns"><td><cfoutput>#evaluate(GetColumns.column_name)#</cfoutput></td>

          </cfloop></tr>

      </cfloop>

      Unfortunately, if I do that, I get an error message that my variables are undefined.  I've tried switching the <cfloop>s to <cfoutput>s, I've tried doing from="1" to="GetRows.recordcount", I've tried combinations of each, and nothing works.  Why on earth will this table build fine in one direction but no the other??  I'm completely baffled!