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 that 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 in 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
To have dependent (cascading or master/slave) ComboBox columns in DataGridView, you can follow this steps:
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.
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.
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