Craigton

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.



Re: .NET Framework Data Access and Storage SqlDataReader "Invalid attempt to read when no data is present"

Erick Thompson - MSFT

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





Re: .NET Framework Data Access and Storage SqlDataReader "Invalid attempt to read when no data is present"

Craigton

Thanks for this Erick - worked a treat!



Re: .NET Framework Data Access and Storage SqlDataReader "Invalid attempt to read when no data is present"

ActionManAce

This worked perfectly. I replaced

Code Block
if (Reader.HasRows)

with

Code Block
if (Reader.Read())

Muchas Gracias





Re: .NET Framework Data Access and Storage SqlDataReader "Invalid attempt to read when no data is present"

Iraqi

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.