Column index

Hi there:

Is there any way to retrieve the column index based on its name? I tried using the ColumnCollection property of the Table object, but it is not a "real" collection, so the IndexOf["MyColumnName"] doesn't exist.

I have the Database, Table, ColumnCollection and Column objects available, is there any other way I can retrieve a column's index in the table?

Thank you

Maybe this code will help - it's not exactly a look up, but it'll get you to the info pretty quickly.

Dim colTbl As TableCollection
Dim tbl As Table
Dim colIdx As IndexCollection
Dim idx As Index
db = New Database(srv, "AdventureWorks")
colTbl = db.Tables
For Each tbl In colTbl
colIdx = tbl.Indexes
For Each idx In colIdx
Console.WriteLine(idx.Name)
Next
Next

|||

Hi, Allen, thank you for your reply.

While that code would work to retrieve all indexes names in a table, my problem was retrieving the position of any column in a table based on its name. Unfortunate choice of names (index), but the code I was looking for (and doesn't work) is something like:

CollumnCollection collumnColl = table.Columns;

int columnPos = columnColl.IndexOf("MyColumnName");

It seems to me that your code would properly retrieve all indexes in a table, not necessarily all column positions, no?

Thanks again.

|||

If you create a variable of type Column, say colThisOne, you can populate it by the following statement:

colThisOne = table.Columns("MyColumnName");

Does that help? It doesn't give you the order number of the column in the table, but relational theory says that the column order doesn't matter. If it does, the best I can tell you at this point is that colThisOne.ID may have the value you're looking for.

|||

Column.ID, eh? Hmm, haven't thought that it would have a meaningful value (apart from being unique). It is an int, indeed, so it may work.

I can access the column by name, however I am trying to dynamically populate a list of properties, so the column position (while indeed irrelevant for all intents and purposes) is important for my solution. I am already working on alternative approaches, so I may not need it, but this is not a bad suggestion at all, I will try it and let you know.

Thank you!