Row already belongs to another table error when trying to add rows?

Question

I tried this solution below:

This Row already belongs to another table error when trying to add rows?

I have a Datatable that contains 597 Columns and 20 Rows and are trying to export the data to excel. However, Excel has a maximum column count 256 and so I need to divide the source data into 3 datatables to make the export work.

Below is the code I have written.

var dtmasterdata = data.Tables[name];

for (int j = 1; j < datatableNumberCount; j++)
                        {
                            DataTable dt2 = new DataTable();
                            dt2.TableName = "Master_" + j;
                            dt2 = dtmasterdata.Copy();

                            foreach (DataColumn col in dtmasterdata.Columns)
                            {
                                DataColumn dtcol = new DataColumn();
                                dtcol = col;
                                dt2.Columns.Add(dtcol.ColumnName, dtcol.DataType);
                            }

                            for (int k = 0; k < dtmasterdata.Rows.Count; k++)
                            {
                                DataRow dr = dt2.NewRow();
                                dr = dtmasterdata.Rows[k];
                                dt2.ImportRow(dtmasterdata.Rows[k]);
                                //dt2.Rows.Add(dr.ItemArray);
                            }

After that I need to delete few columns like below and I want to create 3 datatables

foreach (DataColumn col in dtmasterdata.Columns)
                            {
                                if (j == 1)
                                {
                                    // condition 1
                                    if (col.Ordinal >= 255)
                                    {
                                        dt2.Columns.RemoveAt(col.Ordinal);
                                    }
                                }

                                if (j == 2)
                                {
                                    // condition 2.
                                    if (col.Ordinal < 255 || col.Ordinal >= 510)
                                    {
                                        dt2.Columns.RemoveAt(col.Ordinal);
                                    }
                                }

                                if (j == 3)
                                {
                                    // condition 3.
                                    if (col.Ordinal <= 510 || col.Ordinal >= 765)
                                    {
                                        dt2.Columns.Add(col);
                                    }
                                }
                            }

int worksheetNumber = 1;
                            string worksheetNameWithNumber = "Master Data";
                            if (worksheetNumber > 1)
                                worksheetNameWithNumber = String.Format("{0}_{1}", ws1, worksheetNumber.ToString());
                            Infragistics.Excel.Worksheet worksheet = wb.Worksheets.Add(worksheetNameWithNumber);
                            Infragistics.WebUI.UltraWebGrid.UltraWebGrid masterData1 = new Infragistics.WebUI.UltraWebGrid.UltraWebGrid("masterDataGrid");
                            masterData1.Browser = Infragistics.WebUI.UltraWebGrid.BrowserLevel.UpLevel;
                            masterData1.DataSource = dt2;
                            masterData1.DataMember = "Master_" + j;
                            masterData1.DisplayLayout.HeaderStyleDefault.Font.Bold = true;
                            masterData1.DisplayLayout.HeaderStyleDefault.Font.Name = "Arial";
                            masterData1.DisplayLayout.HeaderStyleDefault.Font.Size = FontUnit.Parse("10px");
                            masterData1.DisplayLayout.HeaderStyleDefault.BackColor = System.Drawing.Color.LightGray;
                            masterData1.DisplayLayout.RowStyleDefault.Font.Name = "Arial";
                            masterData1.DisplayLayout.RowStyleDefault.Font.Size = FontUnit.Parse("10px");
                            Infragistics.WebUI.UltraWebGrid.UltraGridBand masterBand1 = new Infragistics.WebUI.UltraWebGrid.UltraGridBand();
                            masterData1.Bands.Add(masterBand1);
                            dgResults.Controls.Add(masterData1);
                            masterData1.DataBind();
                            wb.ActiveWorksheet = worksheet;
                            this.ugWebGridExporter.Export(masterData1, worksheet);
                            worksheetNumber++;

Answer 1

I am not sure to have really understood all of your code, but to copy a subset of columns to another datatable there is a very simple method in the DataView class named ToTable where you can list the columns you want in the new table. As added bonus, this method copies also the data in the 20 rows of your original table.

So the only difficult is to list these columns to the method.

You can proceed in this way using linq over the DataColumn collection

string[] firstCols = dtmasterdata.Columns
                         .Cast<DataColumn>()
                         .Take(255)
                         .Select(x => x.ColumnName).ToArray();
string[] secondCols = dtmasterdata.Columns
                          .Cast<DataColumn>()
                          .Skip(255)
                          .Take(255)
                          .Select(x => x.ColumnName).ToArray();
string[] thirdCols = dtmasterdata.Columns
                         .Cast<DataColumn>()
                         .Skip(510)
                         .Select(x => x.ColumnName).ToArray();

DataTable t1 = dtmasterdata.DefaultView.ToTable("Master_1", false, firstCols);
DataTable t2 = dtmasterdata.DefaultView.ToTable("Master_2", false, secondCols);
DataTable t3 = dtmasterdata.DefaultView.ToTable("Master_3", false, thirdCols);

Answer 2

Your error is because you are trying to add a column to a datatable that already belongs to your source datatable.

dt2.Columns.Add(col);

You can't just iterate through the columns of a datatable and add them to another.

I've a solution to this, which involves cloning the source data and removing what you don't need.

1st, make 3 clones of the datatables you need. Below is an example with me creating my own source table with 596 columns. Notice that clone only takes the data table structure, no data!

var source597ColsTable = new DataTable("Source");

for (var i = 0; i <= 596; i++)
{
     source597ColsTable.Columns.Add(new DataColumn("Column" + i , typeof(string)));
}

DataRow newRow = source597ColsTable.NewRow();
source597ColsTable.Rows.Add(newRow);

var cols0To199Table = source597ColsTable.Clone();
var cols200To399Table = source597ColsTable.Clone();
var cols400To596Table = source597ColsTable.Clone();

Next copy all the rows from the source table into the clones. The below is a simple function to do so.

  private DataTable CopyRowsFromSource(DataTable sourceTable, DataTable destinationTable)
  {
     foreach (DataRow row in sourceTable.Rows)
     {
        destinationTable.Rows.Add(row.ItemArray);
     }

       return destinationTable;
    }

Then call this function for each of your tables.

    cols0To199Table = CopyRowsFromSource(source597ColsTable, cols0To199Table);
    cols200To399Table = CopyRowsFromSource(source597ColsTable, cols200To399Table);
    cols400To596Table = CopyRowsFromSource(source597ColsTable, cols400To596Table);

Finally, remove all the columns from the datatables to give you your split.

 private DataTable RemoveColumns(DataTable table, int startCol, int endCol)
 {
       var colsToRemove = new List<DataColumn>();

       for (var colCount = startCol; colCount <= endCol; colCount++)
       {
            colsToRemove.Add(table.Columns[colCount]);
       }

       foreach (DataColumn col in colsToRemove)
       {
            table.Columns.Remove(col);
       }

       return table;
}

Then call.. again for each cloned table.

cols0To199Table = RemoveColumns(cols0To199Table, 200, 596); 

cols200To399Table = RemoveColumns(cols200To399Table, 0, 199); 
cols200To399Table = RemoveColumns(cols200To399Table, 200, 396); 

cols400To596Table = RemoveColumns(cols400To596Table, 0, 399); 

After running this, you will have 3 datatables, columns 0-199, 200-399 and 400-596.

Hope that helps.