• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Automatically build a FORM using SQL Server system tables

As every experienced programmer knows, there are always many different ways to accomplish a goal. If you are using a SQL Server database, you have additional options available when dynamically creating forms.
When you create a table, SQL Server automatically stores every attribute of that table (columns, indexes, etc.) in system tables.

With a little knowledge of the system tables, you can retrieve this information and build a dynamic HTML form by supplying only the table name.

First, create a simple table in Query Analyzer.

CREATE TABLE [person] (
    [person_id] [int] IDENTITY (1, 1) NOT NULL ,
    [first_nm] [varchar] (40) NULL ,
    [middle_init] [char] (1) NULL ,
    [last_nm] [varchar] (40) NOT NULL ,
    [age] [int] NOT NULL ,
    CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
    (
        [person_id]
    ) 
ON [PRIMARY] 
) 
ON [PRIMARY]
GO

Don't forget to assign the necessary permissions on the table.

<!--- paste the following code into a new page called build_form.cfm. Don't forget to set up your datasource! --->
<!--- Once the table is created, we set a single variable for the table name. --->
<cfset table_nm = "person">

<!--- get the column names --->
<cfquery name="get_columns" datasource="#datasource#">
    SELECT   sc.name
                ,sc.length
    FROM     SysObjects so
    JOIN      SysColumns sc ON so.id = sc.id
    WHERE   so.name = '#table_nm#'
          AND sc.autoval is null
    order by sc.colid
</cfquery>

<!--- make sure the table exists --->
<cfif not get_columns.recordcount>
    Table <cfoutput><strong>#table_nm#</strong></cfoutput> not found.
<cfabort>
</cfif>

<!--- for this example, the page will submit to itself to insert the data --->

<h3>Insert '<cfoutput>#table_nm#</cfoutput>' Form</h3>
<form name="auto_form" method="post" action="build_form.cfm">
<table>
    <cfoutput query="get_columns">
    <tr>
        <td>#name#</td>
        <td><input type="Text" name="#name#" size="#length#"></td>
    </tr>
    </cfoutput>
</table>
<br>
<input type="submit" value="Insert Name">
</form>


<cfif isdefined("FORM.FIELDNAMES")>
    <!--- insert values if the form was submitted --->
    <cfinsert datasource="#datasource#" tablename="#table_nm#">
</cfif>

<!--- query to display all saved values --->
<cfquery name="get_saved" datasource="#datasource#">
    select *
    from    #table_nm#
</cfquery>

<h3>Saved Records</h3>
<cfif get_saved.recordcount> 
    <!--- since we don't know the table format, we have to dynamically create the table --->
    <table border="1"> 
        <tr>
            <cfloop list="#get_saved.columnlist#" index="col_name">
            <td><cfoutput>#col_name#</cfoutput></td>
            </cfloop> 
        </tr> 
        <cfloop query="get_saved">
        <tr>
            <cfloop list="#get_saved.columnlist#" index="col_name">
            <td><cfoutput>#evaluate(col_name)#</cfoutput></td>
            </cfloop> 
        </tr> 
        </cfloop>
    </table>
<cfelse>
    <p>No saved records.</p>
</cfif>

This example is a simple implementation of this logic - with a little extra work you can extend this idea to change formatting, titles,
validation, etc.