VB.net Debug sqldatareader - immediate window

Question

Scenario is this; I've a sqldatareader query that seems to be returning nothing when I try to convert the sqldatareader to a datatable using datatable.load.

So I debug into it, I grab the verbose SQL query before it goes into the sqldatareader, just to make sure it's formatted correctly. I copy and paste this into SQL server to run it and see if it returns anything. It does, one row.

I go back to visual studio and let the program continue, I create a datatable and try to load the sqldatareader but it just returns an empty reader. I'm baffled as to what's going on.

I'll copy a version of the code (not the exact SQL query I'm using but close) here:

Dim cn As New SqlConnection
cn.ConnectionString = <connection string details here>
cn.Open()
Dim sqlQuery As String = "select * from Products where productid = 5"
Dim cm As New SqlCommand(sqlQuery, cn)
Dim dr As SqlDataReader = cm.ExecuteReader()

Dim dt as new DataTable
dt.load(dr)

dt should have contents but it's empty. If I copy that SQL query into sql server and run it I get a row of results.

Any ideas what I'm doing wrong?

######### UPDATE ############

I've now noticed that it seems to be returning one less row than I get with each SQL query. So, if I run the SQL myself and get 1 row then the datatable seems to have 0 rows. If the query returns 4 rows, the datatable has 3!! Very strange, any ideas anyone?

######### FURTHER UPDATE ############

Ok I've done a loop to count the rows of the datareader before using the datatable.load method. In my test query I'm getting 4 rows (the correct amount) in the datareader, as soon as I use the datatable.load method it says the datatable is empty, what's going on?!

###### Ok

So it seems a datareader is a one-way thing, iterate through it to get the row count and it's essentially empty as far as any other bit of code is concerned. Didn't realise it was forward only and wouldn't 'reset' once read through.

Answer 1

Insert this line above dt.load(dr), check the value in your debug window:

Debug.WriteLine("Datareader has rows: " & dr.HasRows)

Also make sure your connection gets closed by changing cm.ExecuteReader() to :

cm.ExecuteReader(CommandBehavior.CloseConnection);