We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Display rows from different tables in DataGridView - DataSet Tables Rows

sapbucket
sapbucket asked
on
Medium Priority
4,254 Views
Last Modified: 2008-01-09
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
Comment
Watch Question

Author

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 Engineer

Commented:
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 Engineer

Commented:
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)
Commented:
/* 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);  
            }

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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 Engineer

Commented:
Ignore my first post...mjmarlow is right, you have to get a copy of the row

Author

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?

Commented:
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.


               
       
     

Author

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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.