[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4200
  • Last Modified:

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
0
sapbucket
Asked:
sapbucket
  • 3
  • 3
  • 3
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now