[SOLVED] executescalar: Object cannot be cast from DBNull to other types

executescalar: Object cannot be cast from DBNull to other types

I'm having a problem when there is no data/value is fetch during running the program, I get the following error:

Object cannot be cast from DBNull to other types.

I tried validating it using if dr.hasrow() but I still get errors.

drqry = "SELECT max(num) FROM tbCVinfo WHERE cvno LIKE '%" & cvstr & "%'"

cmd2.CommandText = drqry
cmd2.Connection = con

Dim result As String = ""

cv = Convert.ToInt32(cmd2.ExecuteScalar())
'''''''''in this section I'm getting the error.

cv = cv + 1
If cv >= 0 And cv <= 9 Then
  ...............
Else
    cn = "0001"
End If
cn = result

cvno = cn

Solution

An easy way to get around this issue is to check if the returned value is DBNull:

Dim cv As Integer
Dim queryResult = cmd2.ExecuteScalar()
If IsDBNull(queryResult) Then
    ' No matching records. Do something about it.
Else
    cv = DirectCast(queryResult, Integer)
End If