Getting concurrency error on updating record with data adapter

Question

This is my table:

Student:StudentId int PK autoincrement,Name varchar(20)

When i am trying to update last added records then i am geting error:

Error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

This is my code:

using (var connection = new SqlConnection("MyConnectionstring"))
            {
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                adapter.SelectCommand = new SqlCommand("select * from Student", connection);


                DataTable dt = new DataTable();
                adapter.Fill(dt);

                DataRow row = dt.NewRow();
                row["Name"] = "Abc";
                dt.Rows.Add(row);
                var addedRecords = dt.GetChanges(DataRowState.Added);
                adapter.Update(dt);
                dt.AcceptChanges();
                DataRow lastRow = dt.Rows[dt.Rows.Count - 1];

                row["Name"] = "Pqr";
                adapter.Update(dt); //Error Here
                connection.Close();
            }  

Can anybody please tell me why this is happening and what can be the workaround for this problem??

Hiett

You modified the same row twice. Was that intentional? Or did you intend to modify the last row? – Prettify

I think, if you want to modify lastrow, int lastrow = dt.Rows.Count - 1; row[lastrow]["Name"] = "pqr"; – Vex

@PanagiotisKanavos:first i want to add record and then i want to update that recently added record and actually i am trying to understand the process of rowstate that is why i am doing this – Hiett

@sowjanyaattaluri :i want to modify the last row in both datatable and in database table – Hiett

need to write query to modify in database table. – Vex

@sowjanyaattaluri:if i need to write query for it then what is the meaning of this line:adapter.Update(dt); This update command will fire appropriate insert,update query internally based onrowstate property which will be changed in my last case – Hiett

You are updating datatable only that means copy of database table not the original one. – Vex

Let us continue this discussion in chat. – Hiett

Downvoters please at least care to comment your reason for downvoting – Hiett

Answer 1

As described in the Generating Commands with CommandBuilders MSDN topic, the automatic commands generated by the command builders do not retrieve the identity fields for the inserted records:

You might want to map output parameters back to the updated row of a DataSet. One common task would be retrieving the value of an automatically generated identity field or time stamp from the data source. The DbCommandBuilder will not map output parameters to columns in an updated row by default. In this instance you must specify your command explicitly.

Looking at Retrieving Identity or Autonumber Values topic, it turns out that basically you need to generate the insert command manually.

Here is how you can do that for your table (see the comments inside the code):

using (var connection = new SqlConnection("MyConnectionstring"))
{
    connection.Open();

    // Create data adapter with the specified SelectCommand
    var adapter = new SqlDataAdapter("select * from Student", connection);

    // Build InsertCommand    
    var insertCommand = new SqlCommand(
        "insert into Student (Name) values (@Name) SET @Id = SCOPE_IDENTITY()", 
        connection);
    insertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 20, "Name");
    var parameter = insertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "StudentId");
    parameter.Direction = ParameterDirection.Output;
    insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
    adapter.InsertCommand = insertCommand;

    // Auto build outher commands
    var builder = new SqlCommandBuilder(adapter);

    // Read the data
    var dt = new DataTable();
    adapter.Fill(dt);

    // Insert a new record
    var row = dt.NewRow();
    row["Name"] = "Abc";
    dt.Rows.Add(row);

    adapter.Update(dt);

    // Update the just inserted record
    row["Name"] = "Pqr";
    adapter.Update(dt);

    connection.Close();
}  

Yukyukaghir

So is this the reason that during updation of my last records ado.net is unable to find Id of last record because that record is commited in database but still not there in datatable as pointed out by @Amy?? – Hiett

Yes. This is a specific issue with ID columns that are auto generated by the database. It happens only when insert (add new) and the generated value must somehow be read back (because update, delete etc. really need that id in order to match the table record with the corresponding database record). – Yukyukaghir

Most probably I can, but please fill in another question, so I (or someone else) can answer it. – Yukyukaghir

ok i have tried your solution and it is working perfect but now i want to use command builder for updating records so how do update records with update query??? see this fiddle:dotnetfiddle.net/600e4Y – Hiett

Well, you have to bind all the parameters used in the command, e.g. updateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "StudentId");. And don't forget to assign the command you created to adapter.UpdateCommand. – Yukyukaghir

But in this I have some code repetation so my point is can we have any generic structure for this to avoid this code repetation.any idea or something you would like to suggest please – Hiett

You should ask yourself you do you want to create UpdateCommand manually. As I mentioned in the answer, only the InsertCommand is needed, the others can be handled by the command builder. – Yukyukaghir

Actually i am confused regarding when to use command builder and manually write update parameterised query and when not to use this approach – Hiett

can you help me with this question please:#37966627 – Hiett

Answer 2

When you do the first update, the row is written to the database and it gets a primary key per the AUTOINCREMENT. However, the row inside the DataTabledoes not reflect the changed ID. Therefore, when you try to do the second update, it can't find the row you're intending to update (the ID in the data table doesn't match the ID in the database). Consequently, you get a concurrency error.

That's why, in order to get the ID into the DataTable, you will need to refresh the contents of the DataTable before doing the second update.

To refresh the DataTable, call:

adapter.Fill()

For more information, read Merging DataSet Contents.

Jaco