How to deal with SqlDataReader null values in VB.net

Question

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()…

Accepted 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
```

How to deal with SqlDataReader null values in VB.net

Solution

Problem