VB.NET DataGridView中DBNull转String无效及CellEndEdit事件报错求助

在VB.NET的DataGridView中使用CellEndEdit事件时，遇到报错：将类型'DBNull'转换为类型'String'无效。需求是用户清空列内容时弹出提示并恢复原值，首次清空Price列正常，第二次操作就触发错误。

原代码 #

Public Class Form2
    Public _myTable As New DataTable()
    Public _dr As DataRow
    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)
        _myTable.Columns.AddRange({
                New DataColumn("Code", GetType(String)),
                New DataColumn("Quantity", GetType(Integer)),
                New DataColumn("Price", GetType(Integer)),
                New DataColumn("Discount", GetType(Integer)),
                New DataColumn("Total", GetType(Integer))})
        Me.CenterToScreen()
    End Sub
    Private Sub CalculateTotalqty()
        Dim tot As Double = 0
        For Each item As DataGridViewRow In DataGridView1.Rows
            Dim quantity As Double
            If Not IsDBNull(item.Cells("Quantity").Value) Then
                Integer.TryParse(CType(item.Cells("Quantity").Value, String), CInt(quantity))
            End If
            tot += quantity
        Next item
        lblQty.Text = tot.ToString("N2")
    End Sub
    Private Sub CalculateTotalValue()
        Dim tot As Double = 0
        For Each item As DataGridViewRow In DataGridView1.Rows
            Dim quantity As Double = 0
            Dim price As Double = 0
            Dim discount As Double = 0
            If Not IsDBNull(item.Cells("Quantity").Value) Then
                Integer.TryParse(CType(item.Cells("Quantity").Value, String), CInt(quantity))
            End If
            If Not IsDBNull(item.Cells("Price").Value) Then
                Integer.TryParse(CType(item.Cells("Price").Value, String), CInt(price))
            End If
            If Not IsDBNull(item.Cells("Discount").Value) Then
                Integer.TryParse(CType(item.Cells("Discount").Value, String), CInt(discount))
            End If
            tot += (quantity * price) - (discount)
        Next item
        lblValue.Text = tot.ToString("N")
    End Sub
    Private Sub BtnAddproduct_Click(sender As Object, e As EventArgs) Handles BtnAddproduct.Click
        _myTable.Rows.Add("A", 2, 10000, 0, 20000)
        'DataGridView1.AutoGenerateColumns = False
        ' Disable adding new row
        DataGridView1.AllowUserToAddRows = False
        ' Create our medium between grid and collection
        DataGridView1.DataSource = _myTable
        CalculateTotalqty()
        CalculateTotalValue()
    End Sub
    Private Sub grid_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Dim quantity As Double = 0
        Dim price As Double = 0
        Dim discount As Double = 0
        Dim total As Double = 0

        If Not IsDBNull(DataGridView1.Rows(e.RowIndex).Cells("Quantity").Value) Then
            Integer.TryParse(CType(DataGridView1.Rows(e.RowIndex).Cells("Quantity").Value, String), CInt(quantity))
            _dr("Quantity") = quantity
        Else
            MessageBox.Show("Quantity value is required.")
            DataGridView1.Rows(e.RowIndex).Cells("Quantity").Value = _dr("Quantity").ToString()
            quantity = Double.Parse(CType(DataGridView1.Rows(e.RowIndex).Cells("Quantity").Value, String))
        End If
        If Not IsDBNull(DataGridView1.Rows(e.RowIndex).Cells("Price").Value) Then
            Integer.TryParse(CType(DataGridView1.Rows(e.RowIndex).Cells("Price").Value, String), CInt(price))
            _dr("Price") = price
        Else
            MessageBox.Show("Price value is required.")
            DataGridView1.Rows(e.RowIndex).Cells("Price").Value = _dr("Price").ToString()
            price = Double.Parse(CType(DataGridView1.Rows(e.RowIndex).Cells("Price").Value, String))
        End If
        If Not IsDBNull(DataGridView1.Rows(e.RowIndex).Cells("Discount").Value) Then
            Integer.TryParse(CType(DataGridView1.Rows(e.RowIndex).Cells("Discount").Value, String), CInt(discount))
            _dr("Discount") = discount
        Else
            MessageBox.Show("Discount value is required.")
            DataGridView1.Rows(e.RowIndex).Cells("Discount").Value = _dr("Discount").ToString()
            discount = Double.Parse(CType(DataGridView1.Rows(e.RowIndex).Cells("Discount").Value, String))
        End If
        total = (quantity * price) - (discount)
        DataGridView1.Rows(e.RowIndex).Cells("Total").Value = total
        CalculateTotalqty()
        CalculateTotalValue()
    End Sub
    Private Sub DataGridView1_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
        If e.Context = DataGridViewDataErrorContexts.Commit Then
            'MessageBox.Show(String.Format("Column {0} value is required.", e.ColumnIndex))
        End If
    End Sub
    Private Sub DataGridView1_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit
        _dr = _myTable.Rows(e.RowIndex)
    End Sub
End Class

问题分析 #

1. 全局DataRow引用的隐患：_dr是全局变量，和DataTable中的行是同一个引用。第一次编辑后修改_dr("Price")会直接改变DataTable的数据，第二次清空时，_dr中的值已经不是原始值，甚至可能变成DBNull，此时调用ToString()会触发类型转换错误。
2. 未判断DBNull直接转换：代码中多次使用CType(..., String)直接转换单元格值，未提前判断是否为DBNull，导致转换失败。
3. 恢复值逻辑错误：依赖_dr的当前值恢复，但_dr已经被之前的修改污染，无法提供正确的原始值。

解决方案 #

修正后的代码 #

Public Class Form2
    Public _myTable As New DataTable()
    ' 保存编辑前的原始值，键为行索引+列名，值为原始值
    Private _originalValues As New Dictionary(Of String, Object)

    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)
        _myTable.Columns.AddRange({
                New DataColumn("Code", GetType(String)),
                New DataColumn("Quantity", GetType(Integer)),
                New DataColumn("Price", GetType(Integer)),
                New DataColumn("Discount", GetType(Integer)),
                New DataColumn("Total", GetType(Integer))})
        Me.CenterToScreen()
    End Sub

    Private Sub CalculateTotalqty()
        Dim tot As Double = 0
        For Each item As DataGridViewRow In DataGridView1.Rows
            If Not item.IsNewRow Then
                Dim quantity As Integer = 0
                If Not IsDBNull(item.Cells("Quantity").Value) Then
                    Integer.TryParse(item.Cells("Quantity").Value.ToString(), quantity)
                End If
                tot += quantity
            End If
        Next item
        lblQty.Text = tot.ToString("N2")
    End Sub

    Private Sub CalculateTotalValue()
        Dim tot As Double = 0
        For Each item As DataGridViewRow In DataGridView1.Rows
            If Not item.IsNewRow Then
                Dim quantity As Integer = 0
                Dim price As Integer = 0
                Dim discount As Integer = 0

                If Not IsDBNull(item.Cells("Quantity").Value) Then
                    Integer.TryParse(item.Cells("Quantity").Value.ToString(), quantity)
                End If
                If Not IsDBNull(item.Cells("Price").Value) Then
                    Integer.TryParse(item.Cells("Price").Value.ToString(), price)
                End If
                If Not IsDBNull(item.Cells("Discount").Value) Then
                    Integer.TryParse(item.Cells("Discount").Value.ToString(), discount)
                End If

                tot += (quantity * price) - discount
            End If
        Next item
        lblValue.Text = tot.ToString("N")
    End Sub

    Private Sub BtnAddproduct_Click(sender As Object, e As EventArgs) Handles BtnAddproduct.Click
        _myTable.Rows.Add("A", 2, 10000, 0, 20000)
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.DataSource = _myTable
        CalculateTotalqty()
        CalculateTotalValue()
    End Sub

    Private Sub grid_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        If e.RowIndex < 0 OrElse e.ColumnIndex < 0 Then Return

        Dim row = DataGridView1.Rows(e.RowIndex)
        Dim colName = DataGridView1.Columns(e.ColumnIndex).Name
        Dim quantity As Integer = 0
        Dim price As Integer = 0
        Dim discount As Integer = 0
        Dim total As Integer = 0

        ' 处理必填列的空值情况
        If {"Quantity", "Price", "Discount"}.Contains(colName) Then
            If IsDBNull(row.Cells(colName).Value) OrElse String.IsNullOrWhiteSpace(row.Cells(colName).Value?.ToString()) Then
                MessageBox.Show($"{colName} value is required.")
                ' 恢复编辑前的原始值
                Dim key = $"{e.RowIndex}_{colName}"
                If _originalValues.ContainsKey(key) Then
                    row.Cells(colName).Value = _originalValues(key)
                End If
            End If
        End If

        ' 重新获取有效值
        If Not IsDBNull(row.Cells("Quantity").Value) Then
            Integer.TryParse(row.Cells("Quantity").Value.ToString(), quantity)
        End If
        If Not IsDBNull(row.Cells("Price").Value) Then
            Integer.TryParse(row.Cells("Price").Value.ToString(), price)
        End If
        If Not IsDBNull(row.Cells("Discount").Value) Then
            Integer.TryParse(row.Cells("Discount").Value.ToString(), discount)
        End If

        ' 计算并更新Total
        total = (quantity * price) - discount
        row.Cells("Total").Value = total

        CalculateTotalqty()
        CalculateTotalValue()
    End Sub

    Private Sub DataGridView1_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
        If e.Context = DataGridViewDataErrorContexts.Commit Then
            MessageBox.Show($"Column {DataGridView1.Columns(e.ColumnIndex).Name} value is invalid.")
            e.Cancel = True
        End If
    End Sub

    Private Sub DataGridView1_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit
        If e.RowIndex < 0 OrElse e.ColumnIndex < 0 Then Return

        Dim colName = DataGridView1.Columns(e.ColumnIndex).Name
        Dim key = $"{e.RowIndex}_{colName}"
        ' 保存编辑前的原始值，避免后续修改影响恢复
        _originalValues(key) = DataGridView1.Rows(e.RowIndex).Cells(colName).Value
    End Sub
End Class

关键修改点 #

1. 新增原始值存储字典：用_originalValues在CellBeginEdit时保存单元格的原始值，确保恢复时使用的是编辑前的正确值，不受DataTable后续修改影响。
2. 空值判断逻辑优化：先判断单元格值是否为DBNull或空字符串，再执行提示和恢复操作，避免直接转换DBNull触发错误。
3. 类型转换简化：直接调用Value.ToString()前先判断是否为DBNull，避免强制转换的风险。
4. 增强错误提示：DataError事件中明确提示错误列名，并取消错误提交，提升用户体验。

内容的提问来源于stack exchange，提问作者dlaksmi