anyway see why I get this "Concurrency Violation" in these few lines of code??? 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

thanks

Answer 1

What happened:

The first Update() will add the new row to the table, and the DataTable marks it as existing. But you do not merge-back from the Database, so your memory record does not have the new (Db-generated) key.

The second update fails because it performs an Update with the wrong temporary key.

How to solve it:

I know that the Dataset designer tools can set this up for MS-SQL. I don't know SQL-Lite, or how you created the adapter. See if you have an option to refresh updated records. Otherwise you will have to write that yourself.

---

Edit: I took a look at a VS generated Dataset. The "Configure Adapter" wizard has an option to "Refresh the Table". With that option selected, the Update command looks like the following XSD nippet. I replaced code for all other fields with '...'

Note that there actually 2 SQL statements being performed. But this may be specific to MS-SQL, you will have to check.

<CommandText>
UPDATE [Person].[Address] SET [AddressLine1] = @AddressLine1, ....
   WHERE (([AddressID] = @Original_AddressID) AND ....);
SELECT AddressID, AddressLine1, .... 
FROM Person.Address WHERE (AddressID = @AddressID)
</CommandText>

Answer 2

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