OleDB Update Problem

dabeastro

Member
Joined
Jul 1, 2010
Messages
5
Programming Experience
Beginner
I am stuck with something that must be right in front of me but I have spent so many hours working on it that I must be missing it. I will try and simplify the scenario.

I have a table that is updated by the user clicking buttons that are a part of a datagridview. Each button click assigns either a 0 or a 1 to the table through a sql update command. I can verify that everything is working properly by viewing the database. This (sub #1) works great.

My problem arises when I run another bit of code (sub #2) that overwrites the table with a previously saved table. This bit of code also works and I can verify that the working table content has been replaced by the other table's content. However, after doing this, sub #1 no longer functions and after a run or two, gives, "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." I understand what the error suggests, I just can not figure out where the bug is. I suspect sub #2. This has me totally stumped. Perhaps someone else will see my error right away?

sub #1 (assume that y=2 and x=1, I have verified collection of values always works)

VB.NET:
        dbSource = "Data Source = " & path2 & "\userdata.mdb"

        con.ConnectionString = dbProvider & dbSource

        sql = "SELECT * FROM userdata"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "userdata")

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        'determine which button was clicked

        x = dgv4.Rows(e.RowIndex).Index
        y = dgv4.Columns(e.ColumnIndex).Index

        'assign values and color buttons

        If y = 2 Then

            dgv4.Item(y, x).Style.BackColor = Color.Green
            dgv4.Item(y + 1, x).Style.BackColor = Color.LightGray
            dgv4.ClearSelection()

            ds.Tables("userdata").Rows(x).Item(44) = 1

        End If

        da.Update(ds, "userdata")
        ds.Dispose()

sub #2 (clears table and copies data from another table)

VB.NET:
        Dim con1 As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source= " & path2 & "\userdata.mdb"

        Dim AccessConnection As New System.Data.OleDb.OleDbConnection(con1)

        'delete current userdata and add from scenario

        Dim dt2 As New DataTable()
        Dim sql As String = "DELETE * FROM userdata"
        Dim da As New OleDb.OleDbDataAdapter(sql, con1)
        Dim CommandBuilder As New OleDb.OleDbCommandBuilder(da)
        da.Fill(dt2)
        da.Update(dt2)

        AccessConnection.Open()

        Dim AccessCommand2 = New System.Data.OleDb.OleDbCommand("INSERT INTO userdata SELECT * FROM " & txtname.Text, AccessConnection)

        AccessCommand2.ExecuteNonQuery()

        AccessConnection.Close()

        Me.Dispose()
 

dabeastro

Member
Joined
Jul 1, 2010
Messages
5
Programming Experience
Beginner
Nevermind. 10 hours and looking and 1 minute after my post I fixed it. In sub #1, I should have not be using ds.dispose() at the finish. Changed to ds.clear() and error goes away. This must be the result of sub #1 residing in a form that never disposes? The concurrency error really sucks. It signifies too many things at once. I ran around in circles and all I really needed to do was not dispose the dataset? Damn! Thanks for looking.
 
Top Bottom