Clear Dataset on ComboBox 1

[WelshyWizard]

Hi all,

I'm setting the value of a combobox (combobox3) off selection made from another combobox (combobox2). The value chosen gets passed through a sqlcommand and is returned to combobox3 using a dataset.
the code I'm using is listed below:

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        ComboBox3.Visible = True

        ComboBox3.DataSource = Nothing
        ComboBox3.Items.Clear()

        SqlConnection1.Open()
        SqlDataAdapter2.SelectCommand = SqlCommand2
        SqlCommand2.Parameters("@Cat1").Value = "" & ComboBox2.Text & ""
        SqlCommand2.ExecuteNonQuery()
        SqlDataAdapter2.Fill(DsCat21)
        ComboBox3.DataSource = DsCat21.Category2
        ComboBox3.DisplayMember = "Cat2"
        SqlConnection1.Close()

    End Sub

End Class

The problem I'm getting is puzzling me.
The code updates combobox3 as you would expect, however, when you then make a different selection from combobox2, combobox3 updates but not as I would expect. Instead of the

  ComboBox3.DataSource = Nothing
        ComboBox3.Items.Clear()

code clearing the old list, it adds the new items to the bottom of the existing items.
If I put the clear code under a button it works!

Any ideas guys? This has baffled me!!!

Cheers

Today is the tomorrow you worried about yesterday - and all is well.....

 

[mansii]

SqlDataAdapter2.Fill(DsCat21)

The above code will add any existing new rows to the existing rows of the DataTable.
Try clearing out the DataTable before filling it out with the new parameters:

DsCat21.Category2.Rows.Clear
SqlDataAdapter2.Fill(DsCat21)

Also, I think that it be better if you put the SelectCommand parameters before assiging the command to dataadapter.

SqlCommand2.Parameters("@Cat1").Value = "" & ComboBox2.Text & ""
SqlDataAdapter2.SelectCommand = SqlCommand2

One other thing that botthered me is the ExecuteNonQuery function. As far as I concern, the function is a one way trip. Meaning that it will not return any records. In addition, you can remove the SqlCommand2.ExecuteNonQuery() line.

Hope this helps.

 

[WelshyWizard]

Excellent! Thanks very much mansii.. I'm picking .NET up as I go along so I may be on here a few more times!!!!

Thanks.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[mansii]

Sure, and thank's for the star.