asked on

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

sapbucket

ASKER

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 Singh

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 Singh

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)

ASKER CERTIFIED SOLUTION

mjmarlow

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mjmarlow

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 Singh

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

sapbucket

ASKER

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?

mjmarlow

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.

sapbucket

ASKER

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