CFPARAM many, many, many columns

"
over 125 database columns

"

Interesting problem, a generic tool might be a solution if such need is sort of frequent...

I created such a tool back a few years ago, providing the ability of picking any user table for data view, manipulation (update) and search off MS SQL Server 2000/2005....

Coding schema is similar to the following...

-==cfSearching==-
select * from information_schema.columns where table_name = 'x' ..

knowledgenotebook (but this is different ...)

Ok, since apparently it got eaten via email, here's the sys table query that will get you table info.

This query will get you all the columns in the table, with datatype  information:

<cfquery datasource="#variables.datasource#" name="columnInfo">
            SELECT c.colid,
                c.name,
                c.colorder,
                c.prec,
                c.xtype,
                c.typestat,
                c.xusertype,
                c.isnullable,
                t.name AS typename,
                (    SELECT COUNT(*)
                    FROM sysindexkeys
                    WHERE id = syscolumns.id
                        AND indid = 1
                        AND colid = syscolumns.colid
                ) AS isPrimary
            FROM syscolumns c LEFT JOIN
                systypes t ON c.xtype = t.xtype
                    AND c.xusertype = t.xusertype
            WHERE c.id = OBJECT_ID(<cfqueryparam  cfsqltype="CF_SQL_VARCHAR" value="#variables.tableName#">)
            ORDER BY c.colorder
</cfquery>

Probably don't need all those columns, but you should be able to loop  over that and create a CFPARAM with the name based on the 'name' and the  type based on the 'typename' values.  Haven't tested this on SQL 2008  but it used to work great in SQL 2000, so I imagine it still would.

What is the purpose of this application?  Where will it be expected to live and work? Are you familar with your database management system of choice's meta tables?  I.E. the tables that contain the definitiosn of all the other tables.

It's an internal corporate web app, nothing the public will see.  It's a form that will keep track of a lot of data.  Five fields wide by 25 fields high (125 fields just for numbers.)

I am not familiar, no.

This requirement makes me cringe a bit though... why are you needing to do it?

Because I don't want to manually CFPARAM over 125 database columns, defaulting to either 0 or '' unless I absolutely POSITIVELY have to.

If it were just 20 or 30 columns, then even I would say I'm lazy.  But not over 125.

Thanks,

^_^

Well... start by looking @ the SP_HELP proc.  Or perhaps CFDBINFO (which probably wraps up calls to SP_HELP under the hood).

Tha'll give you the DB info you want, then it's just a matter of looping over the relevant info, and paramming the variable into the specific scope that you want.  You'll probably need to map the SQL Server data types to CF data types with a look-up struct or something (for the TYPE attribute of the CFPARAM).

This requirement makes me cringe a bit though... why are you needing to do it?

--

Adam

WolfShade wrote: Any suggestions? ^_^

What are you having trouble with?

What is the purpose of this application?  Where will it be expected to live and work?

Are you familar with your database management system of choice's meta tables?  I.E. the tables that contain the definitiosn of all the other tables.

This query will get you all the columns in the table, with datatype

information:

Probably don't need all those columns, but you should be able to loop

over that and create a CFPARAM with the name based on the 'name' and the

type based on the 'typename' values. Haven't tested this on SQL 2008

but it used to work great in SQL 2000, so I imagine it still would.