How to filter unbound datagridview with combobox that's outside grid

[Proctor]

Hello: I have an unbound datagridview and a seperate dropdown outside of the grid. When the user makes a selection from this dropdown, I would like to filter on their selection in order to repopulate my grid. I searched through all the postings in the forum but only found one post similar...however it had to do with a bound datagrid.

All my cells in the datagrid are of DataGridViewComboBoxColumn type.

I need to also tell you that I've got another little issue when I first populate each of the DataGridViewComboBoxColumn controls using the code below: It bombs on when I try to set the valuemember, so I had to rem it out. Isn't this suppose to be like setting the selected index?

 Sub PopulateColor(ByVal iColIndex)
        Dim myDB As New OleDb.OleDbConnection(sConnectionString)
        Dim myDA As New OleDb.OleDbDataAdapter("Select Color,ColorId from tblColors order by Color", myDB)
        Dim myDS As New Data.DataSet
        Dim cbn As DataGridViewComboBoxColumn = DataGridView1.Columns(iColIndex)

        myDA.Fill(myDS, "tblColors")
        With cbn
            .DataSource = myDS.Tables("tblColors")
            .DisplayMember = "Color"
'doesn't like this line? why???
Here's the error I'm getting: "error formatting, display"

            '.ValueMember = "ColorId"
        End With
        myDB.Close()

    End Sub

Here's my code that I'm using to try to filter on a selected value in my seperate drop-down control:

'onChange on my drop-down:

    Private Sub cboProfiles_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProfiles.SelectionChangeCommitted
        If Not IsNothing(cboProfiles.SelectedValue) Then
            Dim sql As String = ""
            sql = "SELECT tblProfileName.ProfileId, tblProfileName.ProfileName, tblStates.StateId,tblStates.StateName, tblStates.Zone1, tblStates.Color1, tblStates.Brightness1,tblStates.Flash1, tblStates.Zone2, tblStates.Color2, tblStates.Brightness2,tblStates.Flash2, tblStates.Zone3, tblStates.Color3, tblStates.Brightness3, tblStates.Flash3, tblStates.Zone4, tblStates.Color4, tblStates.Brightness4,tblStates.Flash4, tblStates.SpeedId, tblStates.Wheel FROM tblProfileName,tblStates where tblProfileName.ProfileId = tblStates.ProfileId and tblStates.ProfileId" & cboProfiles.SelectedValue.ToString()
            Dim myDB As New OleDb.OleDbConnection(sConnectionString)
            Dim myDA As New OleDb.OleDbDataAdapter("SELECT tblProfileName.ProfileId, tblProfileName.ProfileName, tblStates.StateId,tblStates.StateName, tblStates.Zone1, tblStates.Color1, tblStates.Brightness1,tblStates.Flash1, tblStates.Zone2, tblStates.Color2, tblStates.Brightness2,tblStates.Flash2, tblStates.Zone3, tblStates.Color3, tblStates.Brightness3, tblStates.Flash3, tblStates.Zone4, tblStates.Color4, tblStates.Brightness4,tblStates.Flash4, tblStates.SpeedId, tblStates.Wheel FROM tblProfileName,tblStates where tblProfileName.ProfileId = tblStates.ProfileId and tblStates.ProfileId=" & cboProfiles.SelectedValue.ToString(), myDB)
            Dim myDS As New Data.DataSet
            Dim myDR As Data.DataRow

            Try
                myDA.Fill(myDS, "profiles")
                Dim profiles As DataTable
                profiles = myDS.Tables("profiles")

                If profiles.Rows.Count = 1 Then
'first, populate other textboxes that are not in the grid

                    For Each myDR In profiles.Rows

                        txtZone1.Text = myDR("Zone1")
                        txtZone2.Text = myDR("Zone2")
                        txtZone3.Text = myDR("Zone3")
                        txtZone4.Text = myDR("Zone4")
                        txtState1.Text = myDR("Statename")

'now, populate the grid...for now, just trying to just get one of my cells (DataGridViewComboBoxColumn) to set it's selected index to value from filtered ds:
                        With Me.DataGridView1.Rows
                            Dim cbn As DataGridViewComboBoxColumn = DataGridView1.Columns(0)

                            With cbn
                                .ValueMember = myDR("Color1")
                            End With
                        End With
                    Next
                End If
                myDB.Close()
            Catch
                MsgBox("An error occurred")
            End Try
        Else
        End If
    End Sub

Any help is really appreciated.
Proctor

[Proctor]

Had to repost to let you know that in regards to populating my
DataGridViewComboBoxColumn

When I do this it works:

 Sub PopulateColor(ByVal iColIndex)
        Dim myDB As New OleDb.OleDbConnection(sConnectionString)
        Dim myDA As New OleDb.OleDbDataAdapter("Select Color,colorid from tblColors order by Color", myDB)
        Dim myDS As New Data.DataSet
        Dim cbn As DataGridViewComboBoxColumn = DataGridView1.Columns(iColIndex)

        myDA.Fill(myDS, "tblColors")
        With cbn
            .DataSource = myDS.Tables("tblColors")
            .DisplayMember = "Color"
'set valuemember ="color"
            .ValueMember = "Color"
        End With
        myDB.Close()

    End Sub

however, when I exchange field color for colorid, is when it's unhappy:

'set valuemember ="colorid"

Not sure why it doesn't like that field.

Proctor

[Clanguage]

Are you sure that the field ColorId exists in your table? Is it spelt correctly?

[Proctor]

Thanks for your reply...yes, I'm positive. I checked it and also made sure that it was spelled correctly.

Thanks again,
proctor