Combo Box + SQL

[erom11]

Is it possible to fill a Combo Box with the names of the fields of a table(from a database)?

[jmcilhinney]

Yes it is. The specifics depend on your database but it would be very similar to this example for SQL Server:

Using connection As New SqlConnection(connectionString)
    connection.Open()
 
    Me.ComboBox1.ValueMember = "COLUMN_NAME"
    Me.ComboBox1.DataSource = connection.GetSchema("Columns", _
                                                   New String() {Nothing, _
                                                                 Nothing, _
                                                                 tableName})
End Using

You can experiment a bit with the GetSchema method of your connection to see what it can do with different arguments.

[erom11]

Thanks for answering. It worked fine! Can you please tell me what exactly the below code does in your code as I don't understand it: New String() {Nothing, Nothing,

I understand that it is something like a restriction but...
Moreover, the returned names of the columns are in alphabetical sequence. Is it possible to return them in the order they exist in the Table?
Thanks in advance

[jmcilhinney]

You're correct, that code does create a set of restrictions. The Columsn collection has four restrictions I believe. The first one is database name and the second is schema name, if I remember correctly. In this case we don't care about either of those so we use Nothing. We do want to restrict the results to a single table though, and the third restriction is the table name. I can't recall what the fourth restriction is but if you want to find out you can call GetSchema and specify the Restrictions collection.

As far as the order of the columns, I think you'll find that one of the columns in the DataTable returned by GetSchema is COLUMN_INDEX or something like that. You can sort your DataTable by that column and the ComboBox will show that order:

Dim schemaTable As DataTable = connection.GetSchema("Columns", ...)
 
schemaTable.DefaultView.Sort = "COLUMN_INDEX"
Me.ComboBox1.DataSource = schemaTable

[erom11]

Thanks for answering. Even if I didn't manage to fix the sequence (as probably I couldn't find the correct collection name in the GetSchema method), I understood many things from your comments and also from the following link which describes the name of the collections and their extra feuatures. The strange thing is that in the very first comment you wrote to me you specify something the name "Columns" in the getSchema method. It worked perfectly but it doesn't exist in the collection names of the link I wrote above. So, I am wondering if this link have every collection name.

[erom11]

Sorry for bothering you again but I found also the collections you told me in this linkhttp://msdn.microsoft.com/en-us/libr...69(VS.80).aspx.
Thanks

[jmcilhinney]

You should note that GetSchema returns a DataTable every time, but what that DataTable represents depends on the collection name and restriction values you specify. If you want to know the names of all the collections supported by any database you simply call GetSchema with no arguments. The results will often be different for different databases. As I said, the result will be a DataTable. One column will contain all the collection names.

As for ordering, it has nothing to do with the collection name. When you call GetSchema you will ALWAYS get a DataTable back. As with any and every DataTable, you can sort the data by any or all columns via the DefaultView. You simply have to assign a single column name to the Sort property to sort in ascending order by that column. If you want to know the names of the columns in a DataTable you simply loop through the Columns collection and test the ColumnName of each item. Alternatively, just bind the table to a DataGridView and read the column headers.