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

Question

I have a DataTable which has some rows and I am using the select to filter the rows to get a collection of DataRows which I then loop through using foreach and add it to another DataTable, but it is giving me the error "This Row already belongs to another table". Here is the code:

DataTable dt = (DataTable)Session["dtAllOrders"];
DataTable dtSpecificOrders = new DataTable();

DataRow[] orderRows = dt.Select("CustomerID = 2");

foreach (DataRow dr in orderRows)
{
    dtSpecificOrders.Rows.Add(dr); //Error thrown here.
}

great question; I get confused about rows and tables belonging to other containers — Vivian River, Nov 15 '10 at 17:47

score 367 · Accepted Answer · edited May 17 '17 at 17:14

367

You need to create a new Row with the values from dr first. A DataRow can only belong to a single DataTable.

You can also use Add which takes an array of values:

myTable.Rows.Add(dr.ItemArray)

Or probably even better:

// This works because the row was added to the original table.
myTable.ImportRow(dr);

// The following won't work. No data will be added or exception thrown.
var drFail = dt.NewRow()
drFail["CustomerID"] = "[Your data here]";
// dt.Rows.Add(row); // Uncomment for import to succeed.
myTable.ImportRow(drFail);

edited May 17 '17 at 17:14

Hans Vonn

3,949
3
21
15

answered Apr 06 '09 at 15:50

JoshBerke

66,142
25
126
164

7

Can I use ImportRow as an alernative? and Why would .NET only not allow you to have the same DataRow for different DataTables? Is this by design? – Xaisoft Apr 06 '09 at 15:52
Thanks for the above snippet. I was just about to ask if I had 100 rows, would I have to type all of them out? – Xaisoft Apr 06 '09 at 15:53
3

Heh I just realized ImportRow, edit my answer and you all beat me to it. But yea I'd recommend that approach as it will copy the row for you – JoshBerke Apr 06 '09 at 15:54
If I do it the dr.ItemArray way, would I still need to create a NewRow? – Xaisoft Apr 06 '09 at 15:55
2

`ImportRow` din work for me. But Add Rows did! Thanksssssssssss – nawfal Apr 13 '11 at 12:42
1

ImportRow will not work if the row you import is a reference to the row in the source table. ItemArray works if you use the source row, but you have to remember to add the row to the destination before you remove the row from the source, otherwise it complains the row has been removed and cannot find the data. – Adam Houldsworth Jun 11 '11 at 06:59
Does anyone have an article where it describes the disadvantage of DataTable vs other .Net collections(List,Dictionary,ObservableCollection) ? – Misi Nov 05 '13 at 14:46
2

`.ImportRow()` didn't work for me because I didn't do `.Clone()` or create columns on my new DataTable, initially. It came up with 0 when I looked at `.Rows.Count` on my new table. Since I was going to need to create columns on my table and add them, I just created a `NewRow()` statement and added my values to the columns on that new row directly and added that row to my table, within the loop (see my answer below). – vapcguy Jun 13 '16 at 20:02
1

myTable.Rows.Add(dr.ItemArray) works only when the columns are properly specified in myTable. – sm2mafaz Jan 24 '18 at 17:35
Thank you. I used ImportRow, but I discovered it was good to build the columns before hand. – j.hull Apr 19 '19 at 17:49
As mentioned the destination table has to have the correct columns before you call ImportRow(). You can use the .Clone() method on the source DataTable to create the columns in the destination table. Clone() doesn't copy any data, just the structure. DataTable dtDestination = dtSource.Clone(); dtDestination.ImportRow(dtSource.Rows[0]); ... – kooch Dec 16 '20 at 19:47
For Me this worked 100% myTable.Rows.Add(dr.ItemArray) My Two Tables are the same, but I need to error records in one and the non error records in the other – Marius Van Wyk Nov 19 '21 at 18:04

score 36 · Answer 2 · edited Feb 28 '12 at 00:33

36

Try this:

DataTable dt = (DataTable)Session["dtAllOrders"];
DataTable dtSpecificOrders = dt.Clone();

DataRow[] orderRows = dt.Select("CustomerID = 2");

foreach (DataRow dr in orderRows)
{
    dtSpecificOrders.ImportRow(dr);
}

edited Feb 28 '12 at 00:33

jcollado

39,419
8
102
133

answered Feb 27 '12 at 13:25

Shekhar Purwar

369
3
2

3

Thank you for this.. although the answer provided by @JoshBerke was correct you need to clone the original table as in your example for it to work. – carny666 Apr 17 '13 at 11:39

score 10 · Answer 3 · edited Jul 13 '12 at 13:46

10

yourTable.ImportRow(dataRow);

It's because the row you're copying doesn't have the same TableName:

For example, try:

Table1.TableName = "Table1";
Table2.TableName = "Table2";

edited Jul 13 '12 at 13:46

nhahtdh

55,989
15
126
162

answered Apr 20 '12 at 08:53

jersoft

478
2
9
20

1

I had a misnamed table too. Thanks! – Bruno Mar 19 '13 at 14:06

score 4 · Answer 4 · answered Jun 29 '13 at 10:16

4

foreach (DataRow dr in dtSpecificOrders.rows)
{
   dtSpecificOrders.Rows.Add(dr.ItemArray); 
}

answered Jun 29 '13 at 10:16

Avinash Singh

2,697
11
44
72

1

I don't think you can add anything to what you are iterating, as you are iterating through it. – vapcguy May 26 '17 at 19:46
1

IMO, this is the most important answer on this page. Suppose you want to change the value of one field then add another record with all other fields the same. If you use dt.NewRow it will clear the item array. Many posts suggest you clone the datatable and row to preserve the values, but that is not necessary if you use this form of the Rows.Add method. Otherwise you need the dt.AcceptChanges() method, all of which can be avoided by this one, simple, perfect line of code posted seven years ago. I could not find this answer anywhere else. – pghcpa Nov 02 '21 at 22:30

vapcguy · Answer 5 · 2016-06-13T20:06:15.380

This isn't the cleanest/quickest/easiest/most elegant solution, but it is a brute force one that I created to get the job done in a similar scenario:

DataTable dt = (DataTable)Session["dtAllOrders"];
DataTable dtSpecificOrders = new DataTable();

// Create new DataColumns for dtSpecificOrders that are the same as in "dt"
DataColumn dcID = new DataColumn("ID", typeof(int));
DataColumn dcName = new DataColumn("Name", typeof(string));
dtSpecificOrders.Columns.Add(dtID);
dtSpecificOrders.Columns.Add(dcName);

DataRow[] orderRows = dt.Select("CustomerID = 2");

foreach (DataRow dr in orderRows)
{
    DataRow myRow = dtSpecificOrders.NewRow();  // <-- create a brand-new row
    myRow[dcID] = int.Parse(dr["ID"]);
    myRow[dcName] = dr["Name"].ToString();
    dtSpecificOrders.Rows.Add(myRow);   // <-- this will add the new row
}

The names in the DataColumns must match those in your original table for it to work. I just used "ID" and "Name" as examples.

score 1 · Answer 6 · edited Feb 26 '15 at 03:19

1

Why don't you just use CopyToDataTable

DataTable dt = (DataTable)Session["dtAllOrders"];
DataTable dtSpecificOrders = new DataTable();

DataTable orderRows = dt.Select("CustomerID = 2").CopyToDataTable();

edited Feb 26 '15 at 03:19

ecoe

4,994
7
54
72

answered Oct 21 '13 at 16:30

MSTr

223
1
7

1

What is orderRows supposed to be here? – Avi Jul 22 '14 at 16:32
A new DataTable of the selected rows from `dt`, Avi. – vapcguy May 26 '17 at 19:44

score 1 · Answer 7 · answered Oct 12 '22 at 17:48

1

Try to copy the DataTable using Copy method dt1 = dt.Copy();

then remove all the rows form dt1 using Clear method dt1.Rows.Clear();

Then start importing it again

foreach (DataRow myRow in dt.Rows)
{
   dt1.ImportRow(myRow);
}

answered Oct 12 '22 at 17:48

Arun Raut

19
2

score 0 · Answer 8 · answered Aug 28 '17 at 04:26

0

you can give some id to the columns and name it uniquely.

answered Aug 28 '17 at 04:26

Adi_Pithwa

421
5
7

score 0 · Answer 9 · answered Jan 09 '23 at 13:15

0

One should be careful when faced with this error especially in binding scenarios. This may well be an indicator that you might bind to a wrong DataTable.

answered Jan 09 '23 at 13:15

Mustafa Özçetin

1,893
1
14
16

score 0 · Answer 10 · answered Mar 06 '23 at 17:42

0

I found that DataTable dt2 = dt.clone(); worked for me...

answered Mar 06 '23 at 17:42

Richard Tomassetti

1

score 0 · Answer 11 · answered Apr 19 '23 at 16:08

0

Why not use LoadDataRow (object.ItemArray, true).

myDS.myTable.LoadDataRow(myTableRow.ItemArray, true);

This will not only add a new row but will in fact see if the row already exists and simply update the existing. For me this makes more sense as it is more versatile as simply ImportRow even though Import is faster if you know that indeed you are always adding a new item to the collection and not simply adding an existing item with updated values.

answered Apr 19 '23 at 16:08

kuklei

1,115
11
14

I use this technique when I have two forms that communicate with each other. The main form has a list of all items and the child form is for only updating or creating a new item and handles the work with validation and saving to database. When the child form returns I dont need to load all the items from the database again, but I simply load the data row that was just sent to the db from the child form – kuklei Apr 19 '23 at 16:11

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

11 Answers11

Linked

Related