Concurrency violation for new records on DataTable when I do dataadapter.Update() RRS feed

  • Question

  • I work with DataTable , BindingSource , SqlDataAdapter

    I save the changes in DataTable using  SqlDataAdapter.Update()

    everything  works fine untill I try to Update() a new inserted row.

    A row that was allready inserted to database and also exist on DataTable without its identity PK.

    So it seems to me  like the SqlDataAdapter cant use DeleteCommand and UpdateCommand
    when the DataRow doesnt have a value in its ID field

    AutoIncrement doesnt solve the problem.

    I read somewhere that the SqlDataAdapter throws DBConcurrencyException when it finds out that
    the row on database has changed since last Fill() or AcceptChanges().

     What do I do with this ?

    How can I make these DataRows valid like those that were added using Fill() ???

    thanx . Im quite lost on this.
    Tuesday, December 8, 2009 12:47 PM

All replies

  • Do you have primary key defined in your database table? If yes, how is it defined?
    Val Mazur (MVP)
    Wednesday, December 9, 2009 11:18 AM
  • thanx for your reply .
      i have a PK defined :
    item_id  int identity PRIMARY KEY
    t is a SQL SERVER EXPRESS 2005

    Sunday, December 13, 2009 10:25 AM
  • Hello,

    Does the problem still exist if the PK has been defined? 

    Best Regards,
    Lingzhi Sun

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, December 15, 2009 12:38 AM
  • yes , I the Pk is defined as I mentioned before . 

    Im trying t oavoid  calling the Fill()  methond of the DataAdapter. Whene i do call Fill every time - I dont have that cuncurrency problem .\

    and when idont  call Fill() after Update() - I cant Update and Delete records that were added after the last call to Fill()

    Im sure there is a simple solution for this,  whithout a solution for this I must work very uneficiently. 

    thanx for any new direction or anything....
    Tuesday, December 15, 2009 12:37 PM
  • I realise this is an old post...maybe someone will find it useful.  I had this same problem - more or less...After I performed the .Update for the new row, I reread the row from SQL to obtain the newly created ID field and created a datarow to update the datatable with the newly created ID.  Subsequently, I created a different datarow to make further changes to the record.  On trying to write these changes back to SQL using .Update, I received the 'concurrency' error.

    It think it's something to do with creating two different datarows: the first to update the ID, the second for other changes, and then trying to perform an .Update.  

    The issue was resolved by placing a datatable.AcceptChanges() after updating the datatable with the ID.

    Friday, October 21, 2011 6:00 AM
  • If your DataSet contains DataTables with relations (parent and child tables), then I'd think you'd need to update the database with the new rows in order to retrieve any Indentity/primary key values back after the insert is complete.  If you are using Access as a db, then you'll likely need to add an event handler to take care of the auto/identity numbering so that the child table receives the correct foreign key value of its parent table.In SQL Server you could build your own Stored Procedure that handles the insert to return the Identity value.  Both solutions would use two seperate DataAdapters in its approach.


    James Crandall ~ Spatial Database Solutions
    Friday, October 21, 2011 12:19 PM