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