Well, if you just want to know the name of the first column in your table,
why don't you do a "select * " and then get the listfirst of
query.columnlist ?
such as
<cfquery name="firstquery" datasource="dsn" maxrows="1">
select * from yourtable
</cfquery>
<cfset yourcolumn = listlast(firstquery.columnlist)>
<cfquery name="secondquery" datasource="dsn">
select #yourcolumn# from yourtable
</cfquery>
At 12:47 PM 2/17/2002 -0800, you wrote:
>I have come up with a hack solution:
>
>DECLARE @IDCOLUMN varchar(50)
>DECLARE @OBJ varchar(50)
>DECLARE @OBJID int
>DECLARE @STATEMENT varchar(50)
>
>SET @OBJ = 'PRODUCTS'
>SET @OBJID = OBJECT_ID(@OBJ)
>SET @IDCOLUMN = COL_NAME(@OBJID,1)
>
>SELECT @IDCOLUMN As IDColumn
>
>Given that the identity column is the first column in the table (which
>most of my tables are), this will work (you can change this in the
>COL_NAME function). It can be shortened to:
>
><cfset tablename = "PRODUCTS">
>
><CFQUERY NAME="qry1" DATASOURCE="dsn">
>SELECT COL_NAME(OBJECT_ID('#tablename#'),1) As IDColumn
></CFQUERY>
>
>This could be used in another query, such as:
>
><CFQUERY NAME="qry2" DATASOURCE="dns">
>SELECT #qry1.IDColumn# As IDColumn
>FROM #tablename#
></CFQUERY>
>
>Thanks for all the input! If anyone knows a better way, please let me
>know!
>
>
>
>-----Original Message-----
>From: Jeff Beer [mailto:[EMAIL PROTECTED]]
>Sent: Sunday, February 17, 2002 11:42 AM
>To: CF-Talk
>Subject: RE: Dynamically Obtaining IDENTITY Column
>
>Scott - do you mean the identity value of a newly inserted record, or to
>select the ID value when you don't know the name of the identity column?
>
>-----Original Message-----
>From: Scott Van Vliet [mailto:[EMAIL PROTECTED]]
>Sent: Sunday, February 17, 2002 2:22 PM
>To: CF-Talk
>Subject: Dynamically Obtaining IDENTITY Column
>
>
>Hey All.
>
>Does anyone out there know of a way to dynamically grab the identity
>column from a table in a select statement?
>
>Example:
>
><CFQUERY NAME="queryname" DATASOURCE="dsn">
>SELECT @@IDENTITY_COLUMN
>FROM TABLE_NAME
></CFQUERY>
>
>Where "@@IDENTITY_COLUMN" would be the function to obtain the value of
>the identity column.
>
>TIA.
>
>----
>SCOTT VAN VLIET
>BRD.WRKS INTERACTIVE
>[EMAIL PROTECTED]
>
>
>
>
>
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
