A ComboBox is KILLING ME!!! [RESOLVED]

I'm trying to populate my combobox from a dataset using the following code that I picked up from the forums :) thnx

VB Code:

Private Sub PopCBOFromDB(ByRef cbo As ComboBox)
        Dim Conn As New OleDb.OleDbConnection(ConnectionString)
        Dim Cmd As OleDb.OleDbCommand = Conn.CreateCommand
        Cmd.CommandText = SQLString
        Conn.Open()
        Dim DA As New OleDb.OleDbDataAdapter
        DA.SelectCommand = Cmd
        DA.Fill(DS)
        cbo.DataSource = DS.Tables("Table")
        cbo.DisplayMember = Field
    End Sub
 
    Private Sub cboBON_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboBON.DropDown
        PopCBOFromDB(cboBON)
    End Sub

The problem is that when I change anything to the actual database (like delete a record in the table I'm pulling my DisplayMember field from) it still shows that same record in the combobox... I'm having trouble going the other way too. e.g. when I add a record to the table it doesn't show the new record.

However, if I close the form and open it again, it will show any changes that were made while it was open the last time. ????? I don't know ?????

Can anyone of you friendly genious types help me?

Thanks,

Squirrelly1 :D

Hi
Your forgot to clear the content the dataset before filling it

VB Code:

Private Sub PopCBOFromDB(ByRef cbo As ComboBox)
        Dim Conn As New OleDb.OleDbConnection(ConnectionString)
        Dim Cmd As OleDb.OleDbCommand = Conn.CreateCommand
        Cmd.CommandText = SQLString
        Conn.Open()
        Dim DA As New OleDb.OleDbDataAdapter
        DA.SelectCommand = Cmd
        DS.Tables.Clear
        DA.Fill(DS)
        cbo.DataSource = nothing
        cbo.DataSource = DS.Tables("Table")
        cbo.DisplayMember = Field
    End Sub

Regards
Jorge

I tried that before and I just tried it again as you posted it. It's doing the same thing :(

Any other ideas?

Squirrelly1

Hi squirrelly1,

I think the problem is that the DropDown event is fired soon after the list portion of the combobox is shown.

If the combobox must be refreshed each time the user click the arrow to drop-down the list portion, I think you should consider using the Enter event instead.

Good Suggestion.

I tried that before too, believe it or not. And it didn't work then any more than it worked now... :( I even implimented the datatable clearing thinking it could have actually been two causes for one problem.

Any other ideas?

Try this now . The thing is you have refill the dataset again to get changes made to the source . You could do a boolean var , set it to true whenever changes made and call this sub if it's true.

VB Code:

Private Sub PopCBOFromDB(ByRef cbo As ComboBox)
        Dim Conn As New OleDb.OleDbConnection(ConnectionString)
        Dim Cmd As OleDb.OleDbCommand = Conn.CreateCommand
        Cmd.CommandText = SQLString
        Conn.Open()
        Dim DA As New OleDb.OleDbDataAdapter
        DA.SelectCommand = Cmd
        DS.Tables.Clear
        DA.Fill(DS)
        cbo.Items.Clear()
        cbo.DataSource = DS.Tables("Table")
        cbo.DisplayMember = Field
 
DA.Dispose()
 
    End Sub

I think the problem is not with PopCBOFromDB. It is with the actual event that calls the method. It seems the method is called after the list is dropped.

Why not call the PopCBOFromDB method just after the DB is updated.

Ok guys, thanks for the posts. I got it fixed.

I just decided to declare my oledb database objects within my form and just use the same ones over and over when i was updating the comboboxes and when i'm deleting the records.. this way everything is on the same page.

Thanks for your help guys,

Squirrelly1