none
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" RRS feed

  • Question

  • problem

    Concurrency violation: the UpdateCommand affected 0 of the expected 1 records"


    update code

     Me.Validate()
                DataAdapter1.FillSchema(DataSet1, SchemaType.Mapped)
                BindingSource1.EndEdit()
                DataAdapter1.Update(DataSet1, "web_tb")

    another way

      Me.Validate()
                BindingSource1.EndEdit()
                DataAdapter1.Update(DataSet1, "web_tb")
                DataSet1.AcceptChanges()
                DataGridView1.Refresh()


    • Edited by ahmeddc Saturday, December 9, 2017 8:49 AM
    Saturday, December 9, 2017 8:30 AM

Answers

  • Hi ahmeddc,

    Please take a look the following code, you can get the affect row number.

     Dim adapter As OleDbDataAdapter
        Dim builder As OleDbCommandBuilder
        Dim ds As New DataSet("TestTable")
        Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
           
            Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb;Persist Security Info=False;"
            Dim sql As String = "select * from Test14"
            Dim conn As New OleDbConnection(str)
            conn.Open()
            Dim cmd As New OleDbCommand(sql, conn)
            adapter = New OleDbDataAdapter(cmd)
            builder = New OleDbCommandBuilder(adapter)
            adapter.Fill(ds, "TestTable")
            BindingSource1 = New BindingSource(ds, "TestTable")
            DataGridView1.DataSource = BindingSource1
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dr = BindingSource1.AddNew()
            dr("Id") = 7
            dr("Column1") = "G"
            dr("Column2") = "GG"
            BindingSource1.EndEdit()
            Dim value As Integer = adapter.Update(ds, "TestTable")
    
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Tuesday, December 12, 2017 9:02 AM
    Monday, December 11, 2017 9:19 AM
    Moderator

All replies

  • What is the principle of a DataAdapter. 

    You read datarows (overloaded also datasets and datables which include datarows) and change those. 

    In the datarows are original fields and changed fields. 

    Before the update is done, the dataadapter checks if the original field is still the same as it was with the first time reading. 

    If that is not the case, an Concurrency violation is detected and nothing is updated. 

    If there is no concurrency violation the update takes place and the dataadapter does it inbuild acceptchanges.

    The acceptchanges set the changed fields over the original fields.

    This should mean that the original fields are always the same as the database fields. However, that is not always the case for instance not with deleted rows or the first record. 

    A simple solution for that is to do a Fill again after an Update. 


    Success
    Cor



    Saturday, December 9, 2017 4:20 PM
  •  string

     Public con As New OleDb.OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\web_database.accdb;Jet OLEDB:Database Password=12345")
        Dim DataSet1 As New DataSet
        Dim BindingSource1 As BindingSource
        Dim BindingSource2 As BindingSource
        Dim DataAdapter1 As New OleDbDataAdapter
        Public DataTable1 As New DataTable


    conection code

    Dim Table1 As OleDbCommand = New OleDbCommand(" SELECT * from web_tb ", con) DataAdapter1 = New OleDbDataAdapter(Table1) Dim builder As New OleDbCommandBuilder(DataAdapter1) DataSet1 = New DataSet() DataAdapter1.Fill(DataSet1, "web_tb") BindingSource1 = New BindingSource(DataSet1, "web_tb") BindingSource2 = New BindingSource(DataSet1, "web_tb")

     Label2.DataBindings.Add("Text", BindingSource1, "ID_web", True, 1, "")


    add new row

     BindingSource1.AddNew()
            Label2.Text = DataSet1.Tables("web_tb").Compute("Max(ID_web)", "ID_web >= 0") + "1"
            DataGridView1.Refresh()

    update

     Me.Validate()
                BindingSource1.EndEdit()
                DataAdapter1.Update(DataSet1, "web_tb")
                DataSet1.AcceptChanges()
                DataGridView1.Refresh()

    Saturday, December 9, 2017 5:46 PM
  • What happens if you comment out the AcceptChanges ?

    Have you checked HasChanges prior to executing DataAdapter.Update(DataSet1,""web_tb)?

    This is from an example I just posted, does the saves, no accept changes. It uses a TableAdapter rather than a DataAdapter but below the surface of both the mechanics are the same. Ignore the KarenDialogs, that is simple a static class which wraps MessageBox into clean functions to present a MessageBox with standard options pre-set.

    Private Sub customersBindingNavigatorSaveItem_Click_1(ByVal sender As Object, ByVal e As EventArgs)
    	If northWindDataSet.HasChanges() Then
    		If KarenDialogs.Question("Save changes back to database?") Then
    			Me.Validate()
    			Me.customersBindingSource.EndEdit()
    
    			Me.ordersBindingSource.EndEdit()
    
    			Me.tableAdapterManager.UpdateAll(Me.northWindDataSet)
    		End If
    	Else
    		MessageBox.Show("There are no changes")
    	End If
    
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Frank L. Smith Sunday, December 10, 2017 8:43 PM
    Sunday, December 10, 2017 12:43 AM
    Moderator
  • What is the variable asked for  ??

    UpdateAll
    Me.tableAdapterManager.UpdateAll(Me.northWindDataSet)
    		
    Sunday, December 10, 2017 8:19 AM
  • It's asking for a (in this case) a NothWindDataSet as per below as shown in the tooltip.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, December 10, 2017 12:39 PM
    Moderator
  • The same problem and the same error
    Sunday, December 10, 2017 4:21 PM
  • The same problem and the same error

    There's another way you can do this.

    Use the BindingSource's .EndEdit method (like you're doing), do NOT use .AcceptChanges.

    If you ask for what the changes are, then "Nothing" means that you don't want to try to update -- if you do then you'll get the exception that you're getting.

    You can use the DataTable's .GetChanges method which returns a DataTable:

    https://msdn.microsoft.com/en-us/library/k2552649(v=vs.110).aspx

    All you really need to know is if the .GetChanges IsNot Nothing. If that's the case (ergo, a datatable was returned so there are changes) then use the .Update. If you do it that way, it should work.

    Understand though: If you try to update (save) and there's nothing to update (there are no changes) you'll get that exact exception.


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Sunday, December 10, 2017 8:47 PM
  • The same problem and the same error

    Do you know what the error message is telling you that is coming back from SQL Server? If you don't know the circumstance that causes the exception, then how can you fix it?
    Sunday, December 10, 2017 10:02 PM
  • Hi ahmeddc,

    Please take a look the following code, you can get the affect row number.

     Dim adapter As OleDbDataAdapter
        Dim builder As OleDbCommandBuilder
        Dim ds As New DataSet("TestTable")
        Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
           
            Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb;Persist Security Info=False;"
            Dim sql As String = "select * from Test14"
            Dim conn As New OleDbConnection(str)
            conn.Open()
            Dim cmd As New OleDbCommand(sql, conn)
            adapter = New OleDbDataAdapter(cmd)
            builder = New OleDbCommandBuilder(adapter)
            adapter.Fill(ds, "TestTable")
            BindingSource1 = New BindingSource(ds, "TestTable")
            DataGridView1.DataSource = BindingSource1
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dr = BindingSource1.AddNew()
            dr("Id") = 7
            dr("Column1") = "G"
            dr("Column2") = "GG"
            BindingSource1.EndEdit()
            Dim value As Integer = adapter.Update(ds, "TestTable")
    
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Tuesday, December 12, 2017 9:02 AM
    Monday, December 11, 2017 9:19 AM
    Moderator