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)
sub #2 (clears table and copies data from another table)
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()