Populating a Combobox - What's the Best Way?

[The_Grudge]

Is there a preferred way to populate a combobox in VB.NET ?

I'm relatively new to .NET (although fluent in VB 6.0), and I'm trying to write an add on to an existing program. The add on is simply to filter the items in a treeview control.

For example, when the user selects "Johnny B. Goode" from the combobox, I want to see only Johnny's items in the treeview. There is an existing dataset in the application that queries the "user" table, so I'm assuming I could use that to populate the combobox which contains a list of users. I also see an option to create "SQL" in the control.

What's the best way, or does it really matter?

I'd like to get it setup so it puts the user name in the combobox and then the "ID" into the .tag property.

[.paul.]

what is the structure of the user table + how do you populate your treeview?
what you're asking would be easily accomplished through querying the db.

[The_Grudge]

Hi .paul.

The structure of the user table is dead simple. It's just ID (e.g. 1,2,3,4), UserName (e.g. John Smith), and Active (e.g. "True" or "False"). I can't speak to the treeview population that well because I didn't write the app. It appears as though the original author loops through a "Dataset" to populate it. The app is absolutely packed with Datasets.

All I need to do is query the user table to put the user name in the combobox and the ID in behind the scenes somewhere so I can write that value to another table where the other data is stored.

I can easily "bind" the combobox to the user table by clicking the 'create SQL' tab within the control on the form, but there's an issue when I do that. I need to be able to add my own option for "All" in case the user doesn't want to filter the list by user.

I could just do what I know from VB6 and run an SQL query and loop through populating the combobox, but I've had difficulty finding a decent example of that online.

Being new to VB.NET I easily get caught up in terms like DataReader, TableAdapter, and more.

[.paul.]

here's how to populate your combobox from a db:

'this is for access
Imports System.Data.OleDb
'this is for sql
Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'to use this you'll need to set your target cpu to x86
        'change the filename in the connection string. if you're not using access you can get a suitable connection string at:
        'www.connectionstrings.com

        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;")
        Dim da As New OleDbDataAdapter("SELECT [ID], [UserName] FROM [user]", con)

        ''sqlclient version
        'Dim con As New SqlConnection("connection string")
        'Dim da As New SqlDataAdapter("SELECT [ID], [UserName] FROM [user]", con)

        Dim dt As New DataTable
        da.Fill(dt)

        'add an All option to datatable
        Dim dr As DataRow = dt.NewRow
        dr.ItemArray = New Object() {-1, "All"}
        dt.Rows.InsertAt(dr, 0)

        ComboBox1.DisplayMember = "UserName"
        ComboBox1.ValueMember = "ID"
        ComboBox1.DataSource = dt
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        'this is the ID field
        MsgBox(ComboBox1.SelectedValue.ToString)
    End Sub

End Class

[The_Grudge]

here's how to populate your combobox from a db:

'this is for access
Imports System.Data.OleDb
'this is for sql
Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'to use this you'll need to set your target cpu to x86
        'change the filename in the connection string. if you're not using access you can get a suitable connection string at:
        'www.connectionstrings.com

        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;")
        Dim da As New OleDbDataAdapter("SELECT [ID], [UserName] FROM [user]", con)

        ''sqlclient version
        'Dim con As New SqlConnection("connection string")
        'Dim da As New SqlDataAdapter("SELECT [ID], [UserName] FROM [user]", con)

        Dim dt As New DataTable
        da.Fill(dt)

        'add an All option to datatable
        Dim dr As DataRow = dt.NewRow
        dr.ItemArray = New Object() {-1, "All"}
        dt.Rows.InsertAt(dr, 0)

        ComboBox1.DisplayMember = "UserName"
        ComboBox1.ValueMember = "ID"
        ComboBox1.DataSource = dt
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        'this is the ID field
        MsgBox(ComboBox1.SelectedValue.ToString)
    End Sub

End Class

Thanks. Ill try it tomorrow from work. One question. The previous developer has a connection setup in the Project-->properties. Is it a detrimental to performance if I create my own connection? If so, is there a way I can use his existing connection?

[dday9]

You can certainly use the existing connection by doing something along these lines:

Dim con As OleDBConnectionString = My.Settings.ExistingConnectionString

Many times the connection string gets saved in the settings because it was added via the wizard tool in visual studios.

[.paul.]

The previous developer has a connection setup in the Project-->properties. Is it a detrimental to performance if I create my own connection? If so, is there a way I can use his existing connection?

not sure about that. start a new thread for that question.

[The_Grudge]

You can certainly use the existing connection by doing something along these lines:

Dim con As OleDBConnectionString = My.Settings.ExistingConnectionString

Many times the connection string gets saved in the settings because it was added via the wizard tool in visual studios.

Thanks. Part of the problem w this app I think is that the whole thing was put together with wizards!!