Question Cuncurrency Violation . Delete command

[bruno_pascal]

Hi everybody

I was doing an appointment book in my visual basic 2008 , registering fields like ID, Name, Address, Phone(Tel), Cellular(Cel), Email and general observations(Obs) into mysql database .

The problem is : when I add a contact and right after I try to delete it , it appears an error which is : Cuncurrency Violation . Delete command affected 0 of 1 expected registers.

Anyone could help me please, giving any suggestions about what should I have do to solve this problem?? If yes,
in which part of the code I have to change??


Thanks in advance to all

The code is :

Imports MySql.Data.MySqlClient

Public Class frmCadaster
    Inherits System.Windows.Forms.Form

    Private oDataAdapter As MySqlDataAdapter
    Private oDataset As DataSet
    Private iRowPos As Integer
    Public marker As Integer



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

        Dim oConnection As MySqlConnection
        oConnection= New MySqlConnection
        oConnection.ConnectionString = "Server=localhost;Database=cadaster;uid =root;pwd=;"

        Me.oDataAdapter = New MySqlDataAdapter("Select * from contacts", oConnection)
        Dim oCommBuild As MySqlCommandBuilder = New MySqlCommandBuilder(oDataAdapter)
        Me.oDataset = New DataSet
        oConnection.Open()
        Me.oDataAdapter.Fill(oDataset, "contacts")
        oConnection.Close()

        Me.iRowPos = 0
        Me.marker = 0

        Me.LoadData()
        Me.Textboxs(True)


    End Sub

    Private Sub LoadData()

        Dim oDataRow As DataRow
        oDataRow = Me.oDataset.Tables("contacts").Rows(Me.iRowPos)

        Try

            Me.txtID.Text = oDataRow("ID").ToString
            Me.txtName.Text = oDataRow("Name").ToString
            Me.txtAddress.Text = oDataRow("Address").ToString
            Me.txtTel.Text = oDataRow("Tel").ToString
            Me.txtCel.Text = oDataRow("Cel").ToString
            Me.txtEmail.Text = oDataRow("Email").ToString
            Me.txtObs.Text = oDataRow("Obs").ToString

        Catch ex As Exception
            MessageBox.Show("Error ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Me.lblRegister.Text = "Register:  " & _
        Me.iRowPos + 1 & " of " & _
        Me.oDataset.Tables("contacts").Rows.Count

    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Dispose()
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

        If Me.iRowPos = (Me.oDataset.Tables("contacts").Rows.Count - 1) Then
            Beep()
            Return

        Else
            Me.iRowPos += 1
            Me.LoadData()

        End If
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

        If Me.iRowPos = 0 Then
            Beep()
            Return
        Else
            Me.iRowPos -= 1
            Me.LoadData()
        End If
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

        Me.iRowPos = 0
        Me.LoadData()
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

        Me.iRowPos= (Me.oDataset.Tables("contacts").Rows.Count - 1)
        Me.LoadData()
    End Sub

    Private Sub Clear()

        txtName.Clear()
        txtAddress.Clear()
        txtTel.Clear()
        txtCel.Clear()
        txtEmail.Clear()
        txtObs.Clear()
        txtName.Focus()
    End Sub

    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        Me.marker = 1
        Clear()
        Me.Textboxs(False)
        Me.Navigation(False)
    End Sub

  Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Me.marker = 2
        Me.Textboxs(False)
        Me.Navigation(False)

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        If marker = 0 Then
            Dim answer As DialogResult
            answer = MessageBox.Show("Are you sure to delete this contact?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

            If answer = Windows.Forms.DialogResult.Yes Then

                Dim oDataRow As DataRow

                oDataRow = Me.oDataset.Tables("contacts").Rows(Me.iRowPos)
                oDataRow.Delete()

                Dim oDeletedTable As DataTable
                oDeletedTable = Me.oDataset.Tables("contacts").GetChanges(DataRowState.Deleted)


                Me.oDataAdapter.Update(oDeletedTable)                      '//------error appears at this line
                Me.oDataset.Tables("contacts").AcceptChanges()

                MessageBox.Show("Contact deleted successfully!", "Exclusion", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Me.marker = 0
                btnPrevious.PerformClick()


            Else

                Return
            End If
        End If

    End Sub


    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If marker = 1 Then
            If txtName.Text <> "" Then
                Dim oDataRow As DataRow

                oDataRow = Me.oDataset.Tables("contacts").NewRow()

                oDataRow("ID") = Me.txtID.Text
                oDataRow("Name") = Me.txtName.Text
                oDataRow("Address") = Me.txtAdress.Text
                oDataRow("Tel") = Me.txtTel.Text.ToString
                oDataRow("Cel") = Me.txtCel.Text
                oDataRow("Email") = Me.txtEmail.Text
                oDataRow("Obs") = Me.txtObs.Text

                Me.oDataset.Tables("contacts").Rows.Add(oDataRow)
                Me.oDataAdapter.Update(Me.oDataset, "contacts")

                MessageBox.Show("Contact saved!", "Added contact", MessageBoxButtons.OK, MessageBoxIcon.Information)



                Me.Textboxs(True)
                Me.marker = 0
                Me.Navigation(True)
                Me.btnLast.PerformClick()
                LoadData()



            Else
                MessageBox.Show("Enter at least a name to save a contact!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            End If

        ElseIf marker = 2 Then

            Dim oDataRow As DataRow
            oDataRow = Me.oDataset.Tables("contacts").Rows(Me.iRowPos)

            oDataRow("ID") = Me.txtID.Text
            oDataRow("Name") = Me.txtName.Text
            oDataRow("Address") = Me.txtAddress.Text
            oDataRow("Tel") = Me.txtTel.Text
            oDataRow("Cel") = Me.txtCel.Text
            oDataRow("Email") = Me.txtEmail.Text
            oDataRow("Obs") = Me.txtObs.Text

            Me.oDataAdapter.Update(Me.oDataset, "contacts")

            MessageBox.Show("Edited contact ", "Edition", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.Textboxs(True)
            Me.Navigation(True)
            Me.marker = 0

        End If

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        LoadData()
        Textboxs(True)
        Me.Navigation(True)
        Me.marker = 0
    End Sub

    Private Sub Textboxs(ByVal value As Boolean)
        txtName.ReadOnly = value
        txtAddress.ReadOnly = value
        txtTel.ReadOnly = value
        txtCel.ReadOnly = value
        txtEmail.ReadOnly = value
        txtObs.ReadOnly = value
    End Sub

    Private Sub Navigation(ByVal value As Boolean)

        btnFirst.Enabled = value
        btnPrevious.Enabled = value
        btnNext.Enabled = value
        btnLast.Enabled = value

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        frmSearch.Show()
    End Sub
End Class

 

[jmcilhinney]

Firstly, code is not a quote so don't use quote tags when posting code. I have fixed the tags so the code is now comfortably readable. The problem now is that there's so much of it that we have to wade through all the irrelevant stuff to find what matters. I'll come back when I have time to do so.

 

[bruno_pascal]

Thank you for adapting the code in better visual terms. I'm new at this forum and I'm learning how tools work here . Sorry,for sending code in the wrong way. Concerning the code size , I have sent so much of it just to elucidate how I am doing the program.

 

[jmcilhinney]

You're making your life harder by not using data-binding. With data-binding, all that code could be condensed to not much more than this:

Private ReadOnly connection As New SqlConnection("connection string here")
Private ReadOnly adapter As New SqlDataAdapter("SELECT * FROM SomeTable", connection)
Private ReadOnly builder As New SqlCommandBuilder(Me.adapter)
Private ReadOnly table As New DataTable

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    Me.adapter.Fill(table)

    Me.BindingSource1.DataSource = Me.table
    Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "Column1")
    Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "Column1")
End Sub

Private Sub nextButton_Click(sender As Object, e As EventArgs) Handles nextButton.Click
    Me.BindingSource1.MoveNext()
End Sub

Private Sub previousButton_Click(sender As Object, e As EventArgs) Handles previousButton.Click
    Me.BindingSource1.MovePrevious()
End Sub

Private Sub addButton_Click(sender As Object, e As EventArgs) Handles addButton.Click
    Me.BindingSource1.AddNew()
End Sub

Private Sub deleteButton_Click(sender As Object, e As EventArgs) Handles deleteButton.Click
    Me.BindingSource1.RemoveCurrent()
End Sub

Private Sub cancelActionButton_Click(sender As Object, e As EventArgs) Handles cancelActionButton.Click
    Me.BindingSource1.CancelEdit()
End Sub

Private Sub saveButton_Click(sender As Object, e As EventArgs) Handles saveButton.Click
    Me.BindingSource1.EndEdit()
    Me.adapter.Update(Me.table)
End Sub

 

[bruno_pascal]

Thank you very much for trying to help me and changing the code. I will copy it to put into practice later. Indeed, this way is much faster and simple but I already have a project similar(not equal) to this using data binding . When I decided to do in this way I've showed you, my intention was to learn in a disconnected way( not in data-binding) cause I think it's cool to have in mind another alternatives too. You know, for me it's very comfortable to go there and then just get data binding . So that's why I have put that code here. That error appears when I just add a contact and then I delete the same contact and I don't know how to solve it...Remaining parts of it are working fine . I would like to seize my code too.

I reiterate that your idea is very welcome and I will use it later in my project. I would like to thank you for giving this suggestion and rewriting the code in a different way. But if you could seize my code and give a solution to it I will be very grateful too.

Thanks ever so much !

 

[jmcilhinney]

A concurrency violation occurs when you try to modify a record in the database where the fields of that record do not match the original values in the DataRow you're saving. I suggest that you debug your code and check all the field values in your DataRow and database record at the time you're trying to delete.

 

[bruno_pascal]

Sorry but I don't have experience on that to do this procedure. I am starting to learn mysql database and visual basic. I'm not expert at this. I just shared this code here to see if someone with more experience could help me with the code I've posted or if possible could run it in machine to see the error. I thought it could be something in my code. I hadn't seen an error like that yet. It's the first time.

I have put a red point at the left margin of the code and execute the program .Then I add a contact . When I go to database, that contact is there. But when I try to remove the same contact I have just added , the error appears , and in the database the contact isn't removed.

I don't know what is happening ...I am confusing . Sorry.

 

[bruno_pascal]

I've tryed to use the code you suggested but I can't remove contacts from database using the delete button command. The contact is just removed from de form but when I go to the database nothing occurs. The contacts I thought it were removed are still there in database.

Then I've added the following line bellow the code :

Private Sub deleteButton_Click(sender As Object, e As EventArgs) Handles deleteButton.Click
    
Me.BindingSource1.RemoveCurrent()
    Me.adapter.Update(Me.table)   '// Line added

End Sub

But when I add a contact and then try to remove it, error Violation Concurrency appears again and the contact isn't removed.