Search code examples
vb.netinvalidoperationexceptionno-data

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


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 

Solution

  • 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.