executescalar: Object cannot be cast from DBNull to other types

Question

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

This is a debugging issue. The error is quite clear, step through and the line it breaks on is your issue, specifically the `ExecuteScalar` call as its probably not returning anything except `DBNull`. Can be addressed by changing your query... — Trevor, May 17 '18 at 01:21
If there are no matching records then aggregate functions like `MAX` return `NULL`, which means `DBNull.Value` in ADO.NET. Obviously that isn't a integer so it can be converted to an `Integer`. You need to either modify your query so that it always returns an `Integer` or else modify your VB code so that it can handle nulls. — jmcilhinney, May 17 '18 at 01:49
By the way, if the value is already an `Integer` but simply boxed, i.e. provided via an `Object` reference, then `CInt` would be more appropriate than `Convert.ToInt32` as there's no actual converting to be done; just casting. — jmcilhinney, May 17 '18 at 01:50
If dr.HasRows = True Then If dr.IsDBNull(0) = False Then dr.Close() cv = Convert.ToInt32(cmd2.ExecuteScalar()) — DIANA ROSE ARNAIZ, May 17 '18 at 02:01
i have revised it. but in the else part of the code it won't return a value — DIANA ROSE ARNAIZ, May 17 '18 at 02:02
You keep saying that you used `dr.HasRows` but I don't see you using a DataReader in your code. You're using `ExecuteScalar()`. Well, based on your previous comment, it appears that you're using a DataReader before calling `.ExecuteScalar()`. If you're doing that _only_ to check if it has rows, you don't need to do that. Check my answer below. — 41686d6564 stands w. Palestine, May 17 '18 at 02:14

score 0 · Accepted Answer · answered May 17 '18 at 02:04

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

executescalar: Object cannot be cast from DBNull to other types

1 Answers1