How to edit a cell in a Datagridview that is bound to a bindingsource

Question

I have a datagridview that is updated when the bindingsource is updated. After the last row that has actual data there should be an empty row. If the user selects a cell in this row they should be able to type data into that cell (as if it were a textbox).

I have set the "AllowUserToAddRows" property of the datagridview to "true"; this adds a blank row after the last row with data. However, when the user clicks the row, the cells are populated with the default value for the field (zero, in my case).

Since the properties that I have bound the datagridview columns to are of type double, it seems like I can not display any non numeric values.

So first, is it possible to either leave the cells blank or display an empty string when they are clicked. Second, how can I allow the user to enter data into certain cells?

Answer 1

Try using the CellFormatting event:

Private Sub dgv_CellFormatting(sender As Object, _
                               e As DataGridViewCellFormattingEventArgs) _
                               Handles dgv.CellFormatting
  If (e.ColumnIndex = 0) Then
    If e.Value IsNot Nothing AndAlso DirectCast(e.Value, Double) = 0 Then
      e.Value = String.Empty
      e.FormattingApplied = True
    End If
  End If
End Sub

The Double type isn't great for comparisons. Consider using a Decimal type instead.

---

Trying to keep the cells "blank" for the new row until they were "touched" by the user requires hacking around with a flag on each cell of the new row, so here is an example using the Tag property:

Dim rowEditing As Integer = -1

Private Sub dgv_RowEnter(sender As Object, _
                         e As DataGridViewCellEventArgs) _
                         Handles dgv.RowEnter
  If e.RowIndex = dgv.NewRowIndex Then
    rowEditing = e.RowIndex
    For i As Integer = 0 To dgv.Columns.Count - 1
      dgv.Rows(rowEditing).Cells(i).Tag = "tagged"
    Next
  End If
End Sub

Private Sub dgv_RowLeave(sender As Object, _
                         e As DataGridViewCellEventArgs) _
                         Handles dgv.RowLeave
  If rowEditing > -1 Then
    For i As Integer = 0 To dgv.Columns.Count - 1
      dgv.Rows(rowEditing).Cells(i).Tag = String.Empty
    Next
    rowEditing = -1
  End If
End Sub

Private Sub dgv_CellEndEdit(sender As Object, _
                            e As DataGridViewCellEventArgs) _
                            Handles dgv.CellEndEdit
  If e.RowIndex = rowEditing AndAlso e.ColumnIndex = 1 Then
    dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Tag = String.Empty
  End If
End Sub

Private Sub dgv_CellFormatting(sender As Object, _
                               e As DataGridViewCellFormattingEventArgs) _
                               Handles dgv.CellFormatting
  If e.RowIndex = rowEditing AndAlso e.ColumnIndex = 1 Then
    If dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Tag IsNot Nothing AndAlso _
       dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Tag = "tagged" Then
      If e.Value IsNot Nothing Then
        e.Value = String.Empty
        e.FormattingApplied = True
      End If
    End If
  End If
End Sub

This example is just testing the second column with is of the type Double.

Answer 2

I made a pretty simple adjustment. Inside the DataGridView_DoubleClick event:

    With Me.DataGridView

        Select Case .Columns(e.ColumnIndex).HeaderText'or preferred variable

            Case "Qty"

                Dim v As Object = InputBox("Enter positive numeric value for " & .Columns(e.ColumnIndex).HeaderText,
         .Columns(e.ColumnIndex).HeaderText & " Value", .Rows(e.RowIndex).Cells(e.ColumnIndex).Value)

                If Not IsNumeric(v) OrElse CInt(v) < 0 Then
                    MsgBox("You must enter a positive numeric value", MsgBoxStyle.OkOnly, "Invalid Entry")
                    Exit Sub
                End If

                Me.DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = v

        End Select

    End With