Why am I getting a "no data is present" error after passing the "HasRows" test?

Question

I've got this code:

Protected Function GetArgValsForCompanyName(coName As String) As String()
    Dim args(2) As String
    Dim sqlConnection1 As New SqlConnection("SERVER=PLATYPUS42;DATABASE=duckbilldata;UID=durante;PWD=pondscum")
    Dim cmd As New SqlCommand
    Dim reader As SqlDataReader

    cmd.CommandText = "select Unit, MemberNo, CustNo from Customers WHERE CompanyName = @CoName"
    cmd.CommandType = CommandType.Text
    cmd.Parameters.Add("@CoName", SqlDbType.VarChar, 50).Value = coName
    cmd.Connection = sqlConnection1
    sqlConnection1.Open()

    reader = cmd.ExecuteReader()
    If reader.HasRows Then
            args(0) = reader.Item(0).ToString()
            args(1) = reader.Item(1).ToString()
            args(2) = reader.Item(2).ToString()
    End If
    reader.Close()
    sqlConnection1.Close()

    Return args
End Function

...which fails on this line:

args(0) = reader.Item(0).ToString()

...with:

*System.InvalidOperationException was unhandled
  HResult=-2146233079
  Message=Invalid attempt to read when no data is present.*

How can it be that it "HasRows" and yet there is no data present?

NOTE: It also fails with the same error when I try this (instead of using the "0" index):

args(0) = reader.Item("Unit").ToString()

UPDATE

The accepted answer works fine in modern apps (e.g., my "sandbox" Windows forms app), but in olden apps, such as a creaky, archaic web site, which uses .NET prehistoric, it doesn't- the "usings" are apparently unrecognized; I get:

Server Error in '/EMS/customerreportingnet' Application.
--------------------------------------------------------------------------------

Compilation Error 
Description: An error occurred during the compilation of a resource required to service this request. 

Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: BC30203: Identifier expected.

Source Error:

Line 90:         Dim args(2) As String
Line 91: 
Line 92:         Using con As New SqlConnection("SERVER=PLATYPUS42;DATABASE=duckbilldata;UID=durante;PWD=pondscum"),
Line 93:               cmd As New SqlCommand("select Unit, MemberNo, CustNo from Customers WHERE 

CompanyName = @CoName", con)
Line 94: 

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.5485; ASP.NET Version:2.0.50727.5491

To address the issue with an archaic compiler which can't handle `Using`, you can hand-implement it using a `Try`-`Finally`. — Craig, Feb 27 '18 at 14:32

score 3 · Accepted Answer · edited May 23 '17 at 12:00

As you say in your answer, a call to .Read is required.

The If reader.HasRows however isn't required. The Do While reader.Read will handle this. If there are rows then it will enter the loop, otherwise it will bypass.

As an additional note, I think it would be beneficial to implement Using:

Protected Function GetArgValsForCompanyName(coName As String) As String()
    Dim args(2) As String

    Using con As New SqlConnection("SERVER=PLATYPUS42;DATABASE=duckbilldata;UID=durante;PWD=pondscum"),
          cmd As New SqlCommand("select Unit, MemberNo, CustNo from Customers WHERE CompanyName = @CoName", con)

        con.Open()

        cmd.CommandType = CommandType.Text
        cmd.Parameters.Add("@CoName", SqlDbType.VarChar, 50).Value = coName

        Using reader As SqlDataReader = cmd.ExecuteReader

            While reader.Read
                args(0) = reader.Item(0).ToString()
                args(1) = reader.Item(1).ToString()
                args(2) = reader.Item(2).ToString()
            End While

        End Using

    End Using

    Return args
End Function

With Using you don't have to worry about calling .Close or the disposing of objects. I also feel it reads better.

score 0 · Answer 2 · answered Mar 21 '17 at 17:03

A call to "read" is also required; this works (either name of field returned or its index can be used):

If reader.HasRows Then
    Do While reader.Read
        'args(0) = reader.Item(0).ToString()
        args(0) = reader.Item("Unit").ToString()
        args(1) = reader.Item(1).ToString()
        args(2) = reader.Item(2).ToString()
    Loop
End If

Why am I getting a "no data is present" error after passing the "HasRows" test?

UPDATE

2 Answers2