I can't seem to figure out how to resolve "Conversion from type 'DBNull' to type 'Integer' is not valid." error

Question

Greetings again.

Please forgive me for being a bother again. As embarrassing as it sounds, I have been working on this pretty all day and can't seem to figure it out.

When I try to run the following code:

Dim undetected As Integer = 0
            Dim myConnection As New SqlConnection(conString)

            myConnection.Open()
                Dim Str As String = "SELECT TOP 1 Batch_No FROM  Batch WHERE Enabled = 1 AND Entered = 1 AND Detected <> 1 AND Paid <> 1 order by Batch_No desc"
                Dim cmd As SqlCommand = New SqlCommand(Str, myConnection)
            undetected = ConvertToInt32(cmd.ExecuteScalar())
            Try
                If undetected = 0 Then
                    'disable the release and reopen link buttons and set the text to none
                    NotMissing.Enabled = CSharpImpl.__Assign(reopenIt.Enabled, False)
                    undetected.Text = "None"
                Else
                    ' otherwise enable the link buttons and display the batch number
                    NotMissing.Enabled = CSharpImpl.__Assign(reopenIt.Enabled, True)
                    undetected.Text = undetected.ToString()
                    myConnection.Dispose()
                End If
                myConnection.Close()

                'dr1.Close()
            Catch
            End Try
            
            
                    Private Class CSharpImpl
	                <Obsolete("Please refactor calling code to use normal Visual Basic assignment")>
	                Shared Function __Assign(Of T)(ByRef target As T, value As T) As T
	                    target = value
	                    Return value
	                End Function
            End Class

I get Conversion from type 'DBNull' to type 'Integer' is not valid.

The error is on this line:

undetected = ConvertToInt32(cmd.ExecuteScalar())

I am probably ignoring something simple.

The embedded query works fine when I run it in SSMS.

Any ideas what it could be?

As always, thank you in advance for your help.

Answer 1

It's stated in the documentation that ExecuteScalar returns the first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty.

So here you need to compare the result with Nothing (and also DBNull, just to be on the safe side), for example:

Using connection As New SqlConnection(My.Settings.MyConnectionString)
    Using command As New SqlCommand("SELECT Id From Table1 WHERE Id = -1", connection)
        connection.Open()
        Dim m_result As Object = command.ExecuteScalar()
        If (m_result Is Nothing Or m_result Is DBNull.Value) Then
            MessageBox.Show("None") 'Return value was null
        Else
            MessageBox.Show(Convert.ToInt32(m_result)) 'There was a return value
        End If
    End Using
End Using