none
SqlDataReader "Invalid attempt to read when no data is present"

    Question

  • Hi,

     

    I used Stored Procedure to get data to fill a dataset to populate an array; it returns 35 rows and thought I would try out the SqlDataReader technique but I get no rows retrieved. The message box displays with "True" i.e. rows are present but the next statement generates the error "Invalid attempt to read when no data is present".

     

    Dim dbConnection As New SqlConnection(My.Settings.BookingsConnectionString)

    Using tempconnection As New SqlConnection(My.Settings.BookingsConnectionString)

    Dim sql As String = "spGetTimes"

    Dim scmd As New SqlCommand(sql, tempconnection)

    scmd.CommandType = CommandType.StoredProcedure

     

    tempconnection.Open()

    Dim reader As SqlDataReader = scmd.ExecuteReader()

     

    Do While reader.HasRows

    MessageBox.Show("Inside Reader HasRows = " & reader.HasRows)

    reader.GetSqlValue(0)

    reader.NextResult()

    Loop

    tempconnection.Close()

    End Using

     

     Thanks in advance.

    Friday, June 29, 2007 12:44 PM

Answers

  • Hello,

     

    HasRows returns a bool to indicate if you have any rows retuned, but it doesn't actually advanced the reader to the next (or first) row. You need to replace the call to HasRows with a call to Read(), which advanced the reader to the next row. Read() will return false if there are no more rows to read.

     

    Thanks,

    Erick

    Friday, June 29, 2007 4:46 PM

All replies

  • Hello,

     

    HasRows returns a bool to indicate if you have any rows retuned, but it doesn't actually advanced the reader to the next (or first) row. You need to replace the call to HasRows with a call to Read(), which advanced the reader to the next row. Read() will return false if there are no more rows to read.

     

    Thanks,

    Erick

    Friday, June 29, 2007 4:46 PM
  • Thanks for this Erick - worked a treat!
    Wednesday, July 04, 2007 12:57 PM
  • This worked perfectly. I replaced

     

    Code Block
    if (Reader.HasRows)

     

     

     

    with

     

    Code Block
    if (Reader.Read())

     

     

     

    Muchas Gracias

     

    Thursday, October 18, 2007 6:09 PM
  • If you want to know the reason beyond that, just put your code in a while loop like the following

     

    Try

    while Reader.Read

    If Reader.HasRows Then

    Object.fieldName = reader("String") or .GetValue(integer)

    End if

    End While  

     

    Catch ex as Exception

    Dim ErrorMsg As String = ex.Message

    Finally

    close the connection

    End try

     

    now if you see the ErrorMsg variable it has the real error, and your error is 99% is mis-matching (can not convert String to integer or any other converting issue) in Object.FieldName and Database field type.

    Saturday, November 24, 2007 11:22 PM