How can you fill combobox in vb.net with column name from Microsoft SQL Server

Question

How to fill combobox in vb.net with column name from Microsoft Sql Server? I have table name called Table_Categories. I wish to fill the combobox with the column names of that table . I am also planning on adding column names in the table using vb.net as its front end . how can I do it ?

    Public Sub Categories()
    Dim Connect As New SqlConnection
    Dim Adapter As New SqlDataAdapter
    Dim DataTable As New System.Data.DataTable
    Dim Query As String
    ConnectionString = "Data Source=LUSPOC-PC;Initial Catalog=Sales_Invnetory;Integrated Security=True"
    Connect = New SqlConnection(ConnectionString)
    Connect.Open()
    Connect.ChangeDatabase("Sales_Inventory")
    Query = "select Column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Table_Categories'"
    Adapter.SelectCommand = New SqlCommand(Query, Connect)

    Adapter.Fill(DataTable)
    Admin.items_category_combobx.DataSource = DataTable
    Admin.items_category_combobx.DisplayMember = "Column_name"
    Admin.items_category_combobx.ValueMember = "Column_name"

End Sub

Please avoid using the comments to write code. Edit your question instead. — Zohar Peled, Sep 01 '16 at 06:47
Don't put it as a comment. Put that in your post to help improve your question so people can help you better. — Aethan, Sep 01 '16 at 06:47

Akshey Bhat · Accepted Answer · 2016-09-01T12:45:46.830

0

Connect.ChangeDatabase("database-name")    
Query = "select Column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='your-Table-name'"
    Admin.items_category_combobx.DataSource = DataTable
    Admin.items_category_combobx.DisplayMember = "Column_name"
    Admin.items_category_combobx.ValueMember = "Column_name"

Change your query as above and you will get column names in your dataset. For adding column, take column name and type from user. build and execute an alter table query for adding column from it.

edited Sep 01 '16 at 12:45

answered Sep 01 '16 at 06:55

Akshey Bhat

8,227
1
20
20

what exactly is the problem? – Akshey Bhat Sep 01 '16 at 07:01
I edited my question with the Query use sent . for you to see how I use it . is that correct ? – Ralph Sep 01 '16 at 07:03
set displaymember and valuemember to "column_name". You have set it incorrectly – Akshey Bhat Sep 01 '16 at 07:04
check my modified answer – Akshey Bhat Sep 01 '16 at 07:10
you mean the rest are correct already ? I already change it to Column_name ,still nothing happens . by the way Im a module , do i need to put it on the combobox ? – Ralph Sep 01 '16 at 07:11
datasource should be datatable not adapter. Yes these properties are of combobox – Akshey Bhat Sep 01 '16 at 07:18
Adapter.SelectCommand.ExecuteNonQuery() is not required Adapter,Fill will get the data for you – Akshey Bhat Sep 01 '16 at 07:25
thanks I already did what you said , but still my combobox is Empty – Ralph Sep 01 '16 at 07:32
try running the query directly against the database where the table is present and see if you are getting any results. – Akshey Bhat Sep 01 '16 at 08:41
the result is Empty – Ralph Sep 01 '16 at 10:22
thats why you are not able to see any items in drop down. check if table name is correct or not. check if it exists in the database against which you are running the query. add "USE " statement before running the query also. – Akshey Bhat Sep 01 '16 at 10:25
I can see the Column names in the Result already when I use the "USE " but I run query on the database Directly , how can do this in vb.net ? – Ralph Sep 01 '16 at 11:34
In vb.net connection string set value of target database to your database – Akshey Bhat Sep 01 '16 at 11:46
can you write it as a syntax ? I dont get it . please ? – Ralph Sep 01 '16 at 11:50
add a line of code before query variable as given in my modified answer – Akshey Bhat Sep 01 '16 at 12:46
I already modified the code as what you have said. but still nothing . – Ralph Sep 02 '16 at 02:33
Please share the value of variable "ConnectionString". – Akshey Bhat Sep 02 '16 at 06:28
The ConnectionString there is my Connection to the Database – Ralph Sep 02 '16 at 09:08
"LUSPOC-PC":-- Is this the name of PC for database server? – Akshey Bhat Sep 02 '16 at 10:27
Yes it is. I copied it from the Dialog box of SQL SERVER – Ralph Sep 02 '16 at 10:58
correct your database name in connection string. Is it "Sales_Invnetory" or "Sales_Inventory"? – Akshey Bhat Sep 02 '16 at 12:20

score 0 · Answer 2 · answered Sep 01 '16 at 08:08

          Using sqlconn As New SqlConnection("your connection string")
                sqlconn.Open()
                Using SqlCommand As New SqlCommand("select Column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Table_Categories'", sqlconn)
                    SqlCommand.CommandTimeout = 0                     
                    Using sqlAdp As New SqlDataAdapter(SqlCommand)
                        sqlAdp.Fill(dt)
                    End Using
                End Using
        End Using
        ComboBox1.DataSource = dt
        ComboBox1.DataTextField = "Column_name" 
        ComboBox1.DataValueField = "Column_name"
        ComboBox1.DataBind()

do i have to declare DataTextField,DataValueField,DataBind() ? its not working — Ralph, Sep 01 '16 at 11:42

How can you fill combobox in vb.net with column name from Microsoft SQL Server

2 Answers2

Linked