removing/hiding db records dependent on previous selection

[lunaclover]

Hi - Here's what I'm trying to do, as you all may know by now having read my 9 million threads. I am connecting to a sql db and have several tables. Each table is connected to its own combobox. Depending on what a user selects for combobox2, for example, I want combobox4 to remove some of its selections, since certain combinations of options in combobox2 and in combobox4 aren't valid. If the user changes his/her selection in combobox2, the options need to reappear in combobox4. There are a million and one business rules I am trying to work with.

Here's my code, which of course isn't working.

Private Sub Infusion()

        If combobox2.ValueMember = "B" Or combobox2.ValueMember = "W" Then
            combobox4.Items.Remove("210")
            combobox4.Items.Remove("-18")
        Else
            'checks for values present so as not to duplicate
            If combobox4.Items.Contains("210") Or combobox4.Items.Contains("-18") Then
            'nothing
            Else
                combobox4.Items.Add("210")
                combobox4.Items.Add("-18")
            End If
        End If

    End Sub

It says
An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

Additional information: Cannot modify the Items collection when the DataSource property is set.

How should I go about this? I can't figure this out. I just want to really 'hide' it, so that way I won't have to replace a whole record later, which I really dont' know how to do.


Thanks for your help,
Luna

 

[jebenson]

What you will have to do is modify combobox4's datasource on the fly as the value selected in combobox2 changes.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[lunaclover]

Okay, thanks, but how do I go about doing that?

 

[jebenson]

Well, how are you getting your data into the combobox to begin with?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[lunaclover]

by creating a bunch of data adapters and data sets for each combobox, and then selecting each ones datasource, displaymember and valuemember.

 

[jebenson]

Do you create the dataadapters in code? If not, you will probably need to do so to do this. Anyway, here's a quick and dirty example. Note that this is very generic, as I don't know any of your field names, table names, etc.

This code sets the initial state of the comboboxes:

Dim da2 As SqlDataAdapter
Dim ds2 As DataSet

Dim da4 As SqlDataAdapter
Dim ds4 As DataSet

da2 = New SQLDataAdapter("Select MyField2Display, MyField2Value from MyTable2", MyConnection)

ds2 = New DataSet

da2.Fill(ds2)

combobox2.DataSource = ds2.Tables(0)
combobox2.DisplayMember = "MyField2Display"
combobox2.ValueMember = "MyField2Value"

da4 = New SQLDataAdapter("Select MyField4Display, MyField4Value from MyTable4", MyConnection)

ds4 = New DataSet

da4.Fill(ds4)

combobox4.DataSource = ds4.Tables(0)
combobox4.DisplayMember = "MyField4Display"
combobox4.ValueMember = "MyField4Value"


Now, in combobox2's SelectedIndexChanged event:

Private Sub combobox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles combobox2.SelectedIndexChanged

If combobox2.ValueMember = "B" Or combobox2.ValueMember = "W" Then
If Not da4 Is Nothing Then
da4.Dispose()
da4 = Nothing
End If

da4 = New SQLDataAdapter("Select MyField4Display, MyField4Value from MyTable4 [red]where MyField4Display<>'210' and MyField4Display<>'-18'[/red]", MyConnection)

If Not ds4 Is Nothing Then
ds4.Dispose()
ds4=Nothing
End If

ds4 = New DataSet

da4.Fill(ds4)

combobox4.DataSource = ds4.Tables(0)

combobox4.DisplayMember = "MyField4Display"

combobox4.ValueMember = "MyField4Value"
Else
If Not da4 Is Nothing Then
da4.Dispose()
da4 = Nothing
End If

da4 = New SQLDataAdapter("Select MyField4Display, MyField4Value from MyTable4", MyConnection)

If Not ds4 Is Nothing Then
ds4.Dispose()
ds4=Nothing
End If

ds4 = New DataSet

da4.Fill(ds4)

combobox4.DataSource = ds4.Tables(0)

combobox4.DisplayMember = "MyField4Display"

combobox4.ValueMember = "MyField4Value"

End If
End Sub

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson