.NET Framework - DB Update

Asked By Dan Paulovich on 01-Oct-09 11:38 PM
I have VB.net 2008 project with a form where data is entered
on a Data gridview. When all the data is entered I have a
routine that saves the data to a data table (Meals) and an MSAccess database.
On another form I have another data gridview (History) that is bound to the above data table.

When I do the save the access database gets updated correctly and the data gridview
shows the correct data.

Another routine deletes a group of rows depending on content, from the Meals table and database.
When I try the delete I get a concurency violation. After much searching I discovered that the
Dataset/Table Primary keys are still minus numbers from being flaged for insertion in the save
routine. The Primary keys in the Access database are correct sequential numbers, thus the concurency
violation. The only way I have discoverd to correct this problem is to do a data table fill after
every save.
This makes the Dataset/Table and access database primary keys equal.

Doing a fill after every save seems a bit much is there another way of updating the Priamry keys.

SAVE ROUTINE:

Private Sub SaveMeal()
MealsCnt = FoodDatabaseDataSet.Meals.Count
DR = Me.FoodDatabaseDataSet.Meals.NewRow()

If MealCmBx.Text = "Breakfast" Or MealCmBx.Text = "Lunch" Or MealCmBx.Text = "Dinner" Or
MealCmBx.Text = "Snack" Then
DR.Item(1) = MealCmBx.Text & "   " & DatePkr.Text
Else : DR.Item(1) = "Dinner" & "   " & DatePkr.Text : End If
Me.FoodDatabaseDataSet.Meals.Rows.InsertAt(DR, MealsCnt)

For RowNo = 0 To LastRow + 2

FoodDatabaseDataSet.Meals.Rows.Add()

For ColNo = 0 To 20

If Not DGV1(ColNo, RowNo).Value Is Nothing Then : DGV1String = DGV1(ColNo,
RowNo).Value.ToString
If DGV1String = "ND" Then : FoodDatabaseDataSet.Meals.Rows(RowNo + MealsCnt +
1).Item(ColNo + 1) = 0
Else : FoodDatabaseDataSet.Meals.Rows(RowNo + MealsCnt + 1).Item(ColNo + 1) =
DGV1.Rows(RowNo).Cells(ColNo).Value
End If
End If

If ColNo < 18 Then
If Not DGV2(ColNo + 3, RowNo).Value Is Nothing Then : DGV2String = DGV2(ColNo +
3, RowNo).Value.ToString
If DGV2String = "ND" Then : FoodDatabaseDataSet.Meals.Rows(RowNo + MealsCnt
+ 1).Item(ColNo + 23) = 0
Else : FoodDatabaseDataSet.Meals.Rows(RowNo + MealsCnt + 1).Item(ColNo + 23)
= DGV2.Rows(RowNo).Cells(ColNo + 3).Value
End If
End If
End If

Next ColNo
FoodDatabaseDataSet.Meals.Rows(RowNo + MealsCnt + 1).Item(40) = MealCmBx.Text
FoodDatabaseDataSet.Meals.Rows(RowNo + MealsCnt + 1).Item(41) = UserCmBx.Text
FoodDatabaseDataSet.Meals.Rows(RowNo + MealsCnt + 1).Item(42) = DatePkr.Text

Next RowNo

FoodDatabaseDataSet.Meals.Rows.Add()

Try
Me.Validate()
MealsBindingSource.EndEdit()
MealsTableAdapter.Update(FoodDatabaseDataSet.Meals)
Catch ex As Exception
MsgBox(ex.ToString)
End Try

Call DGVMealsHiliteCells()
Me.MealsTableAdapter.Fill(Me.FoodDatabaseDataSet.Meals)

End Sub

DELETE  ROUTINE:

Private Sub FileHist_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Handles FileHist.SelectedIndexChanged
DG3Strng = FoodDatabaseDataSet.Meals.Rows(DelRowIndex).Item(1).ToString
If InStr(DG3Strng, "Breakfast") = 1 Or InStr(DG3Strng, "Lunch") = 1 Or InStr(DG3Strng,

If FileHist.Text = "Delete Meal" Then

Do Until DG3Strng = "Percent Daily Value"
FoodDatabaseDataSet.Meals.AcceptChanges()
DG3Strng = FoodDatabaseDataSet.Meals.Rows(DelRowIndex).Item(1).ToString
FoodDatabaseDataSet.Meals.Rows(DelRowIndex).Delete()
Try
Me.Validate()
MealsBindingSource.EndEdit()
MealsTableAdapter.Update(FoodDatabaseDataSet.Meals)
Catch ex As Exception
MsgBox(ex.Message)
End Try
Loop




Scott M. replied on 02-Oct-09 10:23 AM
You can avoid this by ensuring that your Delete Command is deleting based on
the primary key that came from the database and not the DataSet row id.  Can
we see your DeleteCommand.CommandText?

-Scott
Dan Paulovich replied on 02-Oct-09 05:39 PM
DELETE COMMAND:
DELETE FROM Meals
WHERE          (ID = ?) AND (? = 1 AND Shrt_Desc IS NULL OR
Shrt_Desc = ?) AND (? = 1 AND Qty IS NULL OR
Qty = ?) AND (? = 1 AND Units IS NULL OR
Units = ?) AND (? = 1 AND Water IS NULL OR
Water = ?) AND (? = 1 AND Energ_Kcal IS NULL OR
Energ_Kcal = ?) AND (? = 1 AND Lipid_Tot IS NULL OR
Lipid_Tot = ?) AND (? = 1 AND FA_SAt IS NULL OR
FA_SAt = ?) AND (? = 1 AND FA_Poly IS NULL OR
FA_Poly = ?) AND (? = 1 AND FA_Mono IS NULL OR
FA_Mono = ?) AND (? = 1 AND Cholestrl IS NULL OR
Cholestrl = ?) AND (? = 1 AND Carbohydrt IS NULL OR
Carbohydrt = ?) AND (? = 1 AND Fiber_TD IS NULL OR
Fiber_TD = ?) AND (? = 1 AND Sugar_Tot IS NULL OR
Sugar_Tot = ?) AND (? = 1 AND Protein IS NULL OR
Protein = ?) AND (? = 1 AND Sodium IS NULL OR
Sodium = ?) AND (? = 1 AND Vit_A_IU IS NULL OR
Vit_A_IU = ?) AND (? = 1 AND Vit_B6 IS NULL OR
Vit_B6 = ?) AND (? = 1 AND Vit_B12 IS NULL OR
Vit_B12 = ?) AND (? = 1 AND Vit_C IS NULL OR
Vit_C = ?) AND (? = 1 AND Vit_E IS NULL OR
Vit_E = ?) AND (? = 1 AND Vit_K IS NULL OR
Vit_K = ?) AND (? = 1 AND Alpha_Carot IS NULL OR
Alpha_Carot = ?) AND (? = 1 AND Beta_Carot IS NULL OR
Beta_Carot = ?) AND (? = 1 AND Folate_Tot IS NULL OR
Folate_Tot = ?) AND (? = 1 AND Folic_Acid IS NULL OR
Folic_Acid = ?) AND (? = 1 AND Lycopene IS NULL OR
Lycopene = ?) AND (? = 1 AND Niacin IS NULL OR
Niacin = ?) AND (? = 1 AND Retinol IS NULL OR
Retinol = ?) AND (? = 1 AND Ribolfavin IS NULL OR
Ribolfavin = ?) AND (? = 1 AND Thiamin IS NULL OR
Thiamin = ?) AND (? = 1 AND Calcium IS NULL OR
Calcium = ?) AND (? = 1 AND Copper IS NULL OR
Copper = ?) AND (? = 1 AND Iron IS NULL OR
Iron = ?) AND (? = 1 AND Manganese IS NULL OR
Manganese = ?) AND (? = 1 AND Magnesium IS NULL OR
Magnesium = ?) AND (? = 1 AND Phosphorus IS NULL OR
Phosphorus = ?) AND (? = 1 AND Potassium IS NULL OR
Potassium = ?) AND (? = 1 AND Selenium IS NULL OR
Selenium = ?) AND (? = 1 AND Zinc IS NULL OR
Zinc = ?) AND (? = 1 AND Meal IS NULL OR
Meal = ?) AND (? = 1 AND [User] IS NULL OR
[User] = ?) AND (? = 1 AND [Date] IS NULL OR
[Date] = ?)







You can avoid this by ensuring that your Delete Command is deleting based on
the primary key that came from the database and not the DataSet row id.  Can
we see your DeleteCommand.CommandText?

-Scott
Scott M. replied on 03-Oct-09 06:49 AM
Dan Paulovich replied on 03-Oct-09 12:26 PM
Thanks for the Info

How do I make sure the PK is coming from the DB
Scott M. replied on 03-Oct-09 03:14 PM
Make sure that your initial SELECT query is pulling the primary key down
from the database and is populated in the DataTable along with the other
info.  You do not need to show the PK brought down, but you can use it as the
value to check against during your updates.

-Scott
Dan Paulovich replied on 03-Oct-09 04:42 PM
This is the Select query, is this correct?

SELECT
ID, Shrt_Desc, Qty, Units, Water, Energ_Kcal, Lipid_Tot, FA_SAt, FA_Poly, FA_Mono, Cholestrl,
Carbohydrt, Fiber_TD, Sugar_Tot, Protein, Sodium, Vit_A_IU, Vit_B6, Vit_B12, Vit_C, Vit_E, Vit_K,
Alpha_Carot, Beta_Carot, Folate_Tot, Folic_Acid, Lycopene, Niacin, Retinol, Ribolfavin, Thiamin,
Calcium, Copper, Iron, Manganese, Magnesium, Phosphorus, Potassium, Selenium, Zinc, Meal, [User],
[Date]

FROM              Meals

Make sure that your initial SELECT query is pulling the primary key down
from the database and is populated in the DataTable along with the other
info.  You do not need to show the PK brought down, but you can use it as the
value to check against during your updates.

-Scott
Patrice replied on 03-Oct-09 05:21 PM
See http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx that explains in
details how to update client side generated primary keys with those used for
the newly inserted rows (scroll down for the Access part).

--
Patrice
Scott M. replied on 03-Oct-09 10:02 PM
So, you are delete would have one parameter, which would be the ID field
stored along with the current record in the DataSet but NOT the DataSet row
number.

-Scott
Dan Paulovich replied on 04-Oct-09 11:27 AM
The ID field is the primary key
Yes I changed my Delete to include only the ID field



So, you are delete would have one parameter, which would be the ID field
stored along with the current record in the DataSet but NOT the DataSet row
number.

-Scott
Dan Paulovich replied on 04-Oct-09 11:45 AM
Thanks Patrice

I read the article and it appears that my doing a Fill at the end of my
Update is a simpler way of updating the Primary Key in the program.

Do you see any problem with this method?

See http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx that explains in
details how to update client side generated primary keys with those used for
the newly inserted rows (scroll down for the Access part).

--
Patrice
Patrice replied on 04-Oct-09 01:09 PM
As always this is a tradeoff. Here the key factor is likely the number of
rows. If you do not have much rows then reading them is likely simpler. If
you have more rows then you may want to switch to reading only those that
were inserted (or possibly updated if an update triggers some server side
change).

--
Patrice