vb.net 将数据从 datagridview 插入 sql 数据库时，从类型“DBNull”到类型“字符串”的转换无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21668039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conversion from type 'DBNull' to type 'String' is not valid when inserting data from datagridview to sql database
提问by Newbie matt
Hello people i'm a newbie programmer of vb.net, so i have this problem so far, i am searching for a related answer but found no luck, so i decided to post my problem.
大家好，我是 vb.net 的新手程序员，所以到目前为止我遇到了这个问题，我正在寻找相关的答案但没有找到运气，所以我决定发布我的问题。
so here it is.
所以就在这里。
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim connstr As String = "server=midtelephone\sqlexpress; 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
i'd like to update my sql database through datagridview.
我想通过 datagridview 更新我的 sql 数据库。
( that datagridview was called from another sql query - cmd.CommandText = " select period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + claimno.ToString + "'"select period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + claimno.ToString + "'")
（那个datagridview是从另一个sql查询调用的 - cmd.CommandText = " select period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + claimno.ToString + "'"select period, VOUCHIV_AMT, INDIVIDUAL_AMT, , check_no, D_MAILED, DIR_NO from tobee.EBD_BILLHISTORY 其中 CLAIM_NO like '" + claimno.ToString + "'")
now the problem is/are, the error/s come up when i click the save button. whenever i click the save button leaving the other columns empty. errors shows up "invalidcastexception was unhandled" (on a certain rows that is empty) -Conversion from type 'DBNull' to type 'String' is not valid.
现在问题是，当我单击保存按钮时出现错误。每当我单击保存按钮时，其他列都为空。错误显示“invalidcastexception was unhandled”（在某些为空的行上） - 从类型 'DBNull' 到类型 'String' 的转换无效。
aaaaaand.. another error comes up when inserting different value in each rows. specially when i want to insert a datetime value. not really familiar with the code.
aaaaaand.. 在每行中插入不同的值时出现另一个错误。特别是当我想插入日期时间值时。不是很熟悉代码。
is there something wrong with my code? all processes are good(like pulling up the data) except clicking the save button. or updating my database. thanks in advance. your replies are much appreciated.
我的代码有问题吗？除了单击保存按钮外，所有过程都很好（如提取数据）。或更新我的数据库。提前致谢。非常感谢您的回复。
采纳答案by Jade
Try the code below, I also updated the code to avoid SQL injections. You must check if the value from cell is DBNull and handle it appropriately
试试下面的代码，我还更新了代码以避免 SQL 注入。您必须检查单元格中的值是否为 DBNull 并进行适当处理
Dim connstr As String = "server=midtelephone\sqlexpress; 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()
回答by GiorgiTBS
It's better to pass values to command as parameters. Check small portion of code below:
最好将值作为参数传递给命令。检查下面的一小部分代码：
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))
