SQLDataAdapter not returning last row of data

Question

I'm writing a program to pull data from an SQL database and input it into Excel. I have it all working, except that I noticed the rows I have returned in Excel do not match what I see in SQL. The last row is consistently trimmed when filling the DataTable object.

Info: Visual Studio 2015, SQL Server 11.0.5058.

I have tracked down the problem to how I am retrieving the SQL data in the below method. I put in a check after going through this method to output how many rows were returned, and it's consistently one less than I should have (queries are identical). I think it's an indexing issue but I can't see how, given the simplicity of the below method. I can't figure out why the last row is being trimmed out when put into the data table.

private static DataTable PullData(string connstr, string query)
    {
        // Creating connection to SQL server
        SqlConnection conn = new SqlConnection(connstr);
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();
        DataTable dataTable = new DataTable();
        // create data adapter
        using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
        {
            da.SelectCommand.CommandTimeout = 3600;
            // query database and return the result to your datatable
            da.Fill(dataTable);
            da.Dispose();

        }

        conn.Close();
        return dataTable;
    }

****EDIT****: Thank you Tim for helping me track down the issue.. Turns out it was not in my DataTable but in my Excel Range object. There must be differences between how indexing works in Excel/SQL/C# from the last time I used this method of writing data to Excel. Since Excel technically sees Row 1 as the column header, I had to add 1 to the number of rows for the Excel Range object in order for it to accept the correct total number:

Excel.Range range = wsheet.Range["A2", String.Format("{0}{1}", GetExcelColumnName(columns), rows+1)];

The code above doesn't contain any reason to not return what you have asked in the query passed. So the problem is probably in your query text. Do you have some WHERE condition in that query? — Steve
– Steve, Commented Sep 14, 2016 at 12:04
Also, you should always use sql parameters. This method prevents doing that since it accepts a string and doesn't give access to the SqlCommand's parameter or provide a way to pass them. So it encourages sql-injection. — Tim Schmelter
– Tim Schmelter, Commented Sep 14, 2016 at 12:06
an identical query in SQL Studio returns all the requested data. I.E.: If a table returns 10 rows in SQL, it should return 11 rows to this method (because column names becomes the first row (row 0)). — Alex
– Alex, Commented Sep 14, 2016 at 12:09
wouldn't the DataTable object return a total number of rows that includes the column names in datatable.rows.count? — Alex
– Alex, Commented Sep 14, 2016 at 12:13
Acc. your last edit: The first row (header) in excel is 1 not 0 as in C#. So the first data row starts with 2. — Tim Schmelter
– Tim Schmelter, Commented Sep 14, 2016 at 12:39

Tim Schmelter · Accepted Answer · 2016-09-14 12:28:53Z

5

an identical query in SQL Studio returns all the requested data. I.E.: If a table returns 10 rows in SQL, it should return 11 rows to this method (because column names becomes the first row (row 0)).

Why do you think that the column names are in the first row? You get the names via dataTable.Columns:

foreach(DataColumn col in dataTable.Columns)
{
    Console.WriteLine("Column:{0} Type:{1}", col.ColumnName, col.DataType);
}

wouldn't the DataTable object return a total number of rows that includes the column names in datatable.rows.count

No, dataTable.Rows only returns the DataRows which contain the records, not columns.

So you could f.e. list all fields of all DataRows in this way:

for(int i = 0; i < dataTable.Rows.Count; i++)
{
    DataRow row = dataTable.Rows[i];
    foreach (DataColumn col in dataTable.Columns)
    {
        Console.WriteLine("Row#:{0} Column:{1} Type:{2} Value:{3}",
            i + 1,
            col.ColumnName, 
            col.DataType,
            row[col]);
    }
}

Could the above foreach block be used to populate a two dimensional array? I'm using the below to dump all of the data into Excel: object[,] data = new object[dt.rows.count, dt.columns.count];

Yes, that's possible. Modify the loops accordingly:

object[,] data = new object[dataTable.Rows.Count, dataTable.Columns.Count];
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
{
    for (int colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++)
    {
        data[rowIndex, colIndex] = dataTable.Rows[rowIndex][colIndex];
    }
}

edited Sep 14, 2016 at 12:28

answered Sep 14, 2016 at 12:13

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Over a year ago

Ahhh, this helps me. Thanks for the information. I'll look at my code again for a different solution and get back to you.

Alex Over a year ago

Could the above foreach block be used to populate a two dimensional array? I'm using this to dump all of the data into Excel at once: object[,] data = new object[dt.rows.count, dt.columns.count];

Alex Over a year ago

Thank you very much for the information. You helped me track down where the problem was. I'll edit my question to explain...

Collectives™ on Stack Overflow

SQLDataAdapter not returning last row of data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related