I am learning VB and am working on a tutorial on databases. The recordset is a disconnected set. I am getting a concurrency exception. I might have 3 records. I delete all 3. Then I add a record, it adds it. Then I attempt to delete it and it fails. Sometimes it fails on the add/update proc as well.

Always, always, always it blows up on the following line
da.Update(ds, "AddressBook") in both the commit and delete procs.

Any idea what I am doing wrong? I'm providing the delete and commit code as well as a couple of others that may provide a clue to you.

Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim sql As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim inc As Integer
    Dim MaxRows As Integer
Private Sub NavigateRecords()

        txtFirst.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
        txtLast.Text = ds.Tables("AddressBook").Rows(inc).Item(2)

End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AddressBook.mdb"
        con.Open()

        sql = "SELECT * FROM tblContacts"

        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")

        con.Close()

        MaxRows = ds.Tables("AddressBook").Rows.Count

        If MaxRows > 0 Then
            inc = 0
            Call NavigateRecords()
        Else
            MsgBox("There are no records yet")
            inc = -1
        End If

    End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        If MaxRows > 0 Then
            MsgBox("The delete row is " & inc & " and Maxrows is " & MaxRows)
            ds.Tables("AddressBook").Rows(inc).Delete()
            MaxRows = MaxRows - 1
            da.Update(ds, "AddressBook")

            If MaxRows > 0 Then
                inc = 0
                NavigateRecords()
            Else
                txtFirst.Clear()
                txtLast.Clear()
                inc = -1
            End If
        Else
            MsgBox("There are no records to delete.")
        End If

    End Sub
    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If txtFirst.Text <> "" And txtLast.Text <> "" Then

            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("AddressBook").NewRow()

            dsNewRow.Item("FirstName") = txtFirst.Text
            dsNewRow.Item("Surname") = txtLast.Text

            ds.Tables("AddressBook").Rows.Add(dsNewRow)

            da.Update(ds, "AddressBook")

            MsgBox("New Record added to the Database")

            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True

            MaxRows = MaxRows + 1
            inc = MaxRows - 1
            'added following
            NavigateRecords()
        Else
            MsgBox("Please enter some data in both the first or last name fields.")
        End If
    End Sub
End Class

After each da.Update you should add a ds.AcceptChanges.
This commits all changes made to the dataset since the last time a change was made.

Thanks for the reply. I tried your changes but I still got the error. This was just practice stuff. I probably need a book to look at some examples of disconnected record sets.

Thanks. Yes, in fact that was the tutorial I was using. It works fine as long as records exist in the table. Deleting all records then adding/deleting (and repeating) a first record after deleting them all is where/when I was getting my errors.

after the da.update

just clear the dataset and refill it

ds.clear()
da.fill(ds,"table")

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.