.NET DataTable skips rows on Load(DataReader)

Question

I'm trying to populate a DataTable, to build a LocalReport, using the following:

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = new MySqlConnection(Properties.Settings.Default.dbConnectionString);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ... LEFT JOIN ... WHERE ..."; /* query snipped */

// prepare data
dataTable.Clear();
cn.Open();
// fill datatable
dt.Load(cmd.ExecuteReader());
// fill report
rds = new ReportDataSource("InvoicesDataSet_InvoiceTable",dt);
reportViewerLocal.LocalReport.DataSources.Clear();
reportViewerLocal.LocalReport.DataSources.Add(rds);

At one point I noticed that the report was incomplete and it was missing one record. I've changed a few conditions so that the query would return exactly two rows and... surprise: The report shows only one row instead of two. I've tried to debug it to find where the problem is and I got stuck at

 dt.Load(cmd.ExecuteReader());

When I've noticed that the DataReader contains two records but the DataTable contains only one. By accident, I've added an ORDER BY clause to the query and noticed that this time the report showed correctly.

Apparently, the DataReader contains two rows but the DataTable only reads both of them if the SQL query string contains an ORDER BY (otherwise it only reads the last one). Can anyone explain why this is happening and how it can be fixed?

Edit: When I first posted the question, I said it was skipping the first row; later I realized that it actually only read the last row and I've edited the text accordingly (at that time all the records were grouped in two rows and it appeared to skip the first when it actually only showed the last). This may be caused by the fact that it didn't have a unique identifier by which to distinguish between the rows returned by MySQL so adding the ORDER BY statement caused it to create a unique identifier for each row.
This is just a theory and I have nothing to support it, but all my tests seem to lead to the same result.

Mod+1 because I've been programming .NET and ADO.NET every day since the fist betas and I never knew I could just call dt.Load() without a DataAdapter. Sigh. — Dave Markle, Oct 27 '08 at 01:16
Similar situation here, Dave. I learned about it only about a month ago. — Joel Coehoorn, Oct 27 '08 at 19:55
Today I have posted a duplicate question (https://stackoverflow.com/questions/28676382/datatable-load-shows-less-rows-than-source-datareader) that was eventually solved by wrapping the query. I want to highlight that this **only** happens on MYSQL. It doesn't happen on SQLSERVER.I think the problem shouldn't be caused by DataTable component but rather could be a bug in Mysql connector. Any thoughts? — usr-local-ΕΨΗΕΛΩΝ, Feb 23 '15 at 17:15

score 13 · Answer 1 · edited Dec 07 '20 at 19:40

After fiddling around quite a bit I found that the DataTable.Load method expects a primary key column in the underlying data. If you read the documentation carefully, this becomes obvious, although it is not stated very explicitly.

If you have a column named "id" it seems to use that (which fixed it for me). Otherwise, it just seems to use the first column, whether it is unique or not, and overwrites rows with the same value in that column as they are being read. If you don't have a column named "id" and your first column isn't unique, I'd suggest trying to explicitly set the primary key column(s) of the datatable before loading the datareader.

I added an ID column to no avail. – Rhyous Sep 25 '13 at 20:34 — Rhyous, Sep 25 '13 at 20:34

score 6 · Answer 2 · edited Aug 18 '12 at 08:09

6

Just in case anyone is having a similar problem as canceriens, I was using If DataReader.Read ... instead of If DataReader.HasRows to check existence before calling dt.load(DataReader) Doh!

edited Aug 18 '12 at 08:09

Alfred

21,058
61
167
249

answered Jan 09 '11 at 17:18

tant

61
1
2

score 5 · Answer 3 · answered Dec 09 '13 at 13:23

5

Don't use

dr.Read()

Because It moves the pointer to the next row. Remove this line hope it will work.

answered Dec 09 '13 at 13:23

James

172
2
10

1

Oh man, thank you for this little detail, it solves my issue. – EduBic Mar 16 '20 at 17:38

score 5 · Accepted Answer · answered Jan 17 '09 at 07:59

I had same issue. I took hint from your blog and put up the ORDER BY clause in the query so that they could form together the unique key for all the records returned by query. It solved the problem. Kinda weird.

score 4 · Answer 5 · answered Apr 15 '14 at 12:16

4

Came across this problem today.

Nothing in this thread fixed it unfortunately, but then I wrapped my SQL query in another SELECT statement and it work!

Eg:

SELECT * FROM (
    SELECT ..... < YOUR NORMAL SQL STATEMENT HERE />
) allrecords

Strange....

answered Apr 15 '14 at 12:16

Jamie Hartnoll

7,231
13
58
97

score 4 · Answer 6 · answered Feb 28 '12 at 20:51

Had the same issue. It is because the primary key on all the rows is the same. It's probably what's being used to key the results, and therefore it's just overwriting the same row over and over again.

Datatables.Load points to the fill method to understand how it works. This page states that it is primary key aware. Since primary keys can only occur once and are used as the keys for the row ...

"The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created." (http://msdn.microsoft.com/en-us/library/zxkb3c3d.aspx)

Might not only affect the primary key. If the primary key is not in the results, it may key another column. — Bluebaron, Feb 28 '12 at 20:55
I went back to check, just to make sure. Absolutely. The reader returns 5 rows, but the dt.Load only gives you the last row in the reader. Just so y'all know that I wasn't talking out my ass. I think this is the first time someone's actually pin pointed this one. Check please! :) — Bluebaron, Feb 28 '12 at 20:57

score 1 · Answer 7 · answered Oct 23 '08 at 11:52

1

Can you grab the actual query that is running from SQL profiler and try running it? It may not be what you expected.

Do you get the same result when using a SqlDataAdapter.Fill(dataTable)?

Have you tried different command behaviors on the reader? MSDN Docs

answered Oct 23 '08 at 11:52

StingyJack

19,041
10
63
122

Yes, I've tried running the queries in a MySQL client. The only difference between them is that one has ORDER BY Invoice.ID and the other one doesn't, they both return two rows in the same order. I'm going to check on SqlDataAdapter now. – Tom Oct 23 '08 at 11:55
Any luck? You dont by chance have a TOP in the Query do you? – StingyJack Oct 24 '08 at 18:34

score 1 · Answer 8 · answered Apr 28 '16 at 08:52

I know this is an old question, but for me the think that worked whilst querying an access database and noticing it was missing 1 row from query, was to change the following:-

    if(dataset.read())  - Misses a row.

    if(dataset.hasrows) - Missing row appears.

score 1 · Answer 9 · answered Oct 22 '19 at 17:46

For anyone else that comes across this thread as I have, the answer regarding the DataTable being populated by a unique ID from MySql is correct.

However, if a table contains multiple unique IDs but only a single ID is returned from a MySql command (instead of receiving all Columns by using '*') then that DataTable will only organize by the single ID that was given and act as if a 'GROUP BY' was used in your query.

So in short, the DataReader will pull all records while the DataTable.Load() will only see the unique ID retrieved and use that to populate the DataTable thus skipping rows of information

score 0 · Answer 10 · answered Mar 26 '14 at 14:54

I know this is an old question, but I was experiencing the same problem and none of the workarounds mentioned here did help.

In my case, using an alias on the colum that is used as the PrimaryKey solved the issue.

So, instead of

SELECT a
     , b
FROM table

I used

SELECT a as gurgleurp
     , b
FROM table

and it worked.

score 0 · Answer 11 · answered Oct 27 '08 at 21:15

Not sure why you're missing the row in the datatable, is it possible you need to close the reader? In any case, here is how I normally load reports and it works every time...

        Dim deals As New DealsProvider()
        Dim adapter As New ReportingDataTableAdapters.ReportDealsAdapter
        Dim report As ReportingData.ReportDealsDataTable = deals.GetActiveDealsReport()
        rptReports.LocalReport.DataSources.Add(New ReportDataSource("ActiveDeals_Data", report))

Curious to see if it still happens.

score 0 · Answer 12 · edited Sep 29 '11 at 12:41

0

Have you tried calling dt.AcceptChanges() after the dt.Load(cmd.ExecuteReader()) call to see if that helps?

edited Sep 29 '11 at 12:41

answered Oct 28 '08 at 14:37

Brian

116
5

score 0 · Answer 13 · answered Oct 09 '14 at 11:12

0

I had the same problem.. do not used dataReader.Read() at all.. it will takes the pointer to the next row. Instead use directly datatable.load(dataReader).

answered Oct 09 '14 at 11:12

IFlyHigh

546
2
9
20

score 0 · Answer 14 · answered Aug 03 '10 at 16:50

0

In my case neither ORDER BY, nor dt.AcceptChanges() is working. I dont know why is that problem for. I am having 50 records in database but it only shows 49 in the datatable. skipping first row, and if there is only one record in datareader it shows nothing at all.

what a bizzareeee.....

answered Aug 03 '10 at 16:50

canceriens

1

1

Next time, please post that as a comment or in the form of a question. This is not acceptable as an answer on StackOverflow. – Tom Aug 03 '10 at 19:54
He on has 1 point and doesn't have enough point to comment (at least 50) – qnguyen Nov 22 '17 at 05:49

score 0 · Answer 15 · answered Jul 20 '22 at 07:09

Encountered the same problem, I have also tried selecting unique first column but the datatable still missing a row.

But selecting the first column(which is also unique) in group by solved the problem.

i.e

select uniqueData,.....
from mytable
group by uniqueData;

This solves the problem.

.NET DataTable skips rows on Load(DataReader)

15 Answers15

Linked

Related