Joseph.Scott.Garza Joseph.Scott.Garza - 19 days ago 5
Vb.net Question

How to deal with SqlDataReader null values in VB.net

I have the follwoing code that performs a query and returns a result. However, I looked around and found some examples to take care of null values but I get an error: "Invalid attempt to read when no data is present." I also got the error: "Conversion from type 'DBNull' to type 'Decimal' is not valid."

Can someone help me out with this code to prevent null values from crashing my program?

Private Sub EFFICIENCY_STACKRANK_YTD(ByVal EMPLOYEE As String)

Dim queryString As String = "SELECT " & _
" (SELECT CAST(SUM(TARGET_SECONDS) AS DECIMAL)/ CAST(SUM(ROUTE_SECONDS) AS DECIMAL) FROM dbo.APE_BUSDRIVER_MAIN WITH(NOLOCK) WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND EMPLOYEE_NAME = '" & EMPLOYEE & "' AND YEAR_TIME = '" & cbYear.Text & "' AND ACTIVE = 1) AS RESULT1" & _
" FROM dbo.APE_BUSDRIVER_MAIN "


Using connection As New SqlConnection(SQLConnectionStr)
Dim command As New SqlCommand(queryString, connection)
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()

If reader.Read Then
RESULT1 = reader("RESULT1")
Else
RESULT1 = 0
End If

End Using
End Sub

Answer

You have opened the reader, but have not asked it to actually read anything.

After this line:

Dim reader As SqlDataReader = command.ExecuteReader()

add

If reader.Read() Then

and wrap the result reading into this if statement, i.e.

If reader.Read() Then
    Dim index As Integer = reader.GetOrdinal("RESULT1")
    If reader.IsDBNull(index) Then
        RESULT1 = String.Empty
    Else
        RESULT1 = reader(index)
    End If
End If

Note that this works because your SQL should only return a single record. In the event that you were reading multiple records, you would need to call the Read statement in a loop until there were no more records, i.e.

Do While reader.Read()

Loop
Comments