Get id when inserting new row using TableAdapter.Update on a file based database

Question

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.

Answer 1

Why not select the MAX(RowId), as your RowId should increment for each INSERT? Is this possible for you?

As for your final answer, SQLite might be the perfect tool for you. I hope so! And it has its own .NET Data Provider, so no need for OLEDB or ODBC providers.

Answer 2

Try this http://support.microsoft.com/kb/815629 , the sample code is in VB.NET though.

Or if multiline query is accepted in MS Access and it has built-in function/variable for retrieving the last id, use this (the database is SQLite though): http://stackoverflow.com/questions/1599230/anyway-see-why-i-get-this-concurrency-violation-in-these-few-lines-of-code/1604547#1604547 , try to google for the function

[EDIT: Works on my Machine, I don't have SQL Server Compact, but I didn't use multi-statement]

public Form1()
{
    InitializeComponent();


    var c = Connect();

    var da = new SqlDataAdapter("select emp_id, emp_firstname, emp_lastname from emp where 1 = 0", c);



    var b = new SqlCommandBuilder(da);

    var getIdentity = new SqlCommand("SELECT CAST(@@IDENTITY AS INT)", c);

    da.InsertCommand = b.GetInsertCommand();
    da.UpdateCommand = b.GetUpdateCommand();
    da.DeleteCommand = b.GetDeleteCommand();
    da.RowUpdated += (xsender, xe) =>
    {
        if (xe.Status == UpdateStatus.Continue && xe.StatementType == StatementType.Insert)
        {
            xe.Row["emp_id"] = (int)getIdentity.ExecuteScalar();
        }
    };


    var dt = new DataTable();
    da.Fill(dt);

    var nr = dt.NewRow();
    nr["emp_firstname"] = "john";
    nr["emp_lastname"] = "lennon";


    var nrx = dt.NewRow();
    nrx["emp_firstname"] = "paul";
    nrx["emp_lastname"] = "mccartney";


    dt.Rows.Add(nr);
    dt.Rows.Add(nrx);

    da.Update(dt);

    dt.AcceptChanges();


    nrx["emp_lastname"] = "simon";
    da.Update(dt);

    nr["emp_lastname"] = "valjean";
    da.Update(dt);

}



SqlConnection Connect()
{
    return new SqlConnection(@"data source=.\SQLEXPRESS;Database=Test;uid=sa;pwd=hey");
}