How to handle Conversion from type 'DBNull' to type 'Integer' is not valid

Question

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.

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."

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:

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.

If anyone could give me some insight on how this can be done I would greatly appreciate it.

Thanks in advance :)

jean · Accepted Answer · 2013-12-16 10:21:38Z

4

A pure SQL solution can rely in coalesce or isnull for this simple query just returning a single aggregate column.

SELECT COALESCE(SUM ([TotalTime]), 0) FROM [Line1Log] WHERE ([State] = 'Test')

answered Dec 16, 2013 at 10:21

jean

4,3574 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

Hi there, thank you for this response, this is my chosen answer as it works perfectly, exactly what I wanted. Thanks again.

har07 · Accepted Answer · 2013-12-16 10:11:21Z

3

Try to change dbCount1 to nullable integer

Dim dbCount1 As Nullable(Of Integer)

Or

Dim dbCount1 As Integer?

answered Dec 16, 2013 at 10:11

har07

89.5k12 gold badges87 silver badges143 bronze badges

1 Comment

James Over a year ago

Thank you for your response, it helped me out a great deal

Panagiotis Kanavos · Accepted Answer · 2013-12-16 10:04:00Z

1

Define dbCount1 as a Nullable integer and call dbCount.HasValue to check whether the value is a Null or a valid integer:

....
Dim dbCount1 as Integer? = dbCommandLog.ExecuteScalar()
if (dbCount1.HasValue) Then
    lblTest.Text = dbCount1
...

An even better option is to use the If operator, to return a default value if the first argument is null:

Dim dbCount1 as Integer? = dbCommandLog.ExecuteScalar()
lblTest.Text=If(dbCount1,0)

answered Dec 16, 2013 at 10:04

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

7 Comments

James Over a year ago

Thank you so much for your reply. I have tried the two examples that you have given me and the method makes a lot of sense thank you. However when using the examples above I get a "Specified cast is not valid." error.

JoshYates1980 Over a year ago

With Integer, Nothing will set the value as = 0. For example: Dim dbCount1 = If(Not IsDBNull(row.Item(3)), CInt(row.Item(3)), Nothing)

Panagiotis Kanavos Over a year ago

@JoshYates1980 no, the implicit conversion from Null (that's what Nothing is) to int will - mixing up actual 0s with nulls. Such implicit conversions are undesirable as they lead to undetectable bugs

JoshYates1980 Over a year ago

My comment was related to the question, but untested. My code that was tested: Public Class Person with Public Property Age As Integer? This was set to 0: Person.Age = If(Not IsDBNull(row.Item(3)), CInt(row.Item(3)), Nothing)

Panagiotis Kanavos Over a year ago

@JoshYates1980 that's worse. Integer? means the property should accept Nulls. Your code replaces them with 0 instead. That's a bug.

|

RobertKing · Accepted Answer · 2013-12-16 10:15:51Z

1

try below if condition instead of yours,

If(dbCommandLog.ExecuteScalar() = DBNull.Value, False, True)

answered Dec 16, 2013 at 10:15

RobertKing

1,9219 gold badges32 silver badges54 bronze badges

Comments

senthilkumar2185 · Accepted Answer · 2013-12-16 10:03:06Z

0

dbCount1 =CInt(dbCommandLog.ExecuteScalar())

Try this Code

answered Dec 16, 2013 at 10:03

senthilkumar2185

2,5983 gold badges23 silver badges38 bronze badges

1 Comment

James Over a year ago

Hi there, thank you for your response however the above code gives me the same error.

Collectives™ on Stack Overflow

How to handle Conversion from type 'DBNull' to type 'Integer' is not valid

5 Answers 5

1 Comment

1 Comment

7 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

7 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related