I have a similar page that I use to do queries (as CFStudio tends to barf
when I have too much other than it and the browser open, and the query
analyzer, while functional, is slooooow). The part you want is that big
section towards the end. Look for ColumnList.
-Rick
<CFSETTING ENABLECFOUTPUTONLY="Yes">
<CFPARAM NAME="Form.DSN" DEFAULT="">
<CFSET Form.DSN=Trim(Form.DSN)>
<CFSET CrLf=Chr(13)&Chr(10)>
<CFPARAM NAME="Form.Query" DEFAULT="">
<CFSET Form.Query=Trim(Form.Query)>
<CFREGISTRY ACTION="GETALL" NAME="DS_LIST" TYPE="Key"
BRANCH="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSo
urces">
<CFSET DSOptions="">
<CFLOOP QUERY="DS_List">
<CFIF Entry IS Form.DSN>
<CFSET DSOptions="#DSOptions#<OPTION SELECTED>#Entry#</OPTION>">
<CFELSE>
<CFSET DSOptions="#DSOptions#<OPTION>#Entry#</OPTION>">
</CFIF>
</CFLOOP>
<CFOUTPUT>
<HTML>
<HEAD><TITLE>Ad-Hoc Query<CFIF Form.Query IS NOT "">:
#Form.Query#</CFIF></TITLE>
<STYLE><!--
TABLE, TR, TD { font-size: 8pt; }
// -->
</STYLE>
</HEAD>
<BODY>
<FORM METHOD=POST>
Datasource: <SELECT NAME=DSN>#DSOptions#</SELECT><BR>
Query:<BR>
<TEXTAREA ROWS="5" COLS="80"
NAME="Query">#HTMLEditFormat(Form.Query)#</TEXTAREA><BR>
<INPUT TYPE=SUBMIT>
</FORM>
</CFOUTPUT>
<CFIF (Form.DSN IS NOT "") AND (Form.Query IS NOT "")>
<CFOUTPUT><HR></CFOUTPUT>
<CFTRY>
<CFQUERY DATASOURCE="#Form.DSN#"
NAME="AdHocQuery">#PreserveSingleQuotes(Form.Query)#</CFQUERY>
<CFPARAM NAME="AdHocQuery" DEFAULT="">
<CFIF IsQuery(AdHocQuery)>
<CFSET ColNames=AdHocQuery.COlumnList>
<CFOUTPUT><TABLE BORDER="1" CELLPADDING="2"
CELLSPACING="0">#CrLf#<TR><CFLOOP LIST="#ColNames#"
INDEX="ThisCol"><TD><STRONG>#ThisCol#</STRONG></TD></CFLOOP></TR></CFOUTPUT>
<CFOUTPUT QUERY="AdHocQuery"><TR><CFLOOP LIST="#ColNames#"
INDEX="ThisCell"><CFSET ThisVal=Evaluate(ThisCell)><TD><CFIF ThisVal IS
""> <CFELSE>#ThisVal#</CFIF></TD></CFLOOP></TR></CFOUTPUT>
<CFOUTPUT></TABLE>#CrLf#</CFOUTPUT>
</CFIF>
<CFCATCH TYPE="ANY">
<CFOUTPUT><P><EM>Database
Error:<BR>#CFCATCH.Message#<BR>#CFCATCH.Detail#</EM></P></CFOUTPUT>
</CFCATCH>
</CFTRY>
</CFIF>
<CFOUTPUT>
</BODY>
</HTML>
</CFOUTPUT>
-----Original Message-----
From: tom muck [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 26, 2000 2:18 PM
To: [EMAIL PROTECTED]
Subject: [CF-Talk] Dynamic query
I have a web page that displays a list of SQL databases in a select box.
The
user selects a database and a table, and is taken to a page where the
information is displayed from the database. At least, that's what I want to
do.
I can't figure out how to get the list of columns to be displayed in a
table. I
have a query that picks out the columns from the database table:
<cfquery name="getColumns" DBTYPE="OLEDB" DBNAME="#url.databases#"
PROVIDER="SQLOLEDB" DATASOURCE="mysource">
SELECT * from information_schema.columns where table_name = '#url.table#'
</cfquery>
And a query that gets the data from that table:
<cfquery name="getData" DBTYPE="OLEDB" DBNAME="#url.databases#"
PROVIDER="SQLOLEDB" DATASOURCE="mysource">
Select * from #url.table#
</cfquery>
but I don't know how I can display it, given the fact that the column names
are
variables. I tried the following:
<cfloop query="getData"><cfoutput
query="getColumns">#column_name#</cfoutput><br></cfloop>
but that just gives me the name of the column repeated for the number of
records.
Any ideas?
tom
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
