MS SQL Server, Don't know the field name but do know the column order

Don't know the field name but do know the column order

Author	Message
John Shearin #1 / 5	Don't know the field name but do know the column order Hi All, I need to write a select statement that will select columns from a table based on the column order. For instance: If I had a table called (MyTable) and it had 3 fields call MyField1, MyField2 and MyField3 I would like to write a select statement the selects the data from MyField3 with a select statement like this: SELECT 3 FROM MyTable. I need to do this because I do not have the name of the field at run time but I will know the position of the column in the table. Thanks, John
Wed, 20 Aug 2003 04:12:10 GMT

Rob Morron #2 / 5	Don't know the field name but do know the column order The Names of the fields are included in the META data from the recordset. One method that I have used is to execute the following: recordSet.Open "SELECT * FROM MyTable WHERE 1=2", ConnectionString 1 will never equal 2, so what is returned is an empty recordset that still contains all of the META data. recordSet(3).name will be the name of the column. -- Rob Morrone http://www.webstrands.com Quote: > Hi All, > I need to write a select statement that will select columns from a table > based on the column order. > For instance: > If I had a table called (MyTable) and it had 3 fields call MyField1, > MyField2 and MyField3 > I would like to write a select statement the selects the data from MyField3 > with a select statement like this: > SELECT 3 FROM MyTable. > I need to do this because I do not have the name of the field at run time > but I will know the position of the column in the table. > Thanks, John
Wed, 20 Aug 2003 05:18:08 GMT

DaveSat #3 / 5	Don't know the field name but do know the column order something that uses something like this ?: SELECT TABLE_NAME + ".." + COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = "dtproperties" AND ORDINAL_POSITION = 5 -- HTH, David Satz Principal Software Engineer Hyperion Solutions ->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS (Please reply to group only - emails answered rarely) ----------------------------------------------------------------- Quote: > Hi All, > I need to write a select statement that will select columns from a table > based on the column order. > For instance: > If I had a table called (MyTable) and it had 3 fields call MyField1, > MyField2 and MyField3 > I would like to write a select statement the selects the data from MyField3 > with a select statement like this: > SELECT 3 FROM MyTable. > I need to do this because I do not have the name of the field at run time > but I will know the position of the column in the table. > Thanks, John
Wed, 20 Aug 2003 05:47:28 GMT

John Shearin #4 / 5	Don't know the field name but do know the column order Hi Rob, That is a good trick. Unfortunatly I am trying to do all this work from inside the stored procedure. This is because I am trying to keep all my business logic on the back end. Thanks, John Quote: >The Names of the fields are included in the META data from the recordset. >One method that I have used is to execute the following: >recordSet.Open "SELECT * FROM MyTable WHERE 1=2", ConnectionString >1 will never equal 2, so what is returned is an empty recordset that still >contains all of the META data. >recordSet(3).name will be the name of the column. >-- >Rob Morrone >http://www.webstrands.com
Sat, 23 Aug 2003 08:31:35 GMT

John Shearin #5 / 5	Don't know the field name but do know the column order Hi Dave, Yes this is what I am looking for. Thanks for your help, John Quote: >something that uses something like this ?: >SELECT TABLE_NAME + ".." + COLUMN_NAME >FROM information_schema.columns >WHERE TABLE_NAME = "dtproperties" >AND ORDINAL_POSITION = 5 >-- >HTH, >David Satz >Principal Software Engineer >Hyperion Solutions >->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS >(Please reply to group only - emails answered rarely) >-----------------------------------------------------------------
Sat, 23 Aug 2003 08:39:48 GMT

Page 1 of 1

[ 5 post ]