Reader returning 5 rows, data table has 1

Question

I have an sql statement that I'm trying to execute against a sqlite db.

The problem is, with only this one sql statement, the datatable reports 1 row, event though if I look at the reader it has 5 rows in the results view.

SQL:

SELECT 
    f.host_id,h.hostname,h.computer_name_dns_fully_qualified,
    l.username,l.logon_domain 
FROM scans s 
LEFT JOIN hosts h ON h.scan_id=s.scan_id 
LEFT JOIN logged_on_users l ON l.host_id=h.host_id 
LEFT JOIN hosts f ON f.computer_name_netbios LIKE l.logon_ser...

This is how it's called:

        var dt = new DataTable();

        try
        {
            var com = new SQLiteCommand(_con) { CommandText = sql };
            var reader = com.ExecuteReader();
            dt.Load(reader);
            reader.Close();
        }
        catch
        {
        }

        return dt;

So when you use an SQL client and directly run this SQL statement you get 1 row, but through code the reader has 5? are they the same 5 rows? — Daniel Moses, Feb 28 '12 at 19:19
nope. When I run it in SQL browser it returns 5. The reader.resultsview has 5 objects. dt.load(reader); now dt contains 1 row. — Bluebaron, Feb 28 '12 at 19:21
for completeness sake, can you add where you instantiate dt. — Daniel Moses, Feb 28 '12 at 19:22
if you have 1 row in table `scans`, joining to other tables that have more rows for the same `scan_id` would give you all the joined rows... if that is the case, it is the correct and to-be-expected result — Aprillion, Feb 28 '12 at 19:23
@deathApril you mean that even if the raw sql executes in sqlite browser and returns 5 rows, that i might still only get 1 row? — Bluebaron, Feb 28 '12 at 19:28
@Bluebaron oh,, sorry, i thought it was the other way round, that you expected 1 row and got 5 :( i know nothing about C# but a wild guess - isn't there `LoadAll` method to fetch all rows? — Aprillion, Feb 28 '12 at 19:31
This works in every other case but this one. I think I'm going to have to report this to sqlite. — Bluebaron, Feb 28 '12 at 19:51
The problem, even though it was definitely returning 5 rows, is that the primary keys of the rows that were being returned were the same. Even though the data was different. I realized that I actually needed different primary keys anyways. If you get this issue, check that you're not getting one column with all the same data. Also, someone suggested an ORDER BY which they claim will fix the same issue though I could not get it to work. SELECT f.host_id as host_id,h.hostname as ... should be: (note only the 8th character has changed) SELECT h.host_id as host_id,h.hostname as ... — Bluebaron, Feb 28 '12 at 20:40

score 2 · Accepted Answer · answered Feb 29 '12 at 14:37

The problem is that the datatable fill method as described in the MSDN article is matching the primary key and overwriting each row. I knew that if that was true, only the last row would be returned. I went back and undid all my code changes and sure enough, only the last row was returned. This is because each row loaded into the datatable overwrote the row before. If you have this issue, and I've now been pointed to some threads--some as old as 4 years without resolution--stop, drop, and check to see if all the values in one particular column are the same; Datatables is likely keying your data by that column.

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

Add an order by to the query. DataTables need a unique field to work with and order by will sort this. As datatables can update there contents by loading a reader into them.

See : Similar Issue

edited May 23 '17 at 12:15

Community

1
1

answered Feb 28 '12 at 20:01

Simon Thompson

708
6
14

I'm going to give your comment an up vote but post my own solution to this in the hopes that people might understand this issue. The problem is that all rows were returning the same primary key. – Bluebaron Feb 28 '12 at 20:35

score 0 · Answer 3 · edited Dec 15 '12 at 14:27

0

your joining scans to hosts and the hosts to logged_on_users then filter on logged_on_users If the table with the filter is producing multiple rows then you will get cartesian multiplication.

Try breaking part of query into sub query.

Also if this is SQL Server, ask business studio for the query plan - post that as a picture and we can answer clearly.

Thanks Simon

edited Dec 15 '12 at 14:27

marc_s

732,580
175
1,330
1,459

answered Feb 28 '12 at 19:33

Simon Thompson

708
6
14

Nope. Not a cartesian join issue. If it were an issue with the SQL statement, I wouldn't be getting 5 rows in the reader and in the SQL client. – Bluebaron Feb 28 '12 at 19:36
if i understand the comment by Bluebaron correctly, the question is about having 1 row in variable `dt` instead of 5 rows returned by SQL – Aprillion Feb 28 '12 at 19:37
Right. SQL is returning 5 rows. dt.load is not working properly. – Bluebaron Feb 28 '12 at 19:41
sorry i misunderstood, thanks deathApril for your question, o and Bluebaron for straight answer :-) – Simon Thompson Feb 28 '12 at 19:56

Tim Schmelter · Answer 4 · 2012-02-28T19:49:43.210

0

Avoid an empty catch-block since it leads to issues like this. Instead log the exception and/or throw the exception. http://www.codeproject.com/Articles/9538/Exception-Handling-Best-Practices-in-NET

The Load method consumes the first result set from the loaded IDataReader, and after successful completion, sets the reader's position to the next result set, if any.

Evidently there is an exception, therefore you only see one row in the DataTable. So you should set a breakpoint to see what happens.

You are also not closing the connection when there is an exception, that belongs into the finally-block(or use the using statement).

edited Feb 28 '12 at 19:49

answered Feb 28 '12 at 19:36

Tim Schmelter

450,073
74
686
939

Yes, but it will say x number of rows. This happens properly with every request but this one. – Bluebaron Feb 28 '12 at 19:41
_What_ will say _x_ number of rows? Evidently there is an exception, so you should set a breakpoint to see what happens. You are also not closing the connection when there is an exception, that belongs into the finnally-block(or use the `using` statement). – Tim Schmelter Feb 28 '12 at 19:44
dt.rows.count will says 1. Other queries it says 24, 1061, 40, etc. – Bluebaron Feb 28 '12 at 19:48
catch(Exception e) { Console.WriteLine(e.Message); } – Bluebaron Feb 28 '12 at 19:48
still no messages printed on the console – Bluebaron Feb 28 '12 at 19:48
Have you set a breakpoint in the catch-block to see if an exception is raised? You can also execute the load method in an [quick-watch box](http://msdn.microsoft.com/en-us/library/0taedcee.aspx). Afterwards you can use the quick-watch box to call `dt.GetErrors()`. This returns one or multiple DataRows with a property [`RowError`](http://msdn.microsoft.com/en-us/library/system.data.datarow.rowerror.aspx). This will lead you to the actual exception if any. – Tim Schmelter Feb 28 '12 at 19:54

Reader returning 5 rows, data table has 1

4 Answers4