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 :)

Answer 1

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')

Answer 2

Try to change dbCount1 to nullable integer

Dim dbCount1 As Nullable(Of Integer)

Or

Dim dbCount1 As Integer?