Geek Answers Handbook

How to handle SqlDataReader null values ​​in VB.net

I have the following code that executes a query and returns a result. However, I looked around and found some examples to take care of the null values, but I get the error message: "Failed to read when there is no data." I also got the error: "Conversion from type" DBNull "to type" Decimal "is invalid."

Can someone help me with this code to prevent my program from resetting to zero?

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
+4
source share
2 answers

You opened the reader, but did not ask him to read something.

After this line:

Dim reader As SqlDataReader = command.ExecuteReader()


If reader.Read() Then

if, ..

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

, , SQL . , Read , , ..

Do While reader.Read()

Loop
+8

, , . .NET .

"NULL", , DataReader, , . , , ReadNullAsString(), . , String.Empty, DbNull.

1, App_Code, -, , . : (aka index) ColumnName.

Public Module DataReaderExtensions

    ''' <summary>
    ''' Reads fieldName from Data Reader. If fieldName is DbNull, returns String.Empty.
    ''' </summary>
    ''' <returns>Safely returns a string. No need to check for DbNull.</returns>
    <System.Runtime.CompilerServices.Extension()> _
    Public Function ReadNullAsEmptyString(ByVal reader As IDataReader, ByVal fieldName As String) As String
        If IsDBNull(reader(fieldName)) Then
            Return String.Empty
        Else
            Return reader(fieldName)
        End If
        Return False
    End Function

    ''' <summary>
    ''' Reads fieldOrdinal from Data Reader. If fieldOrdinal is DbNull, returns String.Empty.
    ''' </summary>
    ''' <returns>Safely returns a string. No need to check for DbNull.</returns>
    <System.Runtime.CompilerServices.Extension()> _
    Public Function ReadString(ByVal reader As IDataReader, ByVal fieldOrdinal As Integer) As String
        If IsDBNull(reader(fieldOrdinal)) Then
            Return ""
        Else
            Return reader(fieldOrdinal)
        End If
        Return False
    End Function

End Module

2, :

' no need to check for DbNull now, this functionality is encapsulated in the extension module.
RESULT1 = reader.ReadNullAsEmptyString(index)
'or
RESULT1 = reader.ReadNullAsEmptyString("RESULT1")
+9

More articles:


All Articles