vb.net 如何处理从“DBNull”类型到“Integer”类型的转换无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20607831/
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
How to handle Conversion from type 'DBNull' to type 'Integer' is not valid
提问by James
I am just wondering if someone could help me with the following. I am using VB. NET and SQL Server and want to include some error handling in my code.
我只是想知道是否有人可以帮助我解决以下问题。我正在使用VB。NET 和 SQL Server，并希望在我的代码中包含一些错误处理。
I am doing a SQL SUM command which returns fine with no problems providing there is a match in the database. But when there is no match to the String I have specified I get a "Conversion from type 'DBNull' to type 'Integer' is not valid."
我正在执行一个 SQL SUM 命令，它返回正常，只要数据库中有匹配项就没有问题。但是，当与我指定的字符串不匹配时，我会收到“从类型 'DBNull' 到类型 'Integer' 的转换无效”。
I understand this error occurs because I have no value in the database to which I have specified.
我了解发生此错误是因为我在指定的数据库中没有任何值。
Below is the code that I am using which contains no error handling:
以下是我正在使用的代码，其中不包含错误处理：
Dim dbCount1 As Integer
SQLConnectLog()
strSQLog = "SELECT SUM ([TotalTime]) FROM [Line1Log] WHERE ([State] = 'Test')"
dbCommandLog = New SqlCommand(strSQLog, dbConnectionLog)
dbCount1 = dbCommandLog.ExecuteScalar()
SQLDisconnectLog()
lblTest.Text = dbCount1
Now I have tried several different ways to use isDBNull but to no avail, such as:
现在我尝试了几种不同的方法来使用 isDBNull 但都无济于事，例如：
Dim dbCount1 As Integer
SQLConnectLog()
strSQLog = "SELECT SUM ([TotalTime]) FROM [Line1Log] WHERE ([State] = 'Test')"
dbCommandLog = New SqlCommand(strSQLog, dbConnectionLog)
If IsDBNull(dbCount1 = dbCommandLog.ExecuteScalar()) Then
SQLDisconnectLog()
lblTest.Text = dbCount1
Else
lblTest.Text = "0"
End If
The method I used above still does not work.
我上面用的方法还是不行。
I am sure I need to use isDBNull function but no sure where to place it.
我确定我需要使用 isDBNull 函数，但不确定将它放在哪里。
If anyone could give me some insight on how this can be done I would greatly appreciate it.
如果有人能给我一些关于如何做到这一点的见解，我将不胜感激。
Thanks in advance :)
提前致谢 ：）
回答by jean
A pure SQL solution can rely in coalesce or isnull for this simple query just returning a single aggregate column.
对于这个简单的查询，纯 SQL 解决方案可以依赖于 coalesce 或 isnull，只返回单个聚合列。
SELECT COALESCE(SUM ([TotalTime]), 0) FROM [Line1Log] WHERE ([State] = 'Test')
回答by har07
Try to change dbCount1 to nullable integer
尝试将 dbCount1 更改为可为空的整数
Dim dbCount1 As Nullable(Of Integer)
Or
或者
Dim dbCount1 As Integer?
回答by Rohaan
try below if condition instead of yours,
如果条件不是你的，请在下面尝试，
If(dbCommandLog.ExecuteScalar() = DBNull.Value, False, True)
If(dbCommandLog.ExecuteScalar() = DBNull.Value, False, True)
回答by sk2185
dbCount1 =CInt(dbCommandLog.ExecuteScalar())
Try this Code
试试这个代码
回答by Panagiotis Kanavos
Define dbCount1 as a Nullableinteger and call dbCount.HasValueto check whether the value is a Null or a valid integer:
将 dbCount1 定义为Nullable整数并调用dbCount.HasValue以检查该值是 Null 还是有效整数：
....
Dim dbCount1 as Integer? = dbCommandLog.ExecuteScalar()
if (dbCount1.HasValue) Then
lblTest.Text = dbCount1
...
An even better option is to use the Ifoperator, to return a default value if the first argument is null:
更好的选择是使用If运算符，如果第一个参数为 null，则返回默认值：
Dim dbCount1 as Integer? = dbCommandLog.ExecuteScalar()
lblTest.Text=If(dbCount1,0)
