Display rows from different tables in DataGridView - DataSet Tables Rows

Hello,

  I have a dataset with dozens of tables. Each table has only a few rows of data. I would like to combine all the rows from each table (somehow) and display them in a DataGridView.

Here is what I tried, but I get an error (see comments in code):

            DataSet ds = new DataSet();
            string tableName;

            ds = Fill(); // dataset it filled with Tables (only a few rows per table). Fill() method not included for brevity.

            DataSet combinedDataSet = new DataSet();

            for (int i = 0; i < ds.Tables.Count; i++ )
            {
                DataRow newRow = combinedDataSet.Tables[0].NewRow(); // <-- error: "cannot find table 0."

                newRow = ds.Tables[i].Rows[0];

                combinedDataSet.Tables[0].Rows.Add(newRow);

            }
            //dataGridView1.DataSource = ds.Tables[0].Rows[0]; // however, this works (shows me one row in dataGridView1)
            dataGridView1.DataSource = combinedDataSet.Tables[0];
            dataGridView1.Refresh();



This is strange to me because I can definitely see table 0 in the dataGridView1 (see comment above). But, if I try to merge them together I get an error (see comment above).


Any chance you know how to handle this problem?

Many thanks in advance!
---sapbucket
LVL 3
sapbucketAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sapbucketAuthor Commented:
I tried adding a table before I add a row and I get past my original error, but now I have a new error: (see comment below)

...
            DataSet combinedDataSet = new DataSet();

            DataTable newTable = new DataTable();

            combinedDataSet.Tables.Add(newTable);

            for (int i = 0; i < ds.Tables.Count; i++ )
            {
                DataRow newRow = combinedDataSet.Tables[0].NewRow();

                newRow = ds.Tables[i].Rows[0];

                combinedDataSet.Tables[0].Rows.Add(newRow);  //<---- error: This row already belongs to another table.

            }
            //dataGridView1.DataSource = ds.Tables[0].Rows[0];
            dataGridView1.DataSource = combinedDataSet.Tables[0];
            dataGridView1.Refresh();


As you can see I am new to this. Not sure how to copy over a row to the other table...
Ravi SinghSenior Software EngineerCommented:
Hi, try changing your for loop to:

            for (int i = 0; i < ds.Tables.Count; i++ )
            {
                DataRow newRow =  ds.Tables[i].Rows[0];
                combinedDataSet.Tables[0].Rows.Add(newRow);
            }
Ravi SinghSenior Software EngineerCommented:
Also, if that doesn't work, you might need to add the same columns you have in the other DataSet's tables to the combinedDataSet's table (before the for loop executes)
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

mjmarlowCommented:
/* assuming your datatables have the same schema */

            for (int i = 0; i < ds.Tables.Count; i++ )
            {
                DataRow newRow = combinedDataSet.Tables[0].NewRow();

                newRow.ItemArray = ds.Tables[i].Rows[0].ItemArray;

                combinedDataSet.Tables[0].Rows.Add(newRow);  
            }

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mjmarlowCommented:
BTW - your looping logic indicates copying a new row to the same table.  Perhaps you should begin at the 2nd table using:
for(int i = 1; i < ds.Tables.Count; i++)....
Ravi SinghSenior Software EngineerCommented:
Ignore my first post...mjmarlow is right, you have to get a copy of the row
sapbucketAuthor Commented:
mjmarlow -

I tried this:

for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataRow newRow = combinedDataSet.Tables[0].NewRow();

                newRow.ItemArray = ds.Tables[i].Rows[0].ItemArray; // <---- error: "Input array is longer than the number of columns in this table."

                combinedDataSet.Tables[0].Rows.Add(newRow);
            }



which to means that combinedDataSet does not have the same schema (layout) as ds.

how does one go about copying over a schema from one dataset to another?
mjmarlowCommented:
Assuming there is some shared key between tables, the following article covers joining tables together.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;326080

If you do not have shared keys, then in general:

1. create a target table with N string columns, where N is the most number of columns in any data table in your dataset

2. Employ this algorythm
for each source data table.
   create a new row for target table
   for each row in source data table
           for (j=0; j < row ItemArray size; j++)
                 new row ItemArray [ j ] = ConverToString( row ItemArray [ j ] )
   add new row to target table

At the end of this target should have the data from other tables.


               
       
     
sapbucketAuthor Commented:
yes, mjmarlow's algorithm is in general correct. I did not have shared keys.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.