将数据从 datagridview 插入到 sql 数据库时，从类型 'DBNull' 到类型 'String' 的转换无效

Question

大家好，我是vb.net的新手程序员，所以到目前为止我遇到了这个问题，我正在寻找相关的答案，但没有找到运气，所以我决定发布我的问题。

就是这样。

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim connstr As String = "server=midtelephonesqlexpress; database=testdb; user= sa; password=sa;"
cmdconn = New SqlConnection
cmd = New SqlCommand
cmdconn.ConnectionString = sqlstr
cmd.Connection = cmdconn
cmdconn.Open()
Dim period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO As String
For i As Integer = 0 To Me.DataGridView1.Rows.Count - 1
'cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no) values" &_
'" (@period,@VOUCH_AMT,@INDIVIDUAL_AMT,@check_no,@D_MAILED,@DIR_NO)"
period = Me.DataGridView1.Rows(i).Cells(0).Value()
VOUCH_AMT = Me.DataGridView1.Rows(i).Cells(1).Value()
INDIVIDUAL_AMT = Me.DataGridView1.Rows(i).Cells(2).Value()
check_no = Me.DataGridView1.Rows(i).Cells(3).Value()
D_MAILED = Me.DataGridView1.Rows(i).Cells(4).Value()
DIR_NO = Me.DataGridView1.Rows(i).Cells(5).Value()
cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no)values" & _
"('" & period & "','" & VOUCH_AMT & "','" & INDIVIDUAL_AMT & "','" & check_no & "','" & D_MAILED & "', '" & DIR_NO & "')"
cmd.ExecuteNonQuery()
MsgBox("Saved")
Next
cmdconn.Close()
End Sub

我想通过datagridview更新我的sql数据库。

(该数据网格视图是从另一个sql查询中调用的-cmd.CommandText="select period，VOUCH_AMT，INDIVIVUAL_AMT，check_no，D_MAILED，DIR_no from tobe.EBD_BILHISTORY where CLAIM_no like'"+claimno.ToString+"'"select periode，VOUCH-AMT，INDIVIDUAL_MT，check_nno，D_MAILED.DIR_no from Tobe.EBD.BILHISTOry where CLAIM_no like"+claimno ToString+"")

现在的问题是，当我点击保存按钮时会出现错误。每当我单击保存按钮，使其他列为空时。出现错误"invalidcastexception未处理"(在某些空行上)-从类型"DBNull"到类型"String"的转换无效。

aaaaaa。。在每行中插入不同的值时会出现另一个错误。特别是当我想插入日期时间值时。不是很熟悉代码。

我的代码有问题吗？除了点击保存按钮之外，所有的过程都很好(比如提取数据)。或者更新我的数据库。提前谢谢。非常感谢您的回复。

Answer 1

尝试下面的代码，我还更新了代码以避免SQL注入。您必须检查单元格中的值是否为DBNull，并对其进行适当的处理

Dim connstr As String = "server=midtelephonesqlexpress; database=testdb; user= sa; password=sa;"
cmdconn = New SqlConnection
cmd = New SqlCommand
cmdconn.ConnectionString = connstr 'sqlstr
cmd.Connection = cmdconn
cmdconn.Open()
Dim period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO As String
For i As Integer = 0 To Me.DataGridView1.Rows.Count - 1
'cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no) values" &_
'" (@period,@VOUCH_AMT,@INDIVIDUAL_AMT,@check_no,@D_MAILED,@DIR_NO)"
With Me.DataGridView1.Rows(i)
If IsDBNull(.Cells(0).Value()) OrElse .Cells(0).Value() Is Nothing OrElse .Cells(0).Value().ToString().Trim() = "" Then
period = ""
Else
period = .Cells(0).Value()
End If
If IsDBNull(.Cells(1).Value()) OrElse .Cells(1).Value() Is Nothing OrElse .Cells(1).Value().ToString().Trim() = "" Then
VOUCH_AMT = "0"
Else
VOUCH_AMT = .Cells(1).Value()
End If
If IsDBNull(.Cells(2).Value()) OrElse .Cells(2).Value() Is Nothing OrElse .Cells(2).Value().ToString().Trim() = "" Then
INDIVIDUAL_AMT = "0"
Else
INDIVIDUAL_AMT = .Cells(2).Value()
End If
If IsDBNull(.Cells(3).Value()) OrElse .Cells(3).Value() Is Nothing OrElse .Cells(3).Value().ToString().Trim() = "" Then
check_no = ""
Else
check_no = .Cells(3).Value()
End If
If IsDBNull(.Cells(4).Value()) OrElse .Cells(4).Value() Is Nothing OrElse .Cells(4).Value().ToString().Trim() = "" Then
D_MAILED = ""
Else
D_MAILED = .Cells(4).Value()
End If
If IsDBNull(.Cells(5).Value()) OrElse .Cells(5).Value() Is Nothing OrElse .Cells(5).Value().ToString().Trim() = "" Then
DIR_NO = ""
Else
DIR_NO = .Cells(5).Value()
End If
'period = IIf(IsDBNull(.Cells(0).Value()), "", .Cells(0).Value())
'VOUCH_AMT = IIf(IsDBNull(.Cells(1).Value()), "0", IIf(.Cells(1).Value().ToString().Trim() = "", "0", .Cells(1).Value()))
'INDIVIDUAL_AMT = IIf(IsDBNull(.Cells(2).Value()), "0", IIf(.Cells(2).Value().ToString().Trim() = "", "0", .Cells(2).Value()))
'check_no = IIf(IsDBNull(.Cells(3).Value()), "", .Cells(3).Value())
'D_MAILED = IIf(IsDBNull(.Cells(4).Value()), "", .Cells(4).Value())
'DIR_NO = IIf(IsDBNull(.Cells(5).Value()), "", .Cells(5).Value())
End With
cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no)values" & _
"('" & period.Replace("'", "''") & "'," & VOUCH_AMT & "," & INDIVIDUAL_AMT & ",'" & check_no.Replace("'", "''") & "','" & D_MAILED.Replace("'", "''") & "', '" & DIR_NO.Replace("'", "''") & "')"
cmd.ExecuteNonQuery()
MsgBox("Saved")
Next
cmdconn.Close()

Answer 2

最好将值作为参数传递给命令。检查以下代码的一小部分：

cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no) values" &_
" (@period,@VOUCH_AMT,@INDIVIDUAL_AMT,@check_no,@D_MAILED,@DIR_NO)"
cmd.parameters.addwithvalue("@Period", iif(period is dbnull.value, 0, period))