Concurrency violation?

[Yuliya]

I have another very straightforward piece of code -- DataGridView bound to a database table -- that used to work and now doesn't. When TableAdapter.Update() is called, it throws Concurrency violation exception. No one else is using this database. What could this be?

Thank you.

 

[cjard]

The record it wanted to update.. went away. You can uncheck "use optimistic concurrency" in the tableadapter config, but then you wont find out which of your rows went away during edit. Dont ask me why, when you assert that noone else is editing the db concurrently, you see this exception.. i dont really know, and without spendign hours looking at your setup it would be hard to say!

Most people just make the TA without Optimistic concur and leave it at that (file it in the "too hard" bin)

 

[Yuliya]

Yes, that's exactly what I ended up doing . Although it would be nice to find out why this was happening...

 

[Administrator]

I struggled with this one for a while and I laughed when I figured out what I forgot to do! It's not so much the concurrency part of the TableAdapter, it's that you are NOT refreshing your DataSet after updating it.

Take for example when you add a new row. Your PK's are negative numbers in the DataSet (at least in VS 2008). Then when you update the DataSet your database assigns the proper PK's, positive numbers if you're using an Identity Seed, AutoIncrement, etc. Now, before you can use that DataSet again you have to get the latest info from the Database so your DataSet now has the proper primary keys so when you NEXT go doing your update, the rows will be found as they are now known by the new PK's.

Comprendez?

 

[Yuliya]

I'm confused. What do you mean by getting the latest info from the database? Don't you get it when you call the Update function of the TableAdapter?

 

[cjard]

I struggled with this one for a while and I laughed when I figured out what I forgot to do! It's not so much the concurrency part of the TableAdapter, it's that you are NOT refreshing your DataSet after updating it.

Take for example when you add a new row. Your PK's are negative numbers in the DataSet (at least in VS 2008). Then when you update the DataSet your database assigns the proper PK's, positive numbers if you're using an Identity Seed, AutoIncrement, etc. Now, before you can use that DataSet again you have to get the latest info from the Database so your DataSet now has the proper primary keys so when you NEXT go doing your update, the rows will be found as they are now known by the new PK's.

Comprendez?

In all the scenarios I've seen, the Update() does this automatically.. If you submit a PK that is e.g. -1 and the db recalcs it via stored procedure, trigger or autonumber, then the return value or new PK is automatically propagated back into the datarow. I never bothered looking too deep into how, but I presume the default code the DS generator writes simply takes the parameter value back out aft erht statement is over, and writes it into the row

 

[Administrator]

It depends on the scenario, in my case using Web Services (WCF) you don't have that luxury. This is also one of those three properties on the TableAdapter where you turn off Concurrency as to whether you want to refresh the DataSet.

In the attached image you would want the bottom checkbox CHECKED.

 

[cjard]

I recognise the top two from memory.. the bottom one.. was that in .NET 2?

 

[mikhael123]

Concurrency

I just had the same problem. I think this is what happened to my prog:
I had a loop that updated items in my dataset (VB.NET 2003) and then somewhere in that loop I caught a System.ArgumentException. This of course didnt update the database but I already had some floating new data in my datatable (During the first part of my update loop which didnt have a prob). When I tried to update again the data overlapped with my previous update thus the concurrency problem.

Heres what I did to fix it. After I caught the System.ArgumentException I reset the datatable and refilled it. Then it worked.
DataTable.clear()
DataAdapter.Fill(DataTable)

I hope this works for you too! Cheers

 

[Administrator]

You can set the DataAdapter property to ClearBeforeFill (or whatever it's called) - it's automatic if set.

 

[jwh]

Neal / cjard

The Update() command willl refresh PK's etc IF the underlying datasource supports multiple operations per command, e.g. When you call the update, each Update / Insert is followed by a Select within the same command.

This is why if you try to use a SQL Compact database, then a) it will not do this automagically, and b) the option to switch this on (as in Neals screenshot) will be grayed out.

So to the OP, what database engine are you using, as this may well determine why the data is not being refreshed for you.