.NET中双击DataGridView含NULL数据行时文本框赋值失败的解决

我用.NET制作了一个关联SQL的数据录入表单，包含“保存”和“更新”按钮。双击DataGridView中的数据行时，文本框会自动填充选中的数据以进行更新操作，但双击包含NULL数据的行时，会触发错误：Conversion from type DBNull to type String is not valid，无法将NULL数据填充到文本框中。我尝试修改双击事件代码但未解决问题，以下是相关代码：

更新按钮原代码 #

Private Sub update_Click(sender As Object, e As EventArgs) Handles update.Click
    Dim sql As String = "Update TBL_XXX set A = '" & ACB.Text & "',B = '" & BCB.Text & "',C = '" & C.Text & "',D = '" & D.Text & "' Where Query_No = '" & DataGridView1.CurrentRow.Cells(0).Value & "' "

    Try
        Dim command As New SqlCommand()

        command.Parameters.Add("A", SqlDbType.Decimal).Value = Decimal.Parse(A.Text)
        command.Parameters.Add("B", SqlDbType.Int).Value = Integer.Parse(B.Text)
        command.Parameters.Add("C", SqlDbType.Decimal).Value = Decimal.Parse(C.Text)
        command.Parameters.Add("D", SqlDbType.Decimal).Value = Decimal.Parse(D.Text)
  
        c.CUD(command, sql)
        list()
        clear()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

双击事件原代码 #

Private Sub DataGridView1_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
    A.Text = DataGridView1.CurrentRow.Cells(1).Value
    B.Text = DataGridView1.CurrentRow.Cells(2).Value
    C.Text = DataGridView1.CurrentRow.Cells(3).Value
    D.Text = DataGridView1.CurrentRow.Cells(4).Value
End Sub

保存NULL数据的代码 #

command.Parameters.Add("A", SqlDbType.Decimal).Value = If(A.TextLength = 0, CObj(DBNull.Value), Decimal.Parse(A.Text))

尝试过的无效修改代码 #

Private Sub DataGridView1_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
    A.Text = If(A.TextLength = 0, CObj(DBNull.Value), DataGridView1.CurrentRow.Cells(0).Value)
End Sub

解决办法 #

1. 修复双击事件的NULL转换问题

文本框的Text属性仅接受字符串，无法直接赋值DBNull.Value。需要先判断单元格值是否为DBNull，若是则赋值为空字符串，否则转换为字符串后赋值：

Private Sub DataGridView1_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
    A.Text = If(DataGridView1.CurrentRow.Cells(1).Value Is DBNull.Value, "", DataGridView1.CurrentRow.Cells(1).Value.ToString())
    B.Text = If(DataGridView1.CurrentRow.Cells(2).Value Is DBNull.Value, "", DataGridView1.CurrentRow.Cells(2).Value.ToString())
    C.Text = If(DataGridView1.CurrentRow.Cells(3).Value Is DBNull.Value, "", DataGridView1.CurrentRow.Cells(3).Value.ToString())
    D.Text = If(DataGridView1.CurrentRow.Cells(4).Value Is DBNull.Value, "", DataGridView1.CurrentRow.Cells(4).Value.ToString())
End Sub

2. 修复更新按钮的SQL注入风险

原更新代码虽添加了参数，但SQL语句仍用字符串拼接，未真正利用参数化查询，存在注入风险。修正为参数化SQL：

Private Sub update_Click(sender As Object, e As EventArgs) Handles update.Click
    Dim sql As String = "Update TBL_XXX set A = @A, B = @B, C = @C, D = @D Where Query_No = @QueryNo"

    Try
        Dim command As New SqlCommand(sql)

        command.Parameters.Add("@A", SqlDbType.Decimal).Value = If(String.IsNullOrEmpty(A.Text), DBNull.Value, Decimal.Parse(A.Text))
        command.Parameters.Add("@B", SqlDbType.Int).Value = If(String.IsNullOrEmpty(B.Text), DBNull.Value, Integer.Parse(B.Text))
        command.Parameters.Add("@C", SqlDbType.Decimal).Value = If(String.IsNullOrEmpty(C.Text), DBNull.Value, Decimal.Parse(C.Text))
        command.Parameters.Add("@D", SqlDbType.Decimal).Value = If(String.IsNullOrEmpty(D.Text), DBNull.Value, Decimal.Parse(D.Text))
        command.Parameters.Add("@QueryNo", SqlDbType.VarChar).Value = DataGridView1.CurrentRow.Cells(0).Value
  
        c.CUD(command, sql)
        list()
        clear()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

关键说明 #

• 数据库的NULL需转换为空字符串才能在文本框中显示，不能直接赋值DBNull.Value给文本框的Text属性。
• 参数化查询必须将SQL语句与参数绑定，避免字符串拼接带来的SQL注入风险。

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