SqlDataReader HasRows but doesn't Read

Question

I'm using a SqlDataReader to retrieve database information to put into a javascript string. I get SqlDataReader.HasRows=True, and if I call my string building without reading the data reader first, I get the proper string. If I try to loop on the reader though, nothing comes out.

The following code returns a properly formed string, but only for the first record:

Function sFnLoadCodingLines() As String
    Dim cmdCoding As SqlCommand, drCoding As SqlDataReader, con As New SqlConnection(strcon)
    Dim sTemp As String = ""
    Dim lInvoiceID As Long

    If Not IsNumeric(CLng(Page.Request.QueryString("InvoiceID"))) Then
        sFnLoadCodingLines = ""
        Exit Function
    End If


    lInvoiceID = CLng(Page.Request.QueryString("InvoiceID"))

    cmdCoding = New SqlCommand
    cmdCoding.CommandText = "SELECT BU,DEPT,AccNo,Qty,Amount,Description,EMP,VARIETAL,BRAND,STATE,PROJECT " & _
                                "FROM OI_tblCoding WHERE isDeleted=0 AND InvoiceID=" & lInvoiceID
    cmdCoding.Connection = con
    con.Open()
    drCoding = cmdCoding.ExecuteReader
    drCoding.Read()

    If drCoding.HasRows() Then

        sTemp = sTemp & "addRowToTable(""" & drCoding("BU").ToString & _
            """,""" & drCoding("DEPT").ToString & _
            """,""" & drCoding("AccNo").ToString & _
            """,""" & drCoding("Qty").ToString & _
            """,""" & drCoding("Amount").ToString & _
            """,""" & drCoding("Description").ToString & _
            """,""" & drCoding("EMP").ToString & _
            """,""" & drCoding("VARIETAL").ToString & _
            """,""" & drCoding("BRAND").ToString & _
            """,""" & drCoding("STATE").ToString & _
            """,""" & drCoding("PROJECT").ToString & """);" & vbCrLf

        sFnLoadCodingLines = sTemp

    Else
        sFnLoadCodingLines = ""
    End If

    con.Close()

End Function

The following record returns an empty string:

Function sFnLoadCodingLines() As String
    Dim cmdCoding As SqlCommand, drCoding As SqlDataReader, con As New SqlConnection(strcon)
    Dim sTemp As String = ""
    Dim lInvoiceID As Long

    If Not IsNumeric(CLng(Page.Request.QueryString("InvoiceID"))) Then
        sFnLoadCodingLines = ""
        Exit Function
    End If


    lInvoiceID = CLng(Page.Request.QueryString("InvoiceID"))

    cmdCoding = New SqlCommand
    cmdCoding.CommandText = "SELECT BU,DEPT,AccNo,Qty,Amount,Description,EMP,VARIETAL,BRAND,STATE,PROJECT " & _
                                "FROM OI_tblCoding WHERE isDeleted=0 AND InvoiceID=" & lInvoiceID
    cmdCoding.Connection = con
    con.Open()
    drCoding = cmdCoding.ExecuteReader
    drCoding.Read()

    If drCoding.HasRows() Then

        While drCoding.Read()
            'addRowToTable(sBU, sDPT, sAcct, dbQty, dbAmt, sDesc, sEmp, sVar, sBrand, sState, sProj)
            sTemp = sTemp & "addRowToTable(""" & drCoding("BU").ToString & _
                            """,""" & drCoding("DEPT").ToString & _
                            """,""" & drCoding("AccNo").ToString & _
                            """,""" & drCoding("Qty").ToString & _
                            """,""" & drCoding("Amount").ToString & _
                            """,""" & drCoding("Description").ToString & _
                            """,""" & drCoding("EMP").ToString & _
                            """,""" & drCoding("VARIETAL").ToString & _
                            """,""" & drCoding("BRAND").ToString & _
                            """,""" & drCoding("STATE").ToString & _
                            """,""" & drCoding("PROJECT").ToString & """);" & vbCrLf

        End While

        sFnLoadCodingLines = sTemp

    Else
        sFnLoadCodingLines = ""
    End If

    con.Close()

End Function

jmcilhinney · Accepted Answer · 2014-02-12 01:41:32Z

3

But you ARE reading the data in both cases. You call ExecuteReader and then you call Read immediately after that, before testing HasRows. Get rid of that first Read call and your loop will work as you expect. The first code would then fail as it should because you will not be calling Read at all.

answered Feb 12, 2014 at 1:41

jmcilhinney

54.9k5 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nutsch Over a year ago

Somehow, I hate when my stupidity is pointed out... Thanks a ton

sh1rts Over a year ago

Doesn't mean you've been stupid, nutsch - we all have moments where we can't see the obvious after staring at our own code for too long. Sometimes just takes a fresh pair of eyes, that's all..

Collectives™ on Stack Overflow

SqlDataReader HasRows but doesn't Read

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related