Issues deleting a newly created record in the datatable/database

[Hoogie]

When I create a new record and try deleting it right after, I get an "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records." I've been messing around with it for some time now and cannot figure out why. If I add a new record, close my program, re-open and try and delete it, it works fine.

Dim ds As New DataSet
Dim daTodo As New OleDbDataAdapter

To load my data into my listview:

Private Sub LoadData()
        Dim dt As DataTable = ds.Tables("Todo")
        Dim datDate As Date
        Dim intIndex As Integer
        Dim intLastRow As Integer
        Dim intAdded As Integer
        Dim datBoldDate As Date


        blnLoaded = False


        mcDate.RemoveAllBoldedDates()


        datDate = lblDate.Text
        intLastRow = dt.Rows.Count


        intIndex = 0
        intAdded = -1


        lstTasks.Items.Clear()


        Do While intIndex < intLastRow
            If dt.Rows(intIndex).Item("dateof") = datDate Then
                If dt.Rows(intIndex).Item("checked") = True Then
                    If chkHideComplete.Checked = True Then
                    Else
                        intAdded = intAdded + 1
                        lstTasks.Items.Add(dt.Rows(intIndex).Item("checked"))
                        lstTasks.Items(intAdded).SubItems.Add(dt.Rows(intIndex).Item("task"))
                        lstTasks.Items(intAdded).SubItems.Add(dt.Rows(intIndex).Item("priority"))
                        lstTasks.Items(intAdded).Checked = True
                    End If
                Else
                    intAdded = intAdded + 1
                    lstTasks.Items.Add(dt.Rows(intIndex).Item("checked"))
                    lstTasks.Items(intAdded).SubItems.Add(dt.Rows(intIndex).Item("task"))
                    lstTasks.Items(intAdded).SubItems.Add(dt.Rows(intIndex).Item("priority"))
                    lstTasks.Items(intAdded).Checked = False
                End If
            End If
            datBoldDate = dt.Rows(intIndex).Item("dateof")
            mcDate.AddBoldedDate(datBoldDate)


            intIndex = intIndex + 1
        Loop






        mcDate.UpdateBoldedDates()


        blnLoaded = True


    End Sub

Save my new data:

Private Sub SaveData()
        Dim newRow As DataRow = ds.Tables("Todo").NewRow()
        Dim dt As DataTable = ds.Tables("Todo")
        Dim intIndex As Integer
        Dim blnAddNew As Boolean


        blnAddNew = True


        intIndex = 0


        Do While intIndex < dt.Rows.Count
            If txtTask.Text = dt.Rows(intIndex).Item("task") And lblDate.Text = dt.Rows(intIndex).Item("dateof") Then
                If fraAddTask.Text = "Add New Task" Then
                    MsgBox("Task name already exists!", MsgBoxStyle.Critical, "ToDo")
                    txtTask.Clear()
                    txtTask.Focus()
                    GoTo SameTaskName
                Else
                    dt.Rows(intIndex).Item("details") = txtDetails.Text
                    dt.Rows(intIndex).Item("priority") = cboPriority.SelectedItem.ToString
                    blnAddNew = False
                    Exit Do
                End If


            End If
            intIndex = intIndex + 1
            blnAddNew = True
        Loop


        If blnAddNew = True Then
            newRow("checked") = False
            newRow("task") = txtTask.Text
            newRow("details") = txtDetails.Text
            newRow("dateof") = lblDate.Text
            newRow("priority") = cboPriority.SelectedItem.ToString


            ds.Tables("Todo").Rows.Add(newRow)




        End If


        daTodo.Update(dt)
        fraAddTask.Visible = False
        txtTask.Clear()
        txtDetails.Clear()
        cboPriority.SelectedIndex = -1
        mcDate.Enabled = True


        Exit Sub


SameTaskName:


    End Sub

Remove current item selected in the listview:

Private Sub mnuRemove_Click(sender As Object, e As EventArgs) Handles mnuRemove.Click
        Dim dt As DataTable = ds.Tables("Todo")
        Dim intRow As Integer


        intRow = dt.Rows.IndexOf(dt.Select("task = '" & strTask & "'" & " AND dateof = '" & lblDate.Text & "'").FirstOrDefault)


        If MsgBox("Delete task '" & dt.Rows(intRow).Item("task") & "'?", MsgBoxStyle.YesNo, " ToDo") = MsgBoxResult.Yes Then
            dt.Rows(intRow).Delete()
            daTodo.Update(dt)  <--- [COLOR=#ff0000]ERROR OCCURS HERE![/COLOR]
            LoadData()
        Else
        End If


    End Sub

 

[Hoogie]

I even added the check:

If ds.HasChanges(DataRowState.Deleted) Then
       daTodo.Update(dt)
End If

It obviously knows a a row has been deleted as it still runs the code, but there error still appears while trying to Update. I'm at a loss here. I don't understand how it's saying "Hey, a record was deleting we need to update the database" and then when it tries to delete the record in the database that has been untouched since it was initially added, "Nope, lols. "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."". If nothing else was changed in the database after it was added, why the heck won't it delete the row in the database? Ugh.

 

[jmcilhinney]

Is the ID for the record being auto-generated by the database? If so, are you getting that ID from the database when you insert the record so that you can use it to identify the record when you then delete it? If not then that's your issue.

 

[Hoogie]

It is being auto-generated, and you are correct that I am not getting the ID from the database. I assumed finding the row which contains the information I need (minus the ID) and then deleting that row that way would do it. Why exactly is that needed jmcilhinney? Just curious. Does it require the primary key in order to delete it? Or is it because it does not know what the ID is while creating the record, and then when trying to match it its like "woah whats this number?!"

Either way, thanks jmcilhinney, I changed the primary key and then deleted the auto-generated number and it works flawlessly. Thanks man, I appreciate it.

 

[jmcilhinney]

Each DataRow contains two versions of the data: original and current. When you save changes from a DataRow to the database, the original version is compared to what's in the database. If they match then the current version is saved, which may mean updated or deleted. If they do not match then the only valid assumption is that someone else has changed the database since you retrieved the data so a concurrency violation occurs to prevent you wiping out someone else's changes without even knowing they were there.

The proper way to handle your situation is to retrieve the auto-generated ID from the database as you save the new records and there's a proper way to do that. I'm guessing that you're using Access but I think that this should work with any OLE DB provider:

Retrieve Access AutoNumber Value After Insert