DataGridView Cascading/Dependent ComboBox Columns

Question

So I work from time to time in Winforms on a legacy app and am not familiar with best practices at all times with binding objects. Basically I have a three part set where I have two people, they may have only one product, but that product could cause the possibility to have different sets of SKUs. Is there a way to trigger an event and population of a combobox from the values of a first combobox? I have been looking around and I am either finding basic data of just how to bind a combobox(I can do that fine) or do something with how you bind it. Not binding after a dependent parent change is triggered and changing the dataset. Example below:

POCOS:

Public Class Person
  Public Property PersonID As Integer
  Public Property FirstName As String
  Public Property LastName As String
  Public Property ProductId As Integer
  Public Property SkuId As Integer
End Class

Public Class Product
  Public Property ProductId As Integer
  Public Property Description As String
End Class

Public Class Sku
  Public Property SKUId As Integer
  Public Property ProductId As Integer
  Public Property Description As String
End Class

Main code example (basic UI really only has a Datset labeled 'ds' that matches nearly identically the Person and Product POCOS with datatables. A datagridview 'dgv' whose columns are bound to data in Person EXCEPT for a column called SKU ta has no binding as I want to bind it after the fact and that is where I am failing miserably at.

Update 9-13-2016 I can get the below code to work EXCEPT in certain large scale solutions(the whole reason I did this). It basically will NOT execute the line that casts the cell() to a datagridviewcomboboxcell and ignores it and jumps over the line. No reason why, it just jumps over it. I am wondering if on larger classes the datagrid views can become corrupt or something.

Main Code:

Private _people As List(Of Person) = New List(Of Person)
Private _products As List(Of Product) = New List(Of Product)
Private _SKUs As List(Of Sku) = New List(Of Sku)
Private _initialLoadDone = False
Private _currentRow As Integer? = Nothing

Private Sub DynamicComboBoxDoubleFill_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    _products = New List(Of Product)({
                                     New Product With {.ProductId = 1, .Description = "Offline"},
                                     New Product With {.ProductId = 2, .Description = "Online"}
                                     })

    Dim s = ""
    For Each o In _products
      Dim row As DataRow = ds.Tables("tProducts").NewRow
      row("ProductId") = o.ProductId
      row("Description") = o.Description
      ds.Tables("tProducts").Rows.Add(row)
    Next

    _SKUs = New List(Of Sku)({
     New Sku With {.SKUId = 1, .ProductId = 1, .Description = "Mail"},
     New Sku With {.SKUId = 2, .ProductId = 1, .Description = "Magazine"},
     New Sku With {.SKUId = 3, .ProductId = 2, .Description = "Email"},
     New Sku With {.SKUId = 4, .ProductId = 2, .Description = "APIRequest"}
    })

    Dim items = _SKUs

    _people = New List(Of Person)({
      New Person With {.PersonID = 1, .FirstName = "Emily", .LastName = "X", .ProductId = 1, .SkuId = 1},
      New Person With {.PersonID = 2, .FirstName = "Brett", .LastName = "X", .ProductId = 2, .SkuId = 3}
                                  })
    For Each p In _people
      Dim row As DataRow = ds.Tables("tPeople").NewRow
      row("PersonId") = p.PersonId
      row("FirstName") = p.FirstName
      row("LastName") = p.LastName
      row("ProductId") = p.ProductId
      row("SkuId") = p.SkuId
      ds.Tables("tPeople").Rows.Add(row)
    Next

    For Each row As DataGridViewRow In dgv.Rows
      ArrangeValuesForSKUComboBox(row)
    Next

    _initialLoadDone = True
  End Sub

  Private Sub ArrangeValuesForSKUComboBox(row As DataGridViewRow)
    Dim productId = CInt(row.Cells("ProductId")?.Value)
    Dim skus = _SKUs.Where(Function(x) x.ProductId = productId).ToList().Select(Function(x) New With {Key .SkuId = x.SKUId, .SkuDesc = x.Description}).ToList()

    Dim cell = row.Cells("SKU")
    'Yeah I don't always work.  In this example I do, in others I won't.
    'For this reason I just want more ideas.  I don't care if you completely blow up how the binding is done and do something else entirely.
    Dim combobox = CType(cell, DataGridViewComboBoxCell)
    combobox.DataSource = skus
    combobox.ValueMember = "SKUId"
    combobox.DisplayMember = "SkuDesc"
    combobox.Value = skus.FirstOrDefault()?.SkuId
  End Sub

  Private Sub dgv_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgv.CellValueChanged
    If _initialLoadDone Then
      Dim headerText As String = TryCast(sender, DataGridView).Columns(e.ColumnIndex).HeaderText
      If headerText = "PRODUCT" Then
        ArrangeValuesForSKUComboBox(dgv?.CurrentRow)
      End If
    End If
  End Sub

Answer 1

To have dependent (cascading or master/slave) ComboBox columns in DataGridView, you can follow this steps:

1. Set DataSource of slave column to all available values.

   Goal: Here the goal is prevent rendering errors at first load, so all slave combo boxes can show value correctly.

2. Hanlde EditingControlShowing event of the grid and check if the current cell is slave combo cell, then get the editing control using e.Control which is of type DataGridViewComboBoxEditingControl. Then check the value of master combo cell and set the DataSource property of editing control to a suitable subset of values based on the value of master combo cell. If the value of master cell is null, set the data source to null.

   Goal: Here the goal is setting data source of slave combo to show only suitable values when selecting values from slave combo.

3. Handle CellValueChanged and check if the current cell is master combo, then set the value for dependent cell to null.
   Note: Instead of setting the value of slave cell to null, you can set it to first available valid value based on master cell value.

   Goal: Here the goal is prevent the slave combo to have invalid values after the value of master combo changed, so we reset the value.

Following above rules you can have as many dependent combo boxes as you need.

Example

In below example I have a Country (Id, Name) table, a State(Id, Name, CountryId) table and a Population(CountryId, StateId, Population) table. And I want to perform data entry for Population table using 2 combo columns for country and state and a text column for population. I know this is not a normal db design, but it's just for example of having master/slave (dependent) combo box columns in grid:

Private Sub EditingControlShowing(sender As Object, _
    e As DataGridViewEditingControlShowingEventArgs) _
    Handles PopulationDataGridView.EditingControlShowing

    Dim grid = DirectCast(sender, DataGridView)
    If (grid.CurrentCell.ColumnIndex = 1) Then 'State column
        Dim combo = DirectCast(e.Control, DataGridViewComboBoxEditingControl)
        If (grid.CurrentRow.Cells(0).Value IsNot DBNull.Value) Then
            Dim data = Me.DataSet1.State.AsDataView()
            data.RowFilter = "CountryId = " + grid.CurrentRow.Cells(0).Value.ToString()
            combo.DataSource = data
        Else
            combo.DataSource = Nothing
        End If
    End If
End Sub

Private Sub CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) _
    Handles PopulationDataGridView.CellValueChanged
    Dim grid = DirectCast(sender, DataGridView)
    If (e.ColumnIndex = 0 And e.RowIndex >= 0) Then 'Country Column
        grid.Rows(e.RowIndex).Cells(1).Value = DBNull.Value 'State Column 
    End If
End Sub