Here's the code if you want it.
Beware of wrapping
START CODE
<cf_expire>
<!-- *** David DiPietro *** -->
<!-- *** Last updated 07/03/2002 *** -->
<!-- *** Feel Free to use and modify this and pass it to friends. *** -->
<!-- *** Hope it makes your lives easier. *** -->
<!-- *** This is being run with a SQL Server Back End *** -->
<!-- *** Getting Column names from a Table
First select the sysObject id from dbo.sysobjects
Where name = 'TableName'
Then Select * from dbo.syscolumns where ID = TheFirstQueryID.
*** -->
<cfoutput>
<cfset colNum = 3>
<cfset tWidth = 500>
<cfset tableColor = '##E7E8D3'>
<cfset labelRowColor = '##CCCC99'>
<cfset inputRowColor = '##E7E8D3'>
<cfset T1BGcolor = '##E7E8D3'><!--- *** Main Table BGColor - T1BGcolor *** --->
<cfset T1Spacer1Color = 'White'><!--- *** 1st spacer color - T1Spacer1Color *** --->
<cfset T1Spacer2Color = '##800000'><!--- *** 2nd spacer color - T1Spacer2Color
*** --->
<cfset T1RBGcolor = ''><!--- *** Main Table Row BGColor - T1RBGcolor *** --->
<cfset STBGcolor = '##CCCC99'><!--- *** Sub Table BGColor - STBGcolor *** --->
<cfset STColor1 = '##FFFFFF'><!--- *** Sub Table Color1 - STColor1 *** --->
<cfset STColor2 = '##006363'><!--- *** Sub Table Color2 - STColor2 *** --->
<cfset STRowcolor = ''><!--- *** Sub Table RowColor - STRowcolor *** --->
<cfset MHFont1 = '<font size="3" face="Arial, Helvetica, sans-serif"
color="##003030">'><!--- *** Main Headline Font1 - MHFont1 *** --->
<cfset MDFColor = '<font size="2" face="Arial, Helvetica, sans-serif"
color="##003030">'><!--- *** Main Detail Font - MDFColor *** --->
<cfset MDFColorTiny = '<font size="1" face="Arial, Helvetica, sans-serif"
color="##003030">'><!--- *** Main Detail FontTiny - MDFColorTiny *** --->
<cfset SHFColor = '<font size="2" face="Arial, Helvetica, sans-serif"
color="##666633">'><!--- *** Sub Headline Font Color - SHFColor *** --->
<cfset SDFColor = '<font size="2" face="Arial, Helvetica, sans-serif"
color="##333300">'><!--- *** Sub Detail Font Color - SDFColor *** --->
<cfset SDFColorTiny = '<font size="1" face="Arial, Helvetica, sans-serif"
color="##333300">'><!--- *** Sub Detail Font Color Tiny - SDFColorTiny *** --->
<cfset SADFColor = '<font size="2" face="Arial, Helvetica, sans-serif"
color="##800000">'><!--- *** Sub Alert Font Color - SAFColorTiny *** --->
<cfset SAFColorTiny = '<font size="1" face="Arial, Helvetica, sans-serif"
color="##800000">'><!--- *** Sub Alert Font Color Tiny - SAFColorTiny *** --->
<cfset indent = ' '><!--- *** Indent of 5 spaces - indent
*** --->
</cfoutput>
<!-- *** Put your Table name and Datasource Name here *** -->
<!-- *** TABLE NAME *** -->
<cfset TTableName=''>
<!-- *** DATASOURCE NAME *** -->
<cfset DatasourceName = ''>
<!-- *** This finds the id for a specific table. *** -->
<cfquery name="GetID" datasource="#DatasourceName#">
SELECT name, id
FROM dbo.sysobjects
WHERE name = '#TTableName#'
</cfquery>
<!-- *** This selects the columns of that specified table *** -->
<cfquery name="getColumns" datasource="#DatasourceName#">
SELECT *
FROM dbo.syscolumns
WHERE id = #GetID.id#
</cfquery>
<!-- *** Make and fill a list *** -->
<cfset fields = ""><cfoutput query="getColumns"><cfset fields =
"#listappend(fields,name)#"></cfoutput>
<cfoutput>
<TABLE bgcolor="white" border=1 cellpadding= cellspacing= >
<tr>
<td colspan=4><font size=2 face='arial'>
<b>Source Code for the Select query to create an add update or view table for
table: #GetID.name#</B><br><br>
The query below is the select query that gathers the information from table:
#GetID.name#.
<br>Simply highlight the text of the query and paste it into a new document
and finish the cf statements with find
and replace functions.<br><br>
</td>
</tr>
<tr>
<td colspan=4><font size=2 face='courierNew'>
cfinclude template='../../app_locals.cfm'><br>
cfquery name='get#TTableName#Fields' datasource='#DatasourceName#'><br>
Select #fields#<br>
FROM #TTableName#<br>
cfif attributes.view neq 'N'><br>
cfif isdefined('attributes.CID')><br>
Where ID = '##attributes.CID##'<br>
/cfif><br>
/cfif><br>
/cfquery><br></font>
</td>
</tr>
</TABLE>
<cfset hCounter = 0><cfset bCounter = 0><cfset fLength = #ListLen(fields)#><cfset
fLoops = (#fLength#/3)+1>
<br><br>
<TABLE bgcolor="white" border=1 cellpadding= cellspacing= >
<tr>
<td colspan=4><font size=2 face='arial'>
<b>Source Code table for table: #GetID.name#</B><br><br>
The actual form table below needs to be selected from the view source page.
<br>Once copied into your new document below the
select query again the cf tags need to be finished. <br>Theoretically all
you will then need to do is format your page and pass
it an ID and the view attribute and you should be up and running. </font>
<br><br>
</td>
</tr>
</TABLE>
</cfoutput>
<!-- *** The form table begins here. Start copying source code. *** -->
<!-- *** The form table begins here. Start copying source code. *** -->
<!-- *** The form table begins here. Start copying source code. *** -->
<!-- *** The form table begins here. Start copying source code. *** -->
<cfoutput>
cfoutput>
cfinclude template="app_locals.cfm">
<TABLE bgcolor="##T1BGcolor##" border=1 cellpadding= cellspacing= width=#tWidth#>
<tr>
<td colspan=4>##MHFont1##
Inputs for Table - #GetID.name# -
</td>
</tr>
</cfoutput>
cfform name="WhateverForm" method="Post" Action="WhateverFormAction.cfm">
<cfloop from=1 to=#floops# index='i' >
<cfoutput>
<TR bgcolor='##STBGcolor##'>
</cfoutput>
<cfloop from=1 to=#colNum# index='x'><cfset hCounter = #hCounter#+1><cfif
hCounter lt fLength+1>
<cfquery name="getColumnsForName" datasource="#DatasourceName#">
SELECT *
FROM dbo.syscolumns
WHERE id = #GetID.id# and name='#ListGetAt(fields, hcounter)#'
</cfquery>
<cfoutput query="getColumnsForName">
<TD >##MDFColor##
#Name#
</font>
</TD>
</cfoutput><cfelse><TD> </TD></cfif></cfloop>
</TR>
<cfoutput>
<TR bgcolor='##T1BGcolor##'>
</cfoutput>
<cfloop from=1 to=#colNum# index='x'><cfset bCounter = #bCounter#+1><cfif
bCounter lt fLength+1>
<cfquery name="getColumnsForName" datasource="#DatasourceName#">
SELECT *
FROM dbo.syscolumns
WHERE id = #GetID.id# and name='#ListGetAt(fields, bcounter)#'
</cfquery>
<cfoutput query="getColumnsForName">
<TD >##SDFColor##
cfif attributes.view eq 'N'>
<cfif length lt 20> cfinput required="NO" message="Do something for field
#name#" type="Text" name="#Name#" size="#length#" maxlength="#length#"
value=""><cfelse> cfinput required="NO" message="Do something for field #name#"
type="Text" name="#Name#" size="20" maxlength="#length#" value=""></cfif>
cfelseif attributes.view eq 'E'>
<cfif length lt 20> cfinput required="NO" message="Do something for field
#name#" type="Text" name="#Name#" size="#length#" maxlength="#length#"
value="##get#TTableName#Fields.#Name###"><cfelse> cfinput required="NO" message="Do
something for field #name#" type="Text" name="#Name#" size="20" maxlength="#length#"
value="##get#TTableName#Fields.#Name###"></cfif>
cfelseif attributes.view eq 'V'>
##get#TTableName#Fields.#Name###
/cfif></font>
</TD>
</cfoutput><cfelse><TD> </TD></cfif></cfloop>
</TR>
</cfloop>
cfif attributes.view neq 'V'>
<cfoutput>
<TR bgcolor='##T1BGcolor##'>
<td colspan=4>##SDFColor##
<input required="" message="" type="Submit" name="Submit" Value="Submit">
<input required="" message="" type="Reset" name="Reset" Value="Reset">
<A
HREF="../actions/act_whatever.cfm?ID=##get#TTableName#Fields.ID##&action=D">Delete</A
>
</td>
</tr>
</cfoutput>
/cfif>
/cfform>
</TABLE>
<cfoutput>
/cfoutput>
<!-- *** The form table ends here. Stop copying source code. *** -->
<!-- *** The form table ends here. Stop copying source code. *** -->
<!-- *** The form table ends here. Stop copying source code. *** -->
<!-- *** The form table ends here. Stop copying source code. *** -->
<br>
<br>
<TABLE name="whatever" bgcolor="white" border=1 cellpadding= cellspacing= >
<tr>
<td colspan=4><font size=2 face='arial'>
<B>Update, Insert, and Delete action page code for table:
#GetID.name#</B><br><br>
Copy this code from the web page as opposed to the Source code. <br>This
again needs the cf codes finished
through search and replace functions. <br>Be sure to remove the quotes
around numeric variables.</font><br><br>
</td>
</tr>
<tr>
<td colspan=4><font size=2 face='courierNew'>
Action Page code<br><br><br>
cfinclude template='../app_locals.cfm'><br>
cfset update=0><br>
cfset insert=0><br>
cfif isdefined('attributes.ID')><br>
cfif isdefined('attributes.Action')><br>
cfif attributes.Action eq 'D'><br>
cfquery
name='delete#TTableName#' datasource='#DatasourceName#'><br>
Delete<br>
From
#TTableName#<br>
Where id =
'##attributes.id##'<br>
/cfquery><br>
cfelse><br>
cfquery
name='update#TTableName#' datasource='#DatasourceName#'><br>
update
#TTableName#<br>
Set<br>
<cfset lcnt=0><cfloop index='x' list=#fields#><cfif x neq 'ID'><cfset lcnt=
#lcnt#+1><cfif lcnt eq 1> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>#x#='##attributes.#x###'
/cfif><br><cfelse> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>,#x#='##attributes.#x###'
/cfif><br></cfif></cfif></cfloop>
Where
ID=##attributes.ID##<br>
/cfquery><br>
cfset update=1><br>
/cfif><br>
cfelse><br>
cfquery name='update#TTableName#'
datasource='#DatasourceName#'><br>
update #TTableName#<br>
Set<br>
<cfset lcnt=0><cfloop index='x' list=#fields#><cfif x neq 'ID'><cfset lcnt=
#lcnt#+1><cfif lcnt eq 1> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>#x#='##attributes.#x###'
/cfif><br><cfelse> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>,#x#='##attributes.#x###'
/cfif><br></cfif></cfif></cfloop>
Where
ID=##attributes.ID##<br>
/cfquery><br>
cfset update=1><br>
/cfif><br>
cfelse><br>
cfif not isdefined('attributes.Action') or
(isdefined('attributes.Action') and attributes.Action neq 'D')><br>
cfquery name='Insert#TTableName#'
datasource='#DatasourceName#'><br>
INSERT into #TTableName#(<br>
<cfset lcnt=0><cfloop index='x' list=#fields#><cfif x neq 'ID'><cfset lcnt=
#lcnt#+1><cfif lcnt eq 1> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>#x#
/cfif><br><cfelse> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>,#x#
/cfif><br></cfif></cfif></cfloop>
) <br>
Values (
<br>
<cfset lcnt=0><cfloop index='x' list=#fields#><cfif x
neq 'ID'><cfset lcnt= #lcnt#+1><cfif lcnt eq 1> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>'##attributes.#x###'
/cfif><br><cfelse> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>,'##attributes.#x###'
/cfif><br></cfif></cfif></cfloop>
)<br>
/cfquery><br>
cfset insert=1><br>
/cfif><br><br>
/cfif><br><br>
cfif not isdefined('attributes.Action') or
(isdefined('attributes.Action') and attributes.Action neq 'D')><br>
cfquery name='GetID'
datasource='#DatasourceName#'><br>
Select ID<br>
From #TTableName#<br>
Where 0 = 0<br>
<cfset lcnt=0><cfloop index='x' list=#fields#><cfif x neq 'ID'><cfset lcnt=
#lcnt#+1> cfif
isdefined('attributes.#x#') and attributes.#x# neq ''>AND #x#='##attributes.#x###'
/cfif><br></cfif></cfloop>
/cfquery><br><br>
/cfif><br><br>
cfoutput><br>
cfif 0 eq 0><br>
cfif GetID.recordcount gt 0 and
GetID.recordcount gt 0><br>
cfif
isdefined('cgi.Http_Referer') and insert eq 0 and update eq 1><br>
cflocation
URL='##cgi.Http_Referer##' addtoken="No"><br>
cfelse><br>
cflocation
URL='../display/DSP_whatever.cfm?view=E&CompID=##GetID.ID##' addtoken="No"><br>
/cfif><br>
cfelse><br>
cflocation
URL='../display/DSP_whatever.cfm?view=N' addtoken="No"><br>
/cfif><br>
/cfif><br>
/cfoutput><br><br>
</cfoutput></font>
</td>
</tr>
</TABLE>
END CODE
David DiPietro
Systems Developer / Engineer
OSU College of Medicine & Public Health
Voice (614) 292-5960
Fax (614) 292-0745
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
