how to find out ExecuteScalar() is dbnull

Question

How do I check to see if the result of ExecuteScalar() has no data? Checking if it is nothing does not work.

My code:

Dim cmdCode As New OracleCommand
cmdCode.Connection = sgcnn
cmdCode.CommandText = "select max(SUBSTR(client_code,4,7)) from CLIENT_INFO where (client_code Like '%' || :SysUser || '%') "
cmdCode.Parameters.Add("@SysUser", OracleDbType.Varchar2).Value = strMessage

'Dim sqlResult As Object = cmdCode.ExecuteScalar()
'If sqlResult Is Nothing Then
'    ' No result found
'    strMessage.Append("0001")
'    txtClientCode.Text = strMessage.ToString
'Else
'    'result found
'    Dim newProdID As Int32 = 0
'    newProdID = Convert.ToInt32(cmdCode.ExecuteScalar())
'    newProdID = newProdID + 1
'    strMessage.Append(newProdID.ToString("0000"))
'    txtClientCode.Text = strMessage.ToString
'End If

Answer 1

        If IsDBNull(cmdCode.ExecuteScalar()) Then
        '    ' No result found
        '    strMessage.Append("0001")
        '    txtClientCode.Text = ""
    Else
        '    'result found
        '    Dim newProdID As Int32 = 0
        '    newProdID = Convert.ToInt32(cmdCode.ExecuteScalar())
        '    newProdID = newProdID + 1
        '    strMessage.Append(newProdID.ToString("0000"))
        '    txtClientCode.Text = strMessage.ToString
    End If

Answer 2

If sqlResult Is DBNull.Value Then

Once you've established that it's not NULL though, don't go and call ExecuteScalar again. You've already got the result so use it. Also, it already is an Integer so there's no need to convert. Just cast:

Dim newProdID = CInt(sqlResult) + 1