I have a parent row that I create, which will have a number of child rows related to it.
There is a status field in the parent row that I give a value of "UnderConstruction" (which is an enum with a value of zero) so that, while I'm creating the child rows, the parent row will not be processed by another method that looks for new rows to process.
After populating the rest of the parent row, I do a .Update on its SQLDataAdapter, which will do the insert and return me the PK, which I need so I can relate the child rows to the parent row.
Then I create the child rows (manually from code), assigning the FK value as fetched during the parent row update.
Finally, I change the parent row status item to "Ready" (enum value=1) and do another .Update on its SQLDataAdapter.
I get a concurrency error, which I've found to be that the "Original Value" of the status field in the parent row is 0 and the "Current" value is of course 1.
What I don't get is, can't you do a .Update, change a value, and then do another .Update
Secondly, is there a better way to accomplish this whole idea of inserting a row to get the PK and then creating child rows
A factor that may affect your answer is that I'm using the CommandBuilder object to generate my inserts and updates.
Thanks, everyone!
Concurrency violation when updating twice
Adrian McVoy
Just a quick reply to say thanks, Dave.
I'll be looking at this over the weekend to absorb everything.
To get past the problem I described, I wrote an insert statement that simply updates the Status field and called ExecuteNonQuery with it. Of course, that worked.
jsmircic
Just thought I'd simplify this post a bit.
If you do a MyDataAdapter.Update(MyTable), then change a value in a row, then do another MyDataAdapter.Update(MyTable), should this cause a concurrency error
piaskal
It could if you are using optimistic concurrency and the data changed between when you read it and when you saved it. But that's a good thing.
However, if the data did not change, you should be okay.
I can tell by your posts you are trying to wrap your brain around optimistic concurrency and command builder. It is best to play with a table in the Northwind Database trying stuff out. If you get stuck, you can post the Northwind code up to the forum where we can all play with it on our own PC's and understand the problem.
Here is some code that shows me reading all the Categories from the Northwind database, appending the number "2" to all the names, using an SqlCommandBuilder to create an UpdateCommand for the DataAdapter, and writing the changes back to the database.
Even when using a ConflictOption of CompareAllSearchableValues, which is lying down a serious safety net, a concurrency exception is not thrown unless I manually set a break point before the save and change some original data.
string
connectionString = "...Nortwind Connection String...";string
selectSql = "SELECT [CategoryID], [CategoryName] FROM [Categories]";DataSet categories =
new DataSet("Categories");SqlDataAdapter adapter =
new SqlDataAdapter(selectSql, connectionString);adapter.Fill(categories);
foreach
(DataRow category in categories.Tables[0].Rows){
string categoryName = category["CategoryName"].ToString();category["
CategoryName"] = categoryName + "2";}
SqlCommandBuilder builder =
new SqlCommandBuilder(adapter);builder.ConflictOption = ConflictOption.CompareAllSearchableValues;
adapter.UpdateCommand = builder.GetUpdateCommand(
true);adapter.Update(categories);
Here is a post talking about Optimistic Concurrency:
http://davidhayden.com/blog/dave/archive/2005/10/05/2503.aspx
Here is a post talking about SqlCommandBuilder:
http://davidhayden.com/blog/dave/archive/2006/01/21/2742.aspx
As mentioned in my article on SqlCommandBuilder above, it is interesting to note that CommandBuilder makes a call behind the scenes to the Database getting metadata about the table. Think about how often you use it and how much extra traffic you are creating to your database.
Hope this helps.
Regards,
Dave