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?
 
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);  
            }
0
 
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...
0
 
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);
            }
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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)
0
 
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++)....
0
 
Ravi SinghSenior Software EngineerCommented:
Ignore my first post...mjmarlow is right, you have to get a copy of the row
0
 
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?
0
 
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.


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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.