Clearing/Refreshing ComboBox

Question

Good Day!

I'm having trouble with my Combox that was bound to a DataSource. Here it goes, I have two comboBoxes that were bound to columns to my database table, the purpose of the first comboBox is to filter the options in second comboBox. When I choose an item in the first comboBox and then changed it, the Items in ComboBox 2 adds up, It's like collecting the items. I tried clearing the DataSet but I'm not sure if I'm doing that right, is clearing the DataSet is the solution? If yes, how can I implement it? If not, what could be the best solution?

I've searched enough before posting this question and tried some codes, but doesn't seem to answer my problem.

Any help would be much appreciated.

'FinishedTypeComboBox(first ComboBox) used for filtering second ComboBox
con.ConnectionString= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\TROY\Documents\Visual Studio 2010\Projects\SNISISIS_9\LastTable.accdb" 
 
con.Open() 'open the connection to the Database
sql = "SELECT DISTINCT FinishedType FROM Finished_Products ORDER BY FinishedType" 
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "TypeBook")
 For Each dRow As DataRow In ds.Tables("TypeBook").Rows
 FinishedTypeComboBox.Items.Add(dRow.Item("FinishedType"))
Next dRow
'This is how I populate the FinishedComboBox
'Below is my code for second comboBox(NameComboBox)
Private Sub FinishedTypeComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles FinishedTypeComboBox.SelectedIndexChanged
        If Not FinishedTypeComboBox.SelectedIndex = -1 Then
            sql2 = "SELECT FinishedName FROM Finished_Products WHERE FinishedType = '" & FinishedTypeComboBox.Text & "'"
            da2 = New OleDb.OleDbDataAdapter(sql2, con)
            da2.Fill(ds2, "NameBook")
            For Each dRow1 As DataRow In ds2.Tables("NameBook").Rows
                NameComboBox.Items.Add(dRow1.Item("FinishedName"))
            Next dRow1
        End If
    End Sub

Answer 1

hi Troy

instead of iterating through the rows of the resulting table, I would set the DataSource, DisplayMember and ValueMember properties.

FinishedTypeComboBox.DataSource = TypeBook
            FinishedTypeComboBox.DisplayMember = "FinishedType"
            FinishedTypeComboBox.ValueMember = "FinishedType"

then when you want to clear the items you can set the DataSource to Nothing

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        FinishedTypeComboBox.DataSource = Nothing
        
    End Sub

---

“This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.”

Answer 2

Hi iwavila,

I tried the code that you suggest, but I received and Exception:

"Complex DataBinding accepts as a data source either an IList or an IListSource."

What could be the problem?

Answer 3

where is says DataSource = TypeBook, replace TypeBook with

ds.Tables("TypeBook")

---

“This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.”

Answer 4

One more thing, where should I put the DataSource = Nothing in here:

 Private Sub FinishedTypeComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles FinishedTypeComboBox.SelectedIndexChanged
        NameComboBox.DataSource = Nothing
        sql2 = "SELECT FinishedName FROM Finished_Products WHERE FinishedType = '" & FinishedTypeComboBox.Text & "'"
        da2 = New OleDb.OleDbDataAdapter(sql2, con)
        da2.Fill(ds2, "NameBook")
        NameComboBox.DataSource = Nothing
        If Not FinishedTypeComboBox.SelectedIndex = -1 Then
            NameComboBox.DataSource = ds2.Tables("NameBook")
            NameComboBox.DisplayMember = "FinishedName"
            NameComboBox.ValueMember = "FinishedName"
        End If
    End Sub

I tried this code of mine but does not clear well, it still adds the items.

Thanks!

Answer 5

Augusto,

Your code is confusing, you are talking about 2 comboboxes but we see one. If I read your problem 10 times then maybe is the solution not like Joe suggested but more like on our website. Be aware a Listbox and a combobox behave the same for this.

http://www.vb-tips.com/RelatedListBoxWithDataView.ASPX

---

Success
Cor

Answer 6

Cor, I tried the code from the link you gave. Looks like I'm missing something because the system throws an exception and it says that It cannot find the column.

Answer 7

Do

dim dt as New DataTable("NameBook")
da2.Fill(dt)

instead of that current part.

---

Success
Cor

Answer 8

Cor, when I'm about to run the program, another exception occured at the SelectIndexChanged.

Answer 9

your code is almost invisivble for me in this forum. I use threaded view. 

However with an enlarger I can see that your select statement is wrong. It does not contain the FinishedName

Select FinishedName, FinishedType from ...........................

---

Success
Cor

Answer 10

Thanks for reply Cor,

modified my code, but unfortunately didn't change a bit.

I searched the net for hours and no one seemed to know the right answer.

Some says use .Clear, = Nothing, SelectedIndex = -1 etc, nothing worked right.

Answer 11

Maybe if you show your current code than somebody can help you, currently we are simply looking in the complete darkness.

(Not in an image please)

---

Success
Cor