Question Datareader only depth=0 but there are 2 records

[nick447923]

I am in a intro VB.net college class and am a little frustrated with a problem I am having using a data reader to read the result set of a query.

Dim automobile As New Automobile

Dim connection As OleDbConnection = PaulMeadeInsuranceDB.GetConnection

Dim selectStatement As String = _
"SELECT * FROM Automobiles WHERE CustNumber = " _
& CustNumber.ToString

Dim selectcommand As New OleDbCommand(selectStatement, connection)

Dim autoList As New List(Of Automobile)

Try
connection.Open()
Dim reader As OleDbDataReader _
= selectcommand.ExecuteReader(CommandBehavior.SingleResult)



If reader.Read Then

Do While reader.Read()
automobile.Modelyear = CInt(reader("Model Year").ToString)
automobile.Description = reader("Description").ToString
automobile.Vin = (reader("VIN#1"))
automobile.Symbol = CInt(reader("Symbol").ToString)
automobile.Age = CInt(reader("Age").ToString)
autoList.Add(automobile)
reader.Read()
Loop

Else
automobile = Nothing
End If
reader.Close()


Return autoList


Catch ex As OleDbException
Throw ex
Catch e As Exception
Throw e
Finally
connection.Close()


End Try

The data reader that reads the result has a depth of 0 which means, I think, that only one record was returned even though there are two records in AUTOMOBILES with CustNumber = CustNumber.ToString. If create a query in Visual Studio that is the exact same as the above Query except with a fixed CustNumber it returns two records instead of one. I have played around with the command behavior but no luck. I have also asked my programming teacher but he was unable to find what was wrong. I even tried changing the Query to "SELECT * FROM Automobiles" and I still get only one record even though there are four records in the Automobiles table!!

Any help would be much appreciated!

 

[jmcilhinney]

The Depth has exactly nothing to do with the number of records returned, except that the Depth can never be more than the record count. You can execute more than one query at a time using a DataReader and each one will return its own result set. The first result set has a depth of 0, the second has a depth of 1, the third has a depth of 2, etc. You call NextResult to move to the next result set.

You've only got one query and therefore only one result set, so your Depth is always 0. Your problem is here:

If reader.Read Then

Do While reader.Read()

You're saying there that If you successfully read the first row, go on and read the rest of the rows. You effectively throw away the first record because you don't get any data after calling Read the first time. You call Read twice before you actually get any data so you're only going to get from the second record. What you mean is this:

If reader.HasRows Then

Do While reader.Read()

That said, what's the point of the If statement at all? Why not just use the loop alone? You call Read and if it's False you never enter the loop. The If statement offers no advantage.

 

[nick447923]

Datareader depth=0 but there are two records

You are exactly right-it works correctly now. I greatly appreciate your input!
I wondered why it was picking up the second record and not the first.

 

[cjard]

Nick

Every time you call reader.Read() it moves the reader onto the next row.. Youre calling it all over the place, multiple times, before you try to read the data

I'd suggest:

Use a DataAdapter instead
Read your data into a datatable
Read the PQ link in my signature, and never write an SQL like this again:
"SELECT * FROM Automobiles WHERE CustNumber = " & CustNumber.ToString

Big no-no.. Even if that's what youre being taught, learn to use parameterized queries now.. If your tutor takes exception to it, tell them youre concerned about SQL Injection hacking