none
Concurrency violation: the DeleteCommand affected 0 of the expected 1 records. RRS feed

  • Question

  • I have a nagging problem.  I have several forms with hierarchical data that update fine but consistently give the Concurrency violation on deletes.  I am using Access 2007 and there is only one user.  In the DB I defined one-to-many relationships with "Enforce Referential Integrity, Cascade Updates and Cascade Deletes" on each.  I am using VS 2010 (VB) with datatypes.  I created forms and dropped the data tables on the forms as described in the "Walkthrough: Saving Data from related data tables (Hierarchical Update)." on the MSDN help pages. 

     

    After consistently getting this error I thought it was the interface from MY DB to the TableAdapterManager.   I then created a new project, downloaded the  NorthWindDB and followed the instructions from the afore mentioned help page  using Orders and Order_details tables.  I have the same problem there.  If I open the form and choose the second record and press the delete->save buttons on the BindingNavigator (Orders Navigator Bar) I get the error.   Below is the only code I that is on the form.

     

    Public

     

    Class

    MainForm

     

       Private Sub OrdersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

    OrdersBindingNavigatorSaveItem.Click

     

          Me

    .Validate()

     

          Me

    .OrdersBindingSource.EndEdit()

     

          Me

    .Order_DetailsBindingSource.EndEdit()

     

          Me.TableAdapterManager.UpdateAll(Me

    .NwindDataSet1)

     

       End

    Sub

     

       Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase

    .Load

     

          Me.OrdersTableAdapter.Fill(Me

    .NwindDataSet1.Orders)

     

          Me.Order_DetailsTableAdapter.Fill(Me

    .NwindDataSet1.Order_Details)

     

       End

    Sub

    End

     

    Class

     

     

    Sunday, January 29, 2012 12:49 AM

All replies

  • Hi RC8,

    Based on my testing, It works on my computer, please try my T-sql:

    CREATE TABLE [dbo].[Employees](
    	[EmployeeId] [int] NOT NULL,
    	[Name] [nvarchar](20) NOT NULL,
    	[Phone] [nvarchar](20) NULL,
    	[Email] [nvarchar](20) NULL,
    	[ModifyTime] [datetime] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[EmployeeId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    CREATE TABLE [dbo].[Vacation](
    	[Id] [int] NOT NULL,
    	[Days] [int] NULL,
    	[EId] [int] NOT NULL,
    	[test] [nchar](10) NULL,
     CONSTRAINT [PK_Vacation] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Vacation]  WITH CHECK ADD  CONSTRAINT [FK] FOREIGN KEY([EId])
    REFERENCES [dbo].[Employees] ([EmployeeId])
    GO
    
    ALTER TABLE [dbo].[Vacation] CHECK CONSTRAINT [FK]
    GO
    
    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 31, 2012 3:21 AM
    Moderator
  • Alan;

    Sorry for not getting back sooner, but time is a precious commodity for me at the moment. 

    However, I am not sure which RDBMS T-SQL is for.  I opened MSACCESS (which is where the problem is please see above) and entered your query in a blank DB and it errored on the first line.

    With updates working, this leads me to believe that the problem may be in the way .net handles NULLS in a MSACCESS DB.  I looked at the queries in the TABLEADAPTER and I see that every field is used in the where in delete.  I have not had time to figure out the translation issues involved.  If this is a problem can you point towards a documented solution? 

     

    THanks;

    rc8

    Thursday, February 2, 2012 2:34 AM
  • After many hours of research and many trial projects I have narrowed the problem down to a behavior  I can not figure out.  I have several sets of tables with partent - child relationships.  On one set the TableAdapterManager.UpdateAll has the exact opposite behavior than what is stated.  On inserts it attempts to insert the children row(s) before attempting to insert the parent row and on deletes it attempts to delete the parent row prior to deleteing the children.  On the other table sets it appears to work correctly.  I figured this out by putting an _adapter.RowUpdated event handler on each table to see exactly how the rows were being sent to the database.

    I have checked everything I can to make sure the relationships are the exact same both in the DB and in the typed dataset.  All tables are in the same dataset so it can not be the dataset configuration.  THe tableadapters are setup the same for all parents and all children. 

    If anyone has seen this PLEASE let me know.  THis has project has taken FAR TOO LONG already.  HELP PLEASE!!!

       

    Monday, February 6, 2012 3:35 AM