I am getting a concurrency violation on my updatecommand when I try to save a datatable back to the database. I have spent hours trying to find a solution but it escapes me. I am changing one column in about 90% of my rows in a 4200 plus row datatable. I am using a datatable that has a primarykey. I am also using a sqlitedatadapter:
Dim cbActiveCustomers As New SQLiteCommandBuilder(ActiveCustomersAdapter)
ActiveCustomersAdapter.Update(DTActiveCustomers)
I fill the datatable with this
sql = "SELECT * FROM Customers WHERE Status = 'Active'"
ActiveCustomersAdapter = New SQLiteDataAdapter(sql, conn)
ActiveCustomersAdapter.Fill(DTActiveCustomers)
I fill the table, loop through the table changing one date field by adding an increment variable to a date column and then try to save as above. There are no concurrent users and no other activity on the database. I am obviously not doing something correctly but have not found a pointer as to what I am missing.
Your help is appreciated. Brad
Edit: This error occurs when this command is executed:
ActiveCustomersAdapter.Update(DTActiveCustomers)
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Thanks C.P
- Further Edit:
InnerException = Nothing
When I expand the exception there are four red crosses at TargetSite:
1. CustomAttributes-> Method System.Reflection.MemberInfo.get_CustomAttributes cannot be called in this context.
2. IsSecurityCritical -> DirectCast(ex.TargetSite, System.Reflection.RuntimeMethodInfo).IsSecurityCritical cannot be called in this context.
3. IsSecuritySafeCritical = Method System.Reflection.RuntimeMethodInfo.get_IsSecuritySafeCritical cannot be called in this context.
4. IsSecurityTransparent = Method System.Reflection.RuntimeMethodInfo.get_IsSecurityTransparent cannot be called in this context.
The UpdateCommand produces this command:
UPDATE [main].[sqlite_default_schema].[Customers] SET [NextCallDue] = @param1 WHERE (([PrimaryKey] = @param2) AND ([CustomerNumber] = @param3) AND ((@param4 = 1 AND [Zone] IS NULL) OR ([Zone] = @param5)) AND ((@param6 = 1 AND [SiteCode] IS NULL) OR ([SiteCode] = @param7)) AND ((@param8 = 1 AND [FirstName] IS NULL) OR ([FirstName] = @param9)) AND
etc up to @param60 for all 31 columns. When I look at the UpdateCommand->Parameters->Result Views the values for each of the columns appears correct thus satisfying the conditions expected, however the @param4, @ param6 @param8 etc are all showing a value of 0 which does not meet the WHERE condition of the command. This is way over my understanding but it seems the command builder is generating parameters (@param4 @param6 @param8 up to @param60 being the even numbers) that are set to 0 based on some environmental setting to do with concurrency that I have not established. Where would I find further information to help track down what I am missing? Thanks Brad
Edit: After hours and hours of searching and testing without any success I decided to start with a blank Sqlite database and keep adding a column to the datatable then test the modify/save, this worked well until I added a Date column (called "LastCleaned"). At this point I got the concurrency error even though I was not changing the "LastCleaned" column. It appears my problem occurs when sqlite reads a date (which was created in SQLite Expert Personal as a SQL type of "DATE") and then tries to save it back to the database. Does this make sense to anyone? Thanks again Brad
