Question Add, Update, Delete, Prev, Next - program crash

[Coisox]

I try a simple MS Access database apps.

Each button (or function) works perfectly by themselves. Which means, if I click the ADD button and exit the application, then open the database through MS Access, the new record is perfectly added.

But if I click Add, then immediately click the Delete button, the application crash. If I click the Add, then exit application, then run the application again, then click the Delete button, works perfectly.

Here's my code:
Or you can download the project here.

Public Class Form1
    Public cnADONetConnection As New OleDb.OleDbConnection()
    Public dataAdapter As OleDb.OleDbDataAdapter
    Public commandBuilder As OleDb.OleDbCommandBuilder
    Public dataTable As New DataTable

    Dim m_rowPosition As Integer = 0

    Private Sub ShowCurrentRecord()
        If dataTable.Rows.Count = 0 Then
            TextBox1.Text = ""
            Exit Sub
        End If

        TextBox1.Text = dataTable.Rows(m_rowPosition)("nama").ToString()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cnADONetConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb"
        cnADONetConnection.Open()
        dataAdapter = New OleDb.OleDbDataAdapter("Select * From Table1", cnADONetConnection)
        commandBuilder = New OleDb.OleDbCommandBuilder(dataAdapter)
        dataAdapter.Fill(dataTable)

        ShowCurrentRecord()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If m_rowPosition > 0 Then
            m_rowPosition = m_rowPosition - 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If m_rowPosition < (dataTable.Rows.Count - 1) Then
            m_rowPosition = m_rowPosition + 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If dataTable.Rows.Count <> 0 Then
            dataTable.Rows(m_rowPosition)("nama") = TextBox1.Text
            dataAdapter.Update(dataTable)
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim drNewRow As DataRow = dataTable.NewRow()
        drNewRow("nama") = TextBox1.Text
        dataTable.Rows.Add(drNewRow)
        dataAdapter.Update(dataTable)
        m_rowPosition = dataTable.Rows.Count - 1
        Me.ShowCurrentRecord()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        If dataTable.Rows.Count <> 0 Then
            dataTable.Rows(m_rowPosition).Delete()
            dataAdapter.Update(dataTable)
            m_rowPosition = 0
            Me.ShowCurrentRecord()
        End If
    End Sub
End Class

 

[jmcilhinney]

If the application crashes then you'd be given an error message. Please give it to us.

 

[avile22]

Its the famous concurrency violation. This drove me nuts awhile back and I'll be back from work by noon to dig up my old code.

I believe its something to do with the Primary Key in MS Access

 

[Coisox]

Here's the error message.

Currently, what I do to solve this problem is:

dataAdapter.Update(dataTable)
dataTable.Clear()
redo the SQL query
redo commandBuilder
dataAdapter.Fill(dataTable)

 

[cjard]

For info on how you ought to be doing your data access, read the DW2 link in my signature (and keep an eye out for pages that are .net 3.5 enhanced)

Start with the section "Creating a Simple Data App"