copy a row from one table to another

[wpilcher]

This has to be simple but I can't find a way to do it. I have a temporary table that has unedited data in it. Then I have a vb.net form that reads these records into a dataset and allows user to navigate thru these records to edit the data. When all edits are passed the user clicks a button to add these records to a permanent table in the same database.

I can not come up with a way to do this that does not throw some exception or other, usually involving the fact that the row already exists. I know it already exists! I just want to add it to another table!

Can anyone put me on the right track or do you need more info?

[jhammer]

You cannot take set the parent of the table while it is in another table:

tempTable.Rows.Remove(row);
realTable.Rows.Add(row);

Another way will be tocopy manually the DataRow:

DataRow newRow = realTable.NewRow();
for (int i=0; i< tempTable.Columns.Count; i++)
   newRow[i] = row[i];

And last way is to use the Merge method of a DataSet (if your tables are in a DataSet).

[wpilcher]

I tried the first suggestion as below. Am I wrong?

dim dr as datarow
for each dr in ds.tables("temptable").rows
ds.tables("temptable").rows.remove(dr)
ds.tables("realtable").rows.add(dr)
next

I haven't had time to thoughly debug but I don't think the "remove" is working. It still thows exception on the "add" saying row already exists in another table.

I will investigate further and try your other suggestions. Thanks for the help.

[wpilcher]

Yariv, Thanks for the help. I couldn't get the remove(row) add(row) method to work but I'm probably missing something. It just seems so elegantly simple that it should work.

I did get the manual method of column by column replaces to work after some problems resulting from differences in the ordinal positions of some columns but I can live with that..

Thanks again.

[jhammer]

Based on your answer it is clear to me why my first suggestion did not work for you. In order to move a row from table to the other both tables MUST be in the exact schema. Even the columns must be in the exact order.
If you fix your tables to look the same, you will be able to add the row.