In my work with Access Database I needed to duplicate an existing row in the table and get an ID of newly inserted row. Below are short snippets of code showing how.
Duplicating a DataRow
// load the data into Data Table DataTable dataTable = tableAdapter.GetData(); // clone a Data Table DataTable duplicatedDataTable = dataTable.Clone(); // get row to duplicate DataRow row = dataTable.Rows; // import row into cloned Data Table duplicatedDataTable.ImportRow(row);
This way we get a deep copy of the existing row and later use table adapter to insert it into database.
Getting auto-incremented ID number after inserting a row into Access Database
The actual idea of getting an auto-incremented ID of the new row in Access Database is very simple. Right after insert of the row we need to execute similar query to this one:
SELECT MAX(ID) FROM TableNameIn order to avoid loading wrong ID in multi-user environment it is good to add some WHERE selection criteria which will limit selection only to duplicated rows. MAX function will pick the ID of the last inserted row.