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

    Question

  • I am writing a simple database application

    I use the following logic to update my database:

    Me.AddressesBindingSource.EndEdit()
    Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

    However, if I add a new record, and then try to delete it, I get the concurrency violation error message

    (If i try to delete a pre-existing record it works fine)

    Can anyone help?
    frustrated visual basic user
    Wednesday, January 07, 2009 6:58 PM

Answers

  • marcstrong said:
    I use the following logic to update my database:

    Me.AddressesBindingSource.EndEdit()
    Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

    However, if I add a new record, and then try to delete it, I get the concurrency violation error message
    (If i try to delete a pre-existing record it works fine)


    Hi marcstrong,

    Welcome to MSDN forums!

    Based on my understanding, you are making a simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save) via Data Wizard with a BindingNavigator control.


    Please check the 11th post in this thread for detailed walkthrough
    :
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

    Presume an existing SQL Server database Inventory.mdf which has a table Staff.

    The table has five fields (Text type):

    StaffNumber (set as Primary key)

    StaffName

    Designation

    Department

    Location

     

    1.  Data menu -> Add New Data Source

    During wizard, select StaffDB.mdb and Staff table, finishing it will create a StaffDBDataSet.xsd file in Solution Explorer.

     

    2.  Data menu -> Show Data Sources  

    In Data Sources panel, drag&drop each data field to Form in turn in order to bind individual data.

    During this process, the StaffDBDataSet, StaffTableAdapter, StaffBindingNavigator and StaffBindingSource controls will be added automatically to Form, and five pairs of Label and TextBox controls will be added to Form. 

    3.    Set StaffBindingNavigator.BindingSource property as StaffBindingSource in Properties panel.

     

    4.    Set StaffBindingSource.DataSource property as StaffDBDataSet in Properties panel.

     

    5.    Add code in Form1.vb to Save event handle.

        Private Sub StaffBindingNavigatorSaveItem_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles SaveToolStripButton.Click  
            Me.Validate()  
            Me.StaffBindingSource.EndEdit()  
            Me.StaffTableAdapter.Update(Me.StaffDBDataSet.Staff)  
            Me.StaffDBDataSet.AcceptChanges()  
        End Sub 
     

    6.  Right-click the database file StaffDB.mdb in Solution Explorer -> change the "copy to output directory" property to "copy if newer".

     


    The above walkthrough works fine (insert/delete/update/save records) on my machine without the concurrency violation error. Please give it a try.



    Best regards,
    Martin Xie
    Wednesday, January 14, 2009 10:54 AM

All replies

  • By the way, there are other three methods to make simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save).

    1. Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2758290&SiteID=1

    Imports System.Data.OleDb
    Public Class Form1
    Dim myDA As OleDbDataAdapter
    Dim myDataSet As DataSet
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
    con.Open()
    myDA = New OleDbDataAdapter(cmd)
    'Automatically generates DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
    myDataSet = New DataSet()
    myDA.Fill(myDataSet, "MyTable")
    DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
    con.Close()
    con = Nothing
    End Sub
    ' Save data back into database
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Me.Validate()
    Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
    Me.myDataSet.AcceptChanges()
    End Sub
    End Class

    2. Execute Select/Insert/Delete/Update T-SQL commands in VB.NET code
    Code sample: How to Select/Insert/Delete/Update records in MS Access database in VB.NET
    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/
    Code sample: How to Select/Insert/Delete/Update records in SQL Server database in VB.NET
    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/

    3. Using DataSet/DataTable/DataAdapter in VB.NET code
    Please check the 12th post and 13th post in this thread for detailed code sample:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2471782&SiteID=1
     


    Best regards,
    Martin Xie

    Wednesday, January 14, 2009 10:40 AM
  • marcstrong said:
    I use the following logic to update my database:

    Me.AddressesBindingSource.EndEdit()
    Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

    However, if I add a new record, and then try to delete it, I get the concurrency violation error message
    (If i try to delete a pre-existing record it works fine)


    Hi marcstrong,

    Welcome to MSDN forums!

    Based on my understanding, you are making a simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save) via Data Wizard with a BindingNavigator control.


    Please check the 11th post in this thread for detailed walkthrough
    :
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

    Presume an existing SQL Server database Inventory.mdf which has a table Staff.

    The table has five fields (Text type):

    StaffNumber (set as Primary key)

    StaffName

    Designation

    Department

    Location

     

    1.  Data menu -> Add New Data Source

    During wizard, select StaffDB.mdb and Staff table, finishing it will create a StaffDBDataSet.xsd file in Solution Explorer.

     

    2.  Data menu -> Show Data Sources  

    In Data Sources panel, drag&drop each data field to Form in turn in order to bind individual data.

    During this process, the StaffDBDataSet, StaffTableAdapter, StaffBindingNavigator and StaffBindingSource controls will be added automatically to Form, and five pairs of Label and TextBox controls will be added to Form. 

    3.    Set StaffBindingNavigator.BindingSource property as StaffBindingSource in Properties panel.

     

    4.    Set StaffBindingSource.DataSource property as StaffDBDataSet in Properties panel.

     

    5.    Add code in Form1.vb to Save event handle.

        Private Sub StaffBindingNavigatorSaveItem_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles SaveToolStripButton.Click  
            Me.Validate()  
            Me.StaffBindingSource.EndEdit()  
            Me.StaffTableAdapter.Update(Me.StaffDBDataSet.Staff)  
            Me.StaffDBDataSet.AcceptChanges()  
        End Sub 
     

    6.  Right-click the database file StaffDB.mdb in Solution Explorer -> change the "copy to output directory" property to "copy if newer".

     


    The above walkthrough works fine (insert/delete/update/save records) on my machine without the concurrency violation error. Please give it a try.



    Best regards,
    Martin Xie
    Wednesday, January 14, 2009 10:54 AM