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?