CODEWITHSUNDEEP
sqlvb.net
James
James

Reputation: 567

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

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

Upvotes: 3

Views: 23763

Answers (5)

jean
jean

Reputation: 4350

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

Upvotes: 4

RobertKing
RobertKing

Reputation: 1921

try below if condition instead of yours,

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

Upvotes: 1

har07
har07

Reputation: 89325

Try to change dbCount1 to nullable integer

Dim dbCount1 As Nullable(Of Integer)

Or

Dim dbCount1 As Integer?

Upvotes: 3

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131722

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)

Upvotes: 1

senthilkumar2185
senthilkumar2185

Reputation: 2578

dbCount1 =CInt(dbCommandLog.ExecuteScalar())

Try this Code

Upvotes: 0

Related Questions