Databound comboboxes repeating data

[Españolita]

I have several comboboxes on various tabs which I want to load from the database to give a simple list of student names and group names.
The problem is, if I have 2 comboboxes for the student names, the data is repeated twice in both comboboxes. And if I have 2 comboboxes for the Groups, the data is repeated 5 times in all the comboboxes.
The code I have is below.

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Visible = True
        Dim SQL As String = "SELECT stID, stName FROM tblStudent ORDER BY stName"
        Dim Val As String = "stID"
        Dim disp As String = "stName"
        FillCombo(SQL, cboName, Val, disp, "Student")
        FillCombo(SQL, cboStudentName, Val, disp, "Student")
        SQL = "SELECT grID, grName FROM tblGroup ORDER BY grName"
        Val = "grID"
        disp = "grName"
        FillCombo(SQL, cboStudentGroup, Val, disp, "Group")
        FillCombo(SQL, cboRegistersGroup, Val, disp, "Group")
        FillCombo(SQL, cboReportsGroup, Val, disp, "Group")
        FillCombo(SQL, cboAttendanceGroup, Val, disp, "Group")
        FillCombo(SQL, cboMaintGroupName, Val, disp, "Group")
    End Sub
 
    Private Sub FillCombo(ByVal SQL As String, ByVal CBO As ComboBox, ByVal val As String, ByVal disp As String, ByVal tblName As String)
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0"
        dbSource = "Data Source=" & Application.StartupPath & "\Student.mdb"
        Conn.ConnectionString = dbProvider & ";" & dbSource
        Conn.Open()
        da = New OleDb.OleDbDataAdapter(SQL, Conn)
        da.Fill(ds, tblName)
        Conn.Close()
        CBO.DataSource = ds.Tables(tblName)
        CBO.ValueMember = val
        CBO.DisplayMember = disp
    End Sub

Why is it repeating the data the same number of times as there are comboboxes and how do I solve it?

[jmcilhinney]

Each time you call FillCombo you are filling the specified DataTable from the database. You call FillCombo twice with the table name "Student" so you are fill that DataTable twice, so you see two lots of the same data. If you want to display the same data in multiple ComboBoxes then you fill the DataTable ONCE only and then bind it multiple times, so you need to separate the code that retrieves the data from the code that binds it to the ComboBox.

Also, if you bind a DataTable directly to multiple controls then making a selection in one will make the same selection in the others. You need to bind your DataTable to multiple BindingSources and then bind one BindingSource to each ComboBox.

[Españolita]

below is my solution which works but it seems a bit cobbled together. Is there a more elegant way to do it?
Also, as the database is changed, will names be added/removed from these comboboxes as they are added/romoved from the database?
finally, What should I dispose of and when?

Dim Conn As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
 
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'Hide form while drawing to prevent flickering
        Me.Visible = False
 
        'set up tab control
        TabControl1.Alignment = TabAlignment.Left
        TabControl1.SizeMode = TabSizeMode.Fixed
        TabControl1.DrawMode = TabDrawMode.OwnerDrawFixed
        TabControl1.ItemSize = New Size(70, 130)
 
        'Show form again once all drawing has been done
        Me.Visible = True
 
        Dim SQL As String = "SELECT stID, stName FROM tblStudent ORDER BY stName"
        Dim sVal As String = "stID"
        Dim disp As String = "stName"
        FillComboStudent(SQL, sVal, disp, "Student")
        SQL = "SELECT grID, grName FROM tblGroup ORDER BY grName"
        sVal = "grID"
        disp = "grName"
        FillComboGroup(SQL, sVal, disp, "Group")
    End Sub
    Private Sub FillComboStudent(ByVal SQL As String, ByVal Sval As String, ByVal disp As String, ByVal tblName As String)
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0"
        dbSource = "Data Source=" & Application.StartupPath & "\Student.mdb"
        Conn.ConnectionString = dbProvider & ";" & dbSource
        Conn.Open()
        da = New OleDb.OleDbDataAdapter(SQL, Conn)
        da.Fill(ds, tblName)
        Conn.Close()
        Dim bndStudentNames1 As New BindingSource
        Dim bndStudentNames2 As New BindingSource
        bndStudentNames1.DataSource = ds.Tables(tblName)
        bndStudentNames2.DataSource = ds.Tables(tblName)
        cboName.DataSource = bndStudentNames1
        cboStudentName.DataSource = bndStudentNames2
        cboName.ValueMember = Sval
        cboName.DisplayMember = disp
        cboStudentName.ValueMember = Sval
        cboStudentName.DisplayMember = disp
    End Sub
 
    Private Sub FillComboGroup(ByVal SQL As String, ByVal Sval As String, ByVal disp As String, ByVal tblName As String)
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0"
        dbSource = "Data Source=" & Application.StartupPath & "\Student.mdb"
        Conn.ConnectionString = dbProvider & ";" & dbSource
        Conn.Open()
        da = New OleDb.OleDbDataAdapter(SQL, Conn)
        da.Fill(ds, tblName)
        Conn.Close()
        Dim bndGroupNames1 As New BindingSource
        Dim bndGroupNames2 As New BindingSource
        Dim bndGroupNames3 As New BindingSource
        Dim bndGroupNames4 As New BindingSource
        Dim bndGroupNames5 As New BindingSource
        bndGroupNames1.DataSource = ds.Tables(tblName)
        bndGroupNames2.DataSource = ds.Tables(tblName)
        bndGroupNames3.DataSource = ds.Tables(tblName)
        bndGroupNames4.DataSource = ds.Tables(tblName)
        bndGroupNames5.DataSource = ds.Tables(tblName)
        cboStudentGroup.DataSource = bndGroupNames1
        cboRegistersGroup.DataSource = bndGroupNames2
        cboReportsGroup.DataSource = bndGroupNames3
        cboAttendanceGroup.DataSource = bndGroupNames4
        cboMaintGroupName.DataSource = bndGroupNames5
 
        cboStudentGroup.ValueMember = Sval
        cboStudentGroup.DisplayMember = disp
        cboRegistersGroup.ValueMember = Sval
        cboRegistersGroup.DisplayMember = disp
        cboReportsGroup.ValueMember = Sval
        cboReportsGroup.DisplayMember = disp
        cboAttendanceGroup.ValueMember = Sval
        cboAttendanceGroup.DisplayMember = disp
        cboMaintGroupName.ValueMember = Sval
        cboMaintGroupName.DisplayMember = disp
    End Sub

[jmcilhinney]

Like I said, separate the code to retrieve the data and the code to bind the data. Write a FillTable method and call it once for each DataTable that you want to populate. Write a BindComboBox method and call it for each ComboBox that you want to bind. The two have nothing directly to do with each other.