Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1302
  • Last Modified:

Why am I receiving a concurrency error in a single-user application?

I am coding a client-server application which uses data adapters to update a database. My app contains a screen displaying the details of a Quote (Id, Creation Date, Creator etc.) along with the individual Quote Items (Item Id, Description, Price, Quantity). There is a one to-many relationship between Quote and Quote Items (which is implemented in my dataset).

I have no trouble creating a new Quote with various Quote items, but when I try to update an existing Quote I receive the error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

I am the only user of the application so I don't understand why a concurrency error is occuring. Can anyone tell me what I should be looking for?


0
otto8
Asked:
otto8
  • 8
  • 5
  • 2
2 Solutions
 
Bob LearnedCommented:
Sometimes that can happen if you have more than one data adapter locking the record.

Bob
0
 
otto8Author Commented:
hmm...
My update function contains two data adapters; one for the Quote and one for the Quote Items.

public QuotesInvoicesDataset UpdateQuoteInvoice(MySqlConnection Connection,QuotesInvoicesDataset dsQuote)
{
   MySqlDataAdapter daQuote = new MySqlDataAdapter("SELECT * FROM Quote", Connection);
   MySqlCommandBuilder builder = new MySqlCommandBuilder(daQuote);
   daQuote.Update(dsQuote, "Quote");

   //quote items
   MySqlDataAdapter daQuoteItems = new MySqlDataAdapter("SELECT * FROM QuoteItems",Connection);
   MySqlCommandBuilder builder2 = new MySqlCommandBuilder(daQuoteItems);
   daQuoteItems.Update(dsQuote, "QuoteItems");
}
The error is occuring when I run the update function of the first data adapter so I don't think its a problem with locking.
0
 
Bob LearnedCommented:
I don't see a Dispose call anywhere?  Is this 2005?  If so, then would a 'using' block help here?

Bob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
otto8Author Commented:
Good points. I've added Dispose calls for both data adapters, and a using block  but the error still occurs on the first Update.

Yes... I'm running 2005
0
 
Bob LearnedCommented:
What is the timing of things?  What happens before the Update is called?  Is there a call to DataRow.AcceptChanges anywhere?

Bob
0
 
otto8Author Commented:
The timing of events is as follows..
# The user populates a disconnected QuoteDataset by selecting to view a specific Quote.
# The user clicks Edit and amends the Quote (the Quote Items that belong to the QuoteDataset are updated dynamically in the gridQuoteItems_CellValidated event)
# When the user clicks Save, any further items are saved to the QuoteDataset and then the QuoteDataset is passed to the data layer update method (which is detailed above).

I tried using AcceptChanges before calling the update but it stopped my dataset from updating.
I also tried using the GetChanges method and then passing that dataset to the update method but still recieved the same DB COncurrency issue
0
 
Bob LearnedCommented:
Are you using a typed DataSet with TableAdapters?

Bob
0
 
otto8Author Commented:
Yes I'm using a typed DataSet with table adapters. Is this a problem?
0
 
Bob LearnedCommented:
I was using a typed DataSet with TableAdapters, and I kept getting that exception.  Did you configure the TableAdapter to use optimistic concurrency?

Bob
0
 
otto8Author Commented:
sorry I misread your question... I'm using DataAdapters NOT TableAdapters. I guess they're pretty similar.. so I'm now looking into setting the concurrency. I'll let you know how it goes...
0
 
Miriam0000Commented:
I believe that this error occurs in single user environments when there is an error in the update SQL or the parameters.  I don't know your exact code, but you might want to check out your update SQL in a query environment and make sure it is correct.
0
 
otto8Author Commented:
Thanks Miriam0000,
I'm using a CommandBuilder to automatically generate the Update command. So I used the GetUpdateCommand() method to return the update command SQL, pasted that to my SQL environment and replaced the parameter names with actual values. The command ran without any error.
0
 
Miriam0000Commented:
One last thing... when I had similar situation, it ran ok but affected 0 rows resulting in the problem.  doesz your sql environmment indicate that rows have been updated?  if so, how many
0
 
otto8Author Commented:
Hi Bob, I looked into replacing my data adapters with table adapters, but whenever I try to set the table adapters GenerateDBDirectMethods property to true (so that update statements with be automatically generated), I receive the following error message... "The type of the database object does not allow to set GenerateDBDirect to true."  I suspect this is due to my database being MySQL!

Not sure if its possible to set the the concurrency options on a data adapter.
0
 
otto8Author Commented:
Thanks Miriam and Bob. Miriam pointed me in the right direction in telling me to check the SQL that wasgenerated by the CommandBuilder. My Update command had some very conveluted code, so I ended up writing the Insert, Update and Delete commands manaully and ensured that only one datadapter was locking the record (Bob's advice)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now