I have a DataGridView populated by a DataTable. The view is in a form which is to be used to allow a user to edit the data, add new rows etc. One of the columns in the set, I want to be selectable from a list via a ComboBox. Because there is pre-existing data loaded at the start (the "current state"), I have to add a separate DataGridViewComboBoxColumn and then "set" the values for the pre-existing data from an identifier in a separate (hidden) column. I set the DisplayType of the combobox column to Nothing so that they appear like flat textboxes.
Private featureIDColumn As DataGridViewColumn
Private cboFeatureColumn As DataGridViewComboBoxColumn
With dgv
.DataSource = dtbMyDataTable
featureIDColumn = .Columns("FeatureID")
featureIDColumn.Visible = False
cboFeatureColumn = New DataGridViewComboBoxColumn() With {.DisplayIndex = featureIDColumn.Index + 1}
With cboFeatureColumn
.Name = "Feature"
.DataSource = dtbFeatureList ' Simple two-column lookup of ID's and values
.ValueMember = "ID"
.DisplayMember = "FriendlyName"
.HeaderText = "Feature"
.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing
End With
.Columns.Add(cboFeatureColumn)
For Each row As DataGridViewRow In dgv.Rows
Dim cboCell As DataGridViewComboBoxCell = CType(row.Cells(cboFeatureColumn.Index), DataGridViewComboBoxCell)
cboCell.Value = row.Cells(featureIDColumn.Index).Value
Next
End With
I've added a (derived) button column to the grid with the intention that when the user clicks it, a new record is inserted and the DisplayType of the DataGridViewComboBoxCell on that specific row changes to ComboBox (so they can select the value from the dropdown) but all the other cells in the column retain the appearance of a flat textbox.
Private Sub copyAction(clickedCell As DataGridViewCustomButtonCell)
Try
Dim activeRow As DataGridViewRow = dgv.Rows(clickedCell.RowIndex)
Dim newRow As DataRow = dtbMyDataTable.NewRow
For Each dtc As DataColumn In dtbMyDataTable.Columns
Select Case dtc.ColumnName
Case "FeatureID"
newRow.Item(dtc.ColumnName) = 0
Case "Status"
newRow.Item(dtc.ColumnName) = "New"
Case Else
' Various default values are set for certain columns
End Select
Next
dtbActive.Rows.Add(newRow)
dtbActive.AcceptChanges()
Catch ex As Exception
End Try
End Sub
Private Sub dgv_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles dgv.CellFormatting
Try
If Not e.RowIndex < 0 Then
Dim activeRow As DataGridViewRow = dgv.Rows(e.RowIndex)
For Each cll As DataGridViewCell In activeRow.Cells
If activeRow.Cells(statusColumn.Index).Value = "New" Then
If cll.ColumnIndex = cboFeatureColumn.Index Then
Dim cbo As DataGridViewComboBoxCell = CType(dgv.Rows(cll.RowIndex).Cells(cll.ColumnIndex), DataGridViewComboBoxCell)
cbo.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
cll.Style.BackColor = Color.White
cll.Style.ForeColor = Color.DarkBlue
cll.ReadOnly = False
Else
cll.Style.BackColor = Color.LightGreen
cll.Style.ForeColor = Color.DarkGreen
cll.ReadOnly = True
End If
End If
Next
End If
Catch ex As Exception
End Try
End Sub
When they select an item from the dropdown, the associated value (identifier) is updated in the ID field (this is the "true" record; the ComboBox is merely there to serve as an easy means of selecting from a specific set of values)
Private Sub dgv_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgv.CellValueChanged
Try
Dim changedCell As DataGridViewCell = CType(dgv.Rows(e.RowIndex).Cells(e.ColumnIndex), DataGridViewCell)
If changedCell.ColumnIndex = cboFeatureColumn.Index Then
Dim changedCombo = CType(changedCell, DataGridViewComboBoxCell)
dgv.Rows(changedCell.RowIndex).Cells(featureIDColumn.Index).Value = changedCombo.Value
End If
Catch ex As Exception
End Try
End Sub
But - as soon as the new record is created, all the values in the combobox column for the pre-existing records, disappear? And that, I am assuming, is because the combobox column only exists in the DataGridView, not in the underlying DataTable (?) So the DataGridView refreshes from its DataSource and blanks that column.
Okay - so to get around that problem, I created a Sub that refreshes that column from the ID column which I can call whenever the data changes :
Private Sub RefreshFeatureColumn()
Try
For Each row As DataGridViewRow In dgv.Rows
Dim cboCell As DataGridViewComboBoxCell = CType(row.Cells(cboFeatureColumn.Index), DataGridViewComboBoxCell)
cboCell.Value = row.Cells(featureIDColumn.Index).Value
Next
Catch ex As Exception
End Try
End Sub
But now, whenever I click the button, it appears to me that the CellFormatting (and/or possibly the CellPainting ?) event is just firing constantly, rendering the form unusable (it's just flickering perpetually and not capturing any clicks - effectively, hanging)
So - I'm obviously doing something badly wrong, but how else can I use a ComboBox column to manipulate the value in the ID column? Is there any way of handling the CellFormatting events such that it only fires when I want/need it to? Or, perhaps more appropriately, is there a way I can achieve what I'm trying to achieve in a way that doesn't fire the CellFormatting event perpetually like this?
Thanks in advance and apologies for the length of the question, I stripped the code down as much as I possibly could but wanted to include as much as possible to fully explain what it is I'm trying to do (and I can't imagine I'm the first?) I'm sure there's a far more elegant way of doing this but I haven't been able to figure it out.
