I have the following error message and I can't see where in code it could of
came from or what could of caused it. It happened randomly for one of our
users and no one else ever, and I am unable to reproduce it at all.
System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand
affected 0 records.
Now this was caused during the update method of a data adapter that is tied
to a table alled Categories... On the form where the data entry takes place
we have a brand new record called coverage, This is stored in the dataset's
coverage table. At this point this is in thet table as a new record with a
uniquely generated ID Primary key that gets changed when the dataset is
updated to the database (that part works fine, SQL Server returns the new ID
and the local dataset updates approperiatly to match)... the problem though
seems to of occured on the referenced table Categories... This table has
records that are dynamically generated based on what a user checkes in a
check listbox. So if the user unchecks an item, the corresponding record is
removed from the datatable in teh categories table, if they check one a
record gets added... here is an example piece of code...
Private Sub chklstCategories_ItemCheck(ByVal sender As Object, ByVal e As
System.Windows.Forms.ItemCheckEventArgs) Handles chklstCategories.ItemCheck
' update dataset in memory to reflect changes
If Me.b_refillingData = False Then
Select Case e.NewValue
Case CheckState.Checked
Dim dr_catItem As DataRow =
ds_formData.Tables("CoverageJunCategories").NewRow
If Me.fs_FormViewState = bdb.Business.FormStates.AddNew Then
'dr_CoverageRow
dr_catItem("coverageid") = Convert.ToInt32(dr_CoverageRow("CoverageID"))
Else
dr_catItem("coverageid") =
Convert.ToInt32(Me.lstvCoverages.SelectedItems(0).Tag)
End If
dr_catItem("categoryid") = DirectCast(Me.chklstCategories.Items(e.Index),
CategoryItem).ID
ds_formData.Tables("CoverageJunCategories").Rows.Add(dr_catItem)
Debug.WriteLine(ds_formData.Tables("CoverageJunCategories").Rows.Count)
Case CheckState.Unchecked
Dim rowKeys(1) As Object
If Me.fs_FormViewState = bdb.Business.FormStates.AddNew Then
rowKeys(0) = Convert.ToInt32(dr_CoverageRow("CoverageID"))
Else
rowKeys(0) = Convert.ToInt32(Me.lstvCoverages.SelectedItems(0).Tag)
End If
rowKeys(1) = DirectCast(Me.chklstCategories.Items(e.Index), CategoryItem).ID
Dim dr_catitem As DataRow =
ds_formData.Tables("CoverageJunCategories").Rows.Find(rowKeys)
dr_catitem.Delete()
Debug.WriteLine(ds_formData.Tables("CoverageJunCategories").Rows.Count)
End Select
End If
End Sub
as you can see this is a junction table which joins the list of categories
and the list of coverages together.. In our dataset we also have the
coverages and this table referenced like this
ds_formData.Relations.Add("CoveragesJunCategories",
ds_formData.Tables("Coverages").Columns("CoverageID"),
ds_formData.Tables("CoverageJunCategories").Columns("CoverageID"))
to me this code all seems to be correct, but I can't figure out what is
wrong... prior to this we were getting a "Missing update command" error for
the data adapter that handles the coveragesjuncategories table's... I
figured since all I was doing was adding and removing rows I only would need
a Select, Insert, and Delete command... Since it seemed like no real
updateing was occuring (unless when coverages gets its new primary key from
the server when a coverage row is created, it changes the category
junction's table also since they are referenced together with a
primary/foreign key, but if this is a update how would the row be created to
start with? just a little confused on the procedure for a new row in one
table that references a new row in another when the primary key gets updates
on the parent, what happens to the row the foreign key is on is it still an
insert or is it now an update (i assumed insert because the row does not
exist yet in the database only locally in the data table since the update
method as not been called yet on the categorys junction table (child table)
but only on the coverages one (parent table))... anyone that can help me out
would be great! thanks!