How to make a ComboBox column with two data sources (for DataGridView)

Question

The data sources have the same structure, but different data. One would be used for rows that are saved (view mode), and the other one would be for rows that are being added or edited (edit/new rows). How can that be acomplished?

I have a standard foreign key column that references a standard lookup table which has an ID, Name and Active (bit). The combo box column uses that lookup table to show the list, but only active items. Let's say a lookup item is used and later deactivated (Active = 0). The combo box column now shows errors because the ID is not found in the list. Does anyone have any ideas how to solve it?

Answer 1

Not sure if you're still looking but I've just come across the same problem. Here's how I addressed it, hope it is of use to you.

Define a function which returns a DataGridViewComboCell with the appropriate datasource set (collection objects defined elsewhere, note that I'm using EntitySpaces in this example to populate the DataSource):

Private Function GetStockComboDataSource(ByVal type As eStockComboType) As DataGridViewComboBoxCell
        Try
            Dim cell As New DataGridViewComboBoxCell
            Select Case type
                Case eStockComboType.StockIn

                    cell.DataSource = Me.StockInCol.Query.LoadDataTable
                    cell.DisplayMember = "FullName"
                    cell.ValueMember = JCStockInMetadata.ColumnNames.StockItemID

                Case eStockComboType.StockItem

                    cell.DataSource = StockItemCol.Query.LoadDataTable
                    cell.ValueMember = JCStockItemMetadata.ColumnNames.StockItemID
                    cell.DisplayMember = "FullName"
            End Select

            Return cell
End Function

Now when it comes to setting the combo DataSource (I use the RowEnter event here for example):

    Private Sub dgvStock_RowEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvStock.RowEnter
        Try
            If IsNumeric(Me.dgvStock.Rows(e.RowIndex).Cells("ID").Value) Then
                Dim intValue As Integer = Convert.ToInt32(Me.dgvStock.Rows(e.RowIndex).Cells("Stock Item").Value)
                Me.dgvStock.Rows(e.RowIndex).Cells("Stock Item") = GetStockComboDataSource(eStockComboType.StockItem)
                Me.dgvStock.Rows(e.RowIndex).Cells("Stock Item").Value = intValue
            Else
                Me.dgvStock.Rows(e.RowIndex).Cells("Stock Item") = GetStockComboDataSource(eStockComboType.StockIn)

            End If
        Catch ex As Exception
            JCExceptionLogger.LogException(ex)
        End Try

End Sub

Here I switch the combo DataSource depending on whether the ID column is numeric but you can implement whatever business logic you require. Note that I save the value of the cell before setting the combo (intValue). This appears to be necessary otherwise the combo will display as blank.