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

jmcilhinney · Answer 1 · 2018-09-14T05:57:35.147

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

edited Sep 14 '18 at 05:57

answered Sep 14 '18 at 05:50

jmcilhinney

50,448
5
26
46

thanx , jmcilhinney – Ejaz Sarwar Sep 14 '18 at 05:54

score 1 · Accepted Answer · answered Sep 15 '18 at 10:59

        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

how to find out ExecuteScalar() is dbnull

2 Answers2