DataGridView CellFormatting event firing repeatedly while trying to link ComboBox column-CodePudding

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.

CodePudding user response：

you dont need cellformatting event you can do your code on the mouse click or mouse double click event on your datagridview all you have to do is to remove the event from the cellformatting event against your datagrid and create an event on mouse click or mouse double click and try to write your code from there

try that and let me what happened