concurrency violation issue on updating rows

T_MemTypeAdvanceBooking table has these columns:

CentreID                Int               PrimaryKey
MembershipType          Uniqueindentifier PrimaryKey
FacilityName            UniqueIdentifier  PrimaryKey
ActivityName            NvarChar(35)      PrimaryKey
NumOfAdvanceDaysBooking smallint

I know might sounds silly for having these primary key, but in my application when I fill all the keys and i do an insert, it is ok! but when i try to update a row with a different value of NumOfAdvancedDaysBooking, it throws the exception of ConcurrencyViolation, how is that so? what is the fix for this in my case? Here iste Update Command:

Me.SqlUpdateCommand1.CommandText = "UPDATE [T_MemTypeAdvanceBooking] SET [CentreId" & _
    "] = @CentreId, [Membershi开发者_如何学CpTypeId] = @MembershipTypeId, [MAIN FACILITY USED] = @M" & _
    "AIN_FACILITY_USED, [ActivityType] = @ActivityType, [NumOfAdvanceDays] = @NumOfAd" & _
    "vanceDays WHERE (([CentreId] = @Original_CentreId) AND ([MembershipTypeId] = @Or" & _
    "iginal_MembershipTypeId) AND" & _
    "([ActivityType] = @Original_ActivityType) AND ([RowVer] = @Original_RowVe" & _
    "r));"

Remove RowVer condition:

Me.SqlUpdateCommand1.CommandText = "UPDATE [T_MemTypeAdvanceBooking] SET [CentreId" & _
    "] = @CentreId, [MembershipTypeId] = @MembershipTypeId, [MAIN FACILITY USED] = @M" & _
    "AIN_FACILITY_USED, [ActivityType] = @ActivityType, [NumOfAdvanceDays] = @NumOfAd" & _
    "vanceDays WHERE (([CentreId] = @Original_CentreId) AND ([MembershipTypeId] = @Or" & _
    "iginal_MembershipTypeId) AND" & _
    "([ActivityType] = @Original_ActivityType));"

Explanation:

When you update DataRow in DataTable you actually get 2 rows. One with DataRowVersion Current and another with Original. Most of the time you work with Current version of DataRow. So, when you send an updated row (via DataAdapter usually) RowVersion is not equal to Original, but Current. What then happens is that the condition is false and no record in DB is updated. DataAdapter get information that no record is updated and think that someone updated the record before you and raises concurrency exception.

I suggest that you add on your tables column Version of type Integer. When inserting you set Version to 1. On every subsequent update you increment the Version by 1. This way you can check for optimistic concurrency by just comparing fetched Version value (@Original_Version) with actual Version value ([Version]) when issuing DB UPDATE statement.

Me.SqlUpdateCommand1.CommandText = "UPDATE [T_MemTypeAdvanceBooking] SET [CentreId" & _
    "] = @CentreId, [MembershipTypeId] = @MembershipTypeId, [MAIN FACILITY USED] = @M" & _
     "AIN_FACILITY_USED, [ActivityType] = @ActivityType, [NumOfAdvanceDays] = @NumOfAd" & _
    "vanceDays WHERE (([CentreId] = @Original_CentreId) AND ([MembershipTypeId] = @Or" & _
    "iginal_MembershipTypeId) AND" & _
    "([ActivityType] = @Original_ActivityType) AND  ([Version] = @Original_Version));"