c# - Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

Question

Here is the code, any ideas why I get this error?

private SQLiteDataAdapter DA_Webfiles;
// Setup connection, fill dataset etc

DataTable dt = this.dataSet.Tables["WEBFILES"];
DataRow newRow = dt.NewRow();
newRow["PATH"] = _url;
dt.Rows.Add(newRow);
this.DA_Webfiles.Update(this.dataSet, "WEBFILES");
// Works to Here

newRow["CONTENT_TYPE"] = "Test Content Type";
this.DA_Webfiles.Update(this.dataSet, "WEBFILES");
// Get ERROR here - Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

Answer 1

you need: dataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

got code clue here: Retrieving Identity or Autonumber Values (ADO.NET)

the table:

CREATE TABLE [emp] (
[emp_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[emp_firstname] VARCHAR(100) NOT NULL,
[emp_lastname] varchar(100) not null
)

the code:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();


        var c = Connect();

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



        var b = new SQLiteCommandBuilder(da);

        da.InsertCommand = new SQLiteCommand(
            @"insert into emp(emp_firstname, emp_lastname ) values(:_emp_firstname, :_emp_lastname);
            select emp_id /* include rowversion field here if you need */ from emp where emp_id = last_insert_rowid();", c);
        da.InsertCommand.Parameters.Add("_emp_firstname", DbType.String, 0, "emp_firstname");
        da.InsertCommand.Parameters.Add("_emp_lastname", DbType.String, 0, "emp_lastname");
        da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

        da.UpdateCommand = b.GetUpdateCommand();
        da.DeleteCommand = b.GetDeleteCommand();


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

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

        dt.Rows.Add(nr);

        da.Update(dt);

        dt.AcceptChanges();

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

    }

    SQLiteConnection Connect()
    {
        return new SQLiteConnection(@"Data Source=../../test.s3db;Version=3;");
    }
}

the code above works on multi-insert too. proof-of-concept code:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();


        var c = Connect();

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



        var b = new SQLiteCommandBuilder(da);

        da.InsertCommand = new SQLiteCommand(
            @"insert into emp(emp_firstname, emp_lastname ) values(:_emp_firstname, :_emp_lastname);
            select emp_id /* include rowversion field here if you need */ from emp where emp_id = last_insert_rowid();", c);
        da.InsertCommand.Parameters.Add("_emp_firstname", DbType.String, 0, "emp_firstname");
        da.InsertCommand.Parameters.Add("_emp_lastname", DbType.String, 0, "emp_lastname");
        da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

        da.UpdateCommand = b.GetUpdateCommand();
        da.DeleteCommand = b.GetDeleteCommand();


        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);

    }

    SQLiteConnection Connect()
    {
        return new SQLiteConnection(@"Data Source=../../test.s3db;Version=3;");
    }
}