none
Concurrency violation: the DeleteCommand affected 0 of the expected 1 records [yet again] RRS feed

  • Question

  • I'm recieving that -very annoying- message on a very simple task consisting on

    a) Loading a datatable with a dataadaptor that contains a selectcommand with certain parameters (eg: "SELECT COL1,COL2 FROM MYTABLE WHERE COL1=@pVALUE")
    This returns some records.
    b) Deleting each row of the datatable as follows
       for each oneRow as dataRow on mytable.Rows()
          onerow.delete()
       next
    c) adding a delete command to the dataadaptor with the same parameter as in a) (eg: "DELETE FROM MYTABLE WHERE COL1=@pVALUE") and assigning the parameter to the column "COL1".
    it ends up with
    d) callling the update method of the dataadaptor that returns the message above.

    I've searched the site and I've found no similar scenarios.
    I have no cascade deletion, no insertions between the a),b),c) calls. The COL1 field is not the primary key and it is a VARCHAR(30) field.
    The param is defined  as VARCHAR 30 as well, both in the select and delete commands of the dataAdaptor.

    Any hint??
    I know I can work around this by executing the Delete command out of the dataAdaptor (eg: using executeNonQuery) but i just don't understand why it's not working. In fact, this approach is working fine on some other tables of my project.

    Thanks in advance.


    David
    Thursday, December 17, 2009 3:05 PM

Answers

  • Basically the error mean that the dataadapter is trying to a delete a row that doesn't exsist.
    Or rather, no row exists that matches the WHERE clause for the delete statement. This is all down to the optimistic concurreny design.

    In short, when you call update on the DataAdapter the .Net runtime will go through each row in the datatable.
    If the row is marked as Deleted, the delete SQL is created for that row and executed. In your case you most likely have more than one row.
    This means that "DELETE FROM MYTABLE WHERE COL1=@pVALUE" will be called multiple times. The first time is called it succeeds (deletes all rows
    where the where statement works), the second time (for the second row) there will be no rows deleted since they were delete previously and you
    will get the error.

    I guess the reason you are not using a SqlCommandBuilder is because you have not primary key. So you should create your own Delete command
    to include all columns in the table. (Or add a primary key).

    create table MyTable(col1 varchar(5), col2 varchar(5))
    
    insert into MyTable values ('aaaaa', 'bbbbb')
    insert into MyTable values ('aaaaa', 'ccccc')
    insert into MyTable values ('aaaaa', 'ddddd')
    insert into MyTable values ('aaaaa', 'eeeee')

                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "SELECT col1, col2 FROM MyTable WHERE col1 = @col1";
                    cmd.Parameters.AddWithValue("@col1", "aaaaa");
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    foreach (DataRow r in dt.Rows)
                    {
                        r.Delete();
                    }
                    SqlCommand delcmd = con.CreateCommand();
    
                    // This fails with your error
                    //delcmd.CommandText = "DELETE FROM MyTable WHERE col1 = @col1";
                    //delcmd.Parameters.AddWithValue("@col1", "aaaaa");
                    //da.DeleteCommand = delcmd;
    
                    // This works.
                    delcmd.CommandText = "DELETE FROM MyTable WHERE col1 = @col1 AND col2 = @col2";
                    delcmd.Parameters.Add("@col1", SqlDbType.VarChar, 5).SourceColumn = "col1";
                    delcmd.Parameters.Add("@OldCol1", SqlDbType.VarChar, 5, "col1").SourceVersion = DataRowVersion.Original;
    
                    delcmd.Parameters.Add("@col2", SqlDbType.VarChar, 5).SourceColumn = "col2";
                    delcmd.Parameters.Add("@OldCol2", SqlDbType.VarChar, 5, "col2").SourceVersion = DataRowVersion.Original;
                    da.DeleteCommand = delcmd;
    
                    da.Update(dt);
                    con.Close();
                }

    HTH
    //Michael


    This posting is provided "AS IS" with no warranties.
    Friday, December 18, 2009 9:00 AM

All replies

  • Basically the error mean that the dataadapter is trying to a delete a row that doesn't exsist.
    Or rather, no row exists that matches the WHERE clause for the delete statement. This is all down to the optimistic concurreny design.

    In short, when you call update on the DataAdapter the .Net runtime will go through each row in the datatable.
    If the row is marked as Deleted, the delete SQL is created for that row and executed. In your case you most likely have more than one row.
    This means that "DELETE FROM MYTABLE WHERE COL1=@pVALUE" will be called multiple times. The first time is called it succeeds (deletes all rows
    where the where statement works), the second time (for the second row) there will be no rows deleted since they were delete previously and you
    will get the error.

    I guess the reason you are not using a SqlCommandBuilder is because you have not primary key. So you should create your own Delete command
    to include all columns in the table. (Or add a primary key).

    create table MyTable(col1 varchar(5), col2 varchar(5))
    
    insert into MyTable values ('aaaaa', 'bbbbb')
    insert into MyTable values ('aaaaa', 'ccccc')
    insert into MyTable values ('aaaaa', 'ddddd')
    insert into MyTable values ('aaaaa', 'eeeee')

                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "SELECT col1, col2 FROM MyTable WHERE col1 = @col1";
                    cmd.Parameters.AddWithValue("@col1", "aaaaa");
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    foreach (DataRow r in dt.Rows)
                    {
                        r.Delete();
                    }
                    SqlCommand delcmd = con.CreateCommand();
    
                    // This fails with your error
                    //delcmd.CommandText = "DELETE FROM MyTable WHERE col1 = @col1";
                    //delcmd.Parameters.AddWithValue("@col1", "aaaaa");
                    //da.DeleteCommand = delcmd;
    
                    // This works.
                    delcmd.CommandText = "DELETE FROM MyTable WHERE col1 = @col1 AND col2 = @col2";
                    delcmd.Parameters.Add("@col1", SqlDbType.VarChar, 5).SourceColumn = "col1";
                    delcmd.Parameters.Add("@OldCol1", SqlDbType.VarChar, 5, "col1").SourceVersion = DataRowVersion.Original;
    
                    delcmd.Parameters.Add("@col2", SqlDbType.VarChar, 5).SourceColumn = "col2";
                    delcmd.Parameters.Add("@OldCol2", SqlDbType.VarChar, 5, "col2").SourceVersion = DataRowVersion.Original;
                    da.DeleteCommand = delcmd;
    
                    da.Update(dt);
                    con.Close();
                }

    HTH
    //Michael


    This posting is provided "AS IS" with no warranties.
    Friday, December 18, 2009 9:00 AM
  • Thank you!

     The following statement in your answer "This means that "DELETE FROM MYTABLE WHERE COL1=@pVALUE" will be called multiple times. The first time is called it succeeds (deletes all rows
    where the where statement works), the second time (for the second row) there will be no rows deleted since they were delete previously and you
    will get the error. " directed me towards the issue and I was able to resolve it.


    Subramaniam

    Wednesday, February 19, 2014 7:43 PM
  • Deathevader,

    Do you think somebody is watching this 5 years old thread still?


    Success
    Cor

    Sunday, March 2, 2014 8:06 AM
  • Im Watching
    Tuesday, November 13, 2018 4:12 PM
  • I've search for few days and only this solve my problem. Thanks a lot. By the way, I'm using vb.net.


      delcmd.Parameters.Add("@col2", SqlDbType.VarChar, 5).SourceColumn = "col2";
      delcmd.Parameters.Add("@OldCol2", SqlDbType.VarChar, 5, "col2").SourceVersion = DataRowVersion.Origina
    




    Saturday, June 1, 2019 10:20 AM