vb.net DBnull 到整数转换错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15882133/
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
DBnull to integer conversion error
提问by d99
I get the Conversion from type 'DBNull' to type 'Integer' is not valid." error on the line "Dim avgObject As string = Cstr(avgCom.ExecuteScalar())
我得到 Conversion from type 'DBNull' to type 'Integer' is not valid." error on the line "Dim avgObject As string = Cstr(avgCom.ExecuteScalar())
The command works when the where module_ID='" & moduleSelect & "'statement is removed and I do not know how to fix this, can anyone help?
该命令在where module_ID='" & moduleSelect & "'删除语句时起作用，但我不知道如何解决此问题，有人可以帮忙吗？
Dim moduleSelect As String = moduleRadio.SelectedValue
Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
Using avgCom As New SqlCommand("SELECT AVG(exam) FROM completed_module where module_ID='" & moduleSelect & "' ", _
connection)
connection.Open()
Dim avgObject As Integer = CInt(avgCom.ExecuteScalar())
Dim averageVar As String
averageVar = avgObject.ToString
avgLabel.Text = averageVar
End Using
回答by C.Evenhuis
DBNullmeans that the record in the database does not contain a value for the column. So basically you are trying to convert "nothing" into a number.
DBNull意味着数据库中的记录不包含该列的值。所以基本上你试图将“无”转换为数字。
What do you want your program to do? Skip the row? Use a default value instead?
你想让你的程序做什么？跳过这一行？改用默认值？
If the command really"works" if you remove a statement from the command, I suggest you simply remove it.
如果从命令中删除一条语句，该命令确实“有效”，我建议您简单地将其删除。
回答by eXecute
I believe you are looking for something like this, first checking if it is dbnull:
我相信你正在寻找这样的东西，首先检查它是否是 dbnull：
Dim moduleSelect As String = moduleRadio.SelectedValue
Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
Using avgCom As New SqlCommand("SELECT AVG(exam) FROM completed_module where module_ID='" & moduleSelect & "' ", _
connection)
connection.Open()
Dim result = avgCom.ExecuteScalar()
If IsDBNull(result) then return
Dim avgObject As Integer = CInt(result)
Dim averageVar As String
averageVar = avgObject.ToString
avgLabel.Text = averageVar
End Using
回答by Amegon
Use Convert.ToString instead. Directcast as string does not work for Null/Nothing
请改用 Convert.ToString。Directcast as string 不适用于 Null/Nothing
UPDATEProblem happens whenever you do not receive any results.
UPDATE问题发生在您没有收到任何结果时。
I tested, so CStr to Convert.ToString works for DBNUll, but CInt and Convert.ToIntXX still throws an eception. You can use
我测试过，所以 CStr 到 Convert.ToString 适用于 DBNUll，但 CInt 和 Convert.ToIntXX 仍然抛出一个eception。您可以使用
Dim scalarResult = avgCom.ExecuteScalar()
If Convert.IsDBNull(scalarResult) then
avgObject = 0
Else
avgObject = Convert.toInt32(scalarResult)
End If
回答by Pandian
Error :Conversion from type 'DBNull' to type 'Integer' is not valid.
错误 ：Conversion from type 'DBNull' to type 'Integer' is not valid.
This error Occurs because your query return a NULLvalue.. you can manage the NULLvalue by using the Below code..
发生此错误是因为您的查询返回一个NULL值..您可以NULL使用下面的代码管理该值..
Try like below it will help you...
像下面这样尝试它会帮助你......
connection.Open()
Dim result As String = avgCom.ExecuteScalar().ToString()
Dim avgObject As Integer = If(result = "", 0, CInt(result))
回答by DrCopyPaste
Probably this fails because there is a value missing. (i.e. NULL) But it might work if you default to 0 if a row with NULL was encountered:
这可能会失败，因为缺少一个值。（即 NULL）但如果遇到带有 NULL 的行，则默认为 0 则它可能会起作用：
SELECT AVG(ISNULL(exam,0)) FROM completed_module where module_ID=
Otherwise make sure your table does not include NULL-values for that column:
否则请确保您的表不包含该列的 NULL 值：
UPDATE completed_module SET exam = 0 WHERE exam IS NULL
(maybe constraint it so it may not have future NULL-Values also ;))
（也许限制它，所以它可能没有未来的 NULL 值也;））
EDIT: this assumes that you can actually have an average value for every row, even those where the column you access is NULL, in that case i would assume NULL does not add anything to your average value (which the other rows that share that ID might) so I default it to 0
编辑：这假设您实际上可以为每一行都有一个平均值，即使您访问的列是 NULL，在这种情况下，我会假设 NULL 不会为您的平均值添加任何内容（共享该 ID 的其他行）可能）所以我默认它为 0
