I have a database table with one field, called ID, being an auto increment integer. Using a TableAdapter I can read and modify existing rows as well as create new ones.

However if I try to modify a newly inserted row I get an DBConcurrencyException:

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Shift.mdb;Persist Security Info=True");
ShiftDataSetTableAdapters.ShiftTableAdapter shiftTA = new ShiftDataSetTableAdapters.ShiftTableAdapter();
shiftTA.Connection = conn;

ShiftDataSet.ShiftDataTable table = new ShiftDataSet.ShiftDataTable();
ShiftDataSet.ShiftRow row = table.NewShiftRow();
row.Name = "life";
table.Rows.Add(row);
shiftTA.Update(row);    // row.ID == -1
row.Name = "answer";    // <-- all fine up to here
shiftTA.Update(row);    // DBConcurrencyException: 0 rows affected

Separate question, is there any static type of the NewShiftRow() method I can use so that I don't have to create table everytime I want to insert a new row.

I guess the problem in the code comes from row.ID that is still -1 after the first Update() call. The Insert is successful and in the database the row has a valid value of ID.

How can I get that ID so that I can continue with the second Update call?

Update:

IT looks like this could have been done automatically using this setting. However according to the answer on msdn social, OLEDB drivers do not support this feature.

Not sure where to go from here, use something else than oledb?

Update:

Tried SQLCompact but discovered that it had the same limitation, it does not support multiple statements.

Final question: is there any simple(single file based) database that would allow you to get the values of a inserted row.