locked
Help! "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" RRS feed

  • Question

  • Help! I have a Access 2003 Database that holds info like "customer name" "phone number" etc. When I enter that info and then click new record then go back to the previously created record and add some more information in the other text boxes ("address", "age", etc) I get the following error:
    Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

    and when I click on details then row, I get this:
    '((Help_Desk_Center.HDC_DataDataSet.Table1Row)(((System.Data.DBConcurrencyException)($exception)).Row)).Customer_called_on' threw an exception of type 'System.Data.StrongTypingException'

    What is all that and how do I fix it, get around it, ignore it, or what ever I need to do?? Thanks for the help
    Wednesday, December 13, 2006 5:22 PM

All replies

  • Hi,

    It's probably because you have not entered values for fields that are not nullable. Can you post more information?

    Charles

    Friday, December 15, 2006 4:49 AM
  • Cverdon, what other information would you like? In the Access database I have set "accept zero value" for all the fields. I can usually enter info into some of the boxes and leave the rest blank the first time around. It only seems to happen when I navigate back to a previous record and enter more information and then click "save". Your theory sounds like it might be a solution, so how would I set the fields to allow "nullable" values? Thanks for the help.
    • Proposed as answer by TheLearner Thursday, April 22, 2010 12:50 PM
    Friday, December 15, 2006 3:29 PM
  • Make sure that the fields' properties are Required = No.

    Can you post your code and the sql update and insert commands that are executed?

    Charles

    • Proposed as answer by MSDNRocks Monday, March 28, 2011 6:26 AM
    • Unproposed as answer by MSDNRocks Monday, March 28, 2011 6:28 AM
    Friday, December 15, 2006 8:35 PM
  • I also have the same problem exactly with sqlserver 2005 database
    Concurrency violation

    I try to solve it like MSDN which says get a fresh copy the that table and merge it into the old table and the save it again and also the concurrency violation exception occurs!!!

    i hope that this error could fixed in newer versions of ADO.NET

    i try to save and deal with the same database with java it works


    Friday, December 15, 2006 8:55 PM
  • Here is the code for the form:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;

    namespace Help_Desk_Center
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void table1BindingNavigatorSaveItem_Click(object sender, EventArgs e)
            {
                this.Validate();
                this.table1BindingSource.EndEdit();
                this.table1TableAdapter.Update(this.hDC_DataDataSet.Table1);

            }

            private void Form1_Load(object sender, EventArgs e)
            {
                // TODO: This line of code loads data into the 'hDC_DataDataSet.Table1' table. You can move, or remove it, as needed.
                this.table1TableAdapter.Fill(this.hDC_DataDataSet.Table1);

            }



    The "required" fields are all set to no and to allow zero length strings. The update commands are provided by Visual Studio as I did not program them in (just drag and drop stuff)
    Saturday, December 16, 2006 6:51 AM
  • Ok that the standard autogenerated code.

    Can you please open the .xsd file and tell me the sql code of the SelectCommand, InsertCommand and the UpdateCommand of the table that does not work.

    What happen if you add a new record, close the app and reopen it, then try to modify the data?

    Charles

    Saturday, December 16, 2006 12:08 PM
  • Sorry for the long delay in the response, holidays and all ya' know. Thank you for all you help so far though. Anyway, I'm not sure I understand what you are asking for. I'm using Access 2003 and letting Visual Studio handle all the code as far as updating, inserting, and selecting goes. I don't know where I find this code at. When I open the .xsd file this is all I get:



    The app has yet to be compiled into a stand alone version. I just click on debug > build to test the program from VS.

    I can create a new record, type in my name, save, add another record, type in a name, save, go back the the first record, type in like a phone number and when I click save; that's when I get that error.
    Tuesday, December 26, 2006 8:02 PM
  • Click on Fill, GetData and copy paste the sql of the different commands from the Properties window.

    Charles

    Tuesday, December 26, 2006 10:09 PM
  • ======
    Fill
    ======
    SELECT     TicketID, [Customer Name], [Phone Number], [Secondary Phone], [Password], CPU, RAM, [Computer Make], [Computer Model], [Serial Number],
                          [Windows Version], [Issue Type], Issues, Process, Notes, Updates, Drivers, [Sound Card], [NIC/Modem], [Received by], [Repair completed by],
                          [Date completed], [Customer called on], Status, Charger, Software, [Case]
    FROM         Table1


    That's the only "sql" looking code I could find in the properties window ( the window on the right).
    ********There is a new development though. When ever I open the bin folder for the project and run the .exe file, the program works great. No problems what so ever. Maybe it is just something within Visual Studio (maybe how it locks the database or something)??*********
    Tuesday, December 26, 2006 10:23 PM
  • Was this error ever resolved?  I am having issues when executing the .Update method (SQL Server 2005) on my typed dataset data adapter and keep bumping into the "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" exception.  I have read a plethora of opinions on this topic and I really need to solve this problem.

     

    Thursday, January 3, 2008 10:16 PM
  • I dont know if this will help you but - spaces in the field names will cause you all kinds of problems. It would help you if you could get rid of them.

     

     

    Thursday, January 3, 2008 10:23 PM

  • I too was facing this problem in one of my forms. It actually traced back to a problem with the WHERE clause of the SQL statement. When we drag a table on a form, .NET automatically generates SQL statements for SELECT, INSERT, UPDATE and DELETE. By default it generates arguments for all the columns of that table in the WHERE clause of these SQL statements. I was working with a table Doctor and its primary key was Doctor_ID. So I removed all the arguments (except my primary key, i.e. Doctor_ID) from the WHERE clauses of UPDATE and DELETE statements and the error quickly got removed.

    Here are the precise steps to follow:

    1. Open the DataSet in the designer.
    2. Click on the adapter portion of the relevant table. For example in my case I clicked on DoctorTableAdopter.
    3. The properties window will show the UpdateCommand and DeleteCommand for this adopter.
    4. Expand Update and Delete commands by clicking the + sign on their left. This would show the CommandText property for these commands.
    5. Click on the value entered against CommandText. It will show you the SQL statement for that command. Edit its WHERE clause and remove all other columns except the columns included in your primary key. For example in my case a rather lengthy WHERE clause (which was trying to compare more than a dozen columns) was modified to WHERE (Doctor_ID = @Original_Doctor_ID).

    I hope it would help.
    Thursday, January 10, 2008 4:56 AM
  • I am having the same issue. my problem is that the dataadapter is trying to update row with a primary key that dosn't exist it the database (I am reciving my data from a CSV file). Is thier any way to set the adapter to ignore this error? I tried to set the property

    "ContinueUpdateOnError" to true and it did't help.

     

    Thanks

     

     

     

    Tuesday, January 22, 2008 9:33 AM
  •  

    Hi Every Body...

     

    I suffered alote of this error, searched many sites and alote of forums which was all useless.

     

    but finally I solved it.

     

    The problem was so simple.

     

    This error has two reasons :

     

    1-) Editing an Auto Increment Data Field.

    2-) Updating any row of a table which hasn't a Primary Key Field.

     

     

    I wish you all a simple programming. 

     

    Thanks all

     

    Regards

    Saturday, February 23, 2008 12:49 PM
  •  Syrian Programmer wrote:

     

    Hi Every Body...

     

    I suffered alote of this error, searched many sites and alote of forums which was all useless.

     

    but finally I solved it.

     

    The problem was so simple.

     

    This error has two reasons :

     

    1-) Editing an Auto Increment Data Field.

    2-) Updating any row of a table which hasn't a Primary Key Field.

     

     

    I wish you all a simple programming. 

     

    Thanks all

     

    Regards

     

    Also try to Fill data after calling TableAdapterManager.UpdateAll()

    example:

    Me.TableAdapterManager.UpdateAll(Me.PerDeptDataSet)

    Me.TblSalaryTableAdapter.Fill(Me.PerDeptDataSet.tblSalary)

     

    it works 100%.

    regards,

    Wednesday, February 27, 2008 2:10 PM
  • So, In this scenario ( where tehre are identity key value in primary key, how will you handle it ?? Can you give me any sample? i am stuck with it.

     

    Thanks

    Jit

    Friday, April 18, 2008 9:48 PM
  • Hi JitenK,

     

    Have a look at this thread for a code samples that involves autogenerated ids.

    http://forums.microsoft.com/forums/showpost.aspx?postid=2615370&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0\

     

    Charles

    Friday, April 18, 2008 10:37 PM
  • Thanks mate, you're a life saver!
    Monday, July 21, 2008 11:01 AM
  • I am suffering a lot and not able to fix this problem. I am trying to update a table which is in SQL Server 2005 having following fields:

    DBName varchar (PK)

    DBSize varchar

    usedPercentage decimal(10,0)

    date datetime

     

    My data table structure is

    DBName string

    DBSize sting

    usedPercentage decimal

    date datetime

     

    Please note that usedPercentage value in dataset is ver big like" 75.265465455785222465465464564".

     

    Please help. I am getting same error.

     

    Friday, July 25, 2008 10:02 PM
  •  

    Sorry to tell you that but YOU ARE ALL WRONG.

     

    This occurs because you're probably using the AdapterBuilder which constructs your UpdateCommand for you and if you do so , this builder get fucked all the way because you already issued an Update before which seems to stay in a pending state in some sort.

     

    Try this :

     

    Use your DataSet, do a first update by using this AdapterBuilder and perform an AcceptChanges at the Dataset level

    now, try again immediatly after.  This result in a Concurrency violation.

     

    Here's another example.  Use a Grid binded to your DataSet,  Ask your grid to update a field.  The binded table will be not be updated until you explicitly say so to the DataSet.

     

    If you do so by the grid then by the dataset you get the same error.

     

    This occurs because 2 updates are triggered on the Dataset fro which the first one isn't completed or doing something else that I don't even know what yet.

     

    So there must have something Microsoft need to tell us what to do before issuing another update

     

    Wednesday, August 27, 2008 4:07 PM
  • Here's a concrete proof.

     

    See the error which you all like ?

    Now look at the bottom in the WATCH window.  See that the HasChanges = true ?

    this means an update has been issued before but even tought you did an ACCEPTCHANGES this state doesn't seem to change so you're like trying to update over another update.

     

    EVEN TOUGHT YOUR FIRST UPDATE WAS SUCCESSFUL.

     

    Why is that ???????????

     

     

    http://pages.videotron.com/gear/proof.jpg

     

    Wednesday, August 27, 2008 4:17 PM
  • This error will also occur when you are updating a field and changed the private key field's value

     

    ie say you used a number and that number is 13 and you changed it to soemthing else that doesn't exist say 423. since 423 does not exist, there is no record to edit hence Concurrency Violation in other words the server found no record to update based off the primary key because the item does not exist becasue the record's primary key was changed to an invalid value.

    Friday, October 17, 2008 6:45 PM
  • When you use tableAdapters to get rows into a typed dataset the adapter saves the original data loaded from the database in the DataRowVersion.Original version of the row, you can check it using myDataRow["columnName",DataRowVersion.Original].

    Then when you perform a UpdateAll using the TableAdapterManager of your dataset it will include in the where clause of the update query all of the rows matching with original values.
    This is how it tests that nobody has done any update on the record since you loaded it into your dataset, if any field has been changed the result for the update will be 0 because any record matchs the original data.

    I'll try to explain with an example, my english is not very good :P

    You have a table called people with 2 field, name(being the key) and age. And a typed dataset with the same structure in your application.

    Then using your table adapter you do a getby_name(john) on the table people, and it returns a row:

    Name    Age
    ---------------
    John     20

    Now you need to change john's age to 21 and update the data.


    ...
    row["Age"] = 21   /// At this point you have 2 versions of the same datarow Current and Original.
    ...
    MyTableAdapterManager.UpdateAll(ds);


    The update query the table adapter will execute is the next:

    UPDATE people SET Age = 21 WHERE Name = "John" and Age= 20 // Notice that it will include the original age to the where, thats the concurrency test.
    If the age was modified after you loaded the record you will get 0 results and therefore a Concurrency violation.



    Thats how it works.


    Tuesday, February 17, 2009 12:52 PM
  • The precision on the dataset has to be the same that in the database, check my other post on this thread to know why.

    Tuesday, February 17, 2009 12:54 PM
  • For SQL Compact Edition:

    When a new row entry is added, the primary key doesn't appear to adjust value correctly in my case. The values I receive remain negative numbers. Usually, the value is negative until you hit save, then it's supposed to change to positive (because negative value is invalid). So I auto-increment the primary key column myself with positive values using the DefaultValuesNeeded event, which puts a new value in a specified column for a new row.

    1. Select the GridView, find the DefaultValuesNeeded Event handler.

    2. e.Row.Cells["primary key column name"].Value = some value or e.Row.Cells[integer representing primary key column].Value = some value

    3. Of course, you'll have some challenges keeping the value unique; for instance, if you set an integer to increment on its own, then the value will be fine until you hit an integer value that is too large. This becomes an issue if you continually add rows for no reason. The value will continue to increment.

    If you delete a row, you could probably write some code to reuse that number. Perhaps an array that keeps track of all numbers used.

    4. This will not work if you set the primary key to auto-increment (Identity value set at true). It will give you an error stating that the cell cannot be modified. Set the column as Primary = true, no nulls, Identity = false

    This is my solution until I figure something else out. This isn't a problem with SQL Server 2008 Express Edition; at least, I haven't encountered it there. The primary key there increments and changes its value accordingly. I suspect the tableadapter is not inserting correctly when using the Insert, Update, Delete method. If the Identity would just set a positive number to begin with, there'd probably be no issues because it increments the negative number just fine.

    If this works for you, please pass it around as a quick fix.
    Friday, April 10, 2009 8:37 PM
  • I have tried many,many solutions to resolve this error - refilling the table did the trick! Thanks

    Wednesday, June 3, 2009 7:24 PM
  • I was facing the same concurrency issue.
    I am using dataadapter to update the tables.
    I was trying to insert rows in the database in the modified state when actually there was no data

    To check the row state one should check at the code
    DataTable.Rows[].RowState

    If it is Modified and rows do not exist in the database, dbConcurrency exception will arise
    so one should take care of row state also.

    Regards
    Prashant Jain
    Monday, June 8, 2009 5:33 AM
  • Hello,

    I was facing the same issue but discovered it was because of the Select Statement.

    If your select statement contains any CASE statements then this could be the issue.

    For example: A developer had changed a select statement to inspect one of the column values being returned. If it was a certain value, then NULL was returned, else the real value was returned.

    When the data tried to update it saw that the NULL value for the column was not the same as the one in the database and so we would get the described error.

    Hope this may help anyone else that might have the same issue.

    Richard Slade
    • Proposed as answer by Richard Slade Monday, July 13, 2009 12:19 PM
    Monday, July 13, 2009 12:05 PM
  • i have the same problem too
    what i did was this

    after adding a new record,you should clear the dataset(ds.clear)
    then refill the dataset using .fill

    when you go back to this record and edit the content, you should be able to save it
    Friday, December 18, 2009 10:54 AM
  • Thx Prashant. That was my issue. If only their error messages were a little clearer ... something like "Concurrency violation (attempt to update non-existing records)" ...

    Thursday, January 7, 2010 8:42 PM
  • There are many things that can cause this error. After struggling with my own issues for a couple days, I found that implementing certain sequences of the ___TableAdapter.Update(_____), ____TableAdapter.Fill(______), _____DBDataSet.________.AcceptChanges would make this error to go away (but then would cause other issues with how the data was displayed in the bound datagridview. You really need a firm understanding of what these commands are doing and the relationships between the database, tableadapters, datasets and the bound source. For me, the key was to make sure that edits were committed to the underlying db and datasets before moving on and making new edits. This was useful to me in explaining the different ways that data can be edited (deleted) in a database:

    http://msdn.microsoft.com/en-us/library/ms233823(VS.80).aspx

    Depending on whether you are working with the TableAdapters or Tables, different protocol are required. It really requires some digging to wrap your head around how everything interacts. Once I gained some understanding, my error went away. 

     

     

    Sunday, April 18, 2010 8:15 PM
  • The RESOLUTION IS >>>

     

    DataTableAdapter >> Properties >> UpdateCommand >> (delete all parameters from where condition other than primary key)

     

    Repeat the same for Delete command

    Thursday, April 22, 2010 10:33 AM
  • I would almost say the ultimate solution would be to not use strongly typed dataset, but it is a handy tool that draws many in to it.  when i first started playing with database it was within a few weeks that the designer was giving trouble even in the IDE.  it has improved since 2005 but using straight code is better if you are able.

    the designer in many way is like ice cream with chocolate sauce on top, when you don't need the chocolate.  you can take the chocolate off but you have to be careful not to remove the ice cream.  even so, it is not possible to remove the chocolate without removing some of the ice cream.  so, if you can just create ice cream yourself, as you need it, you don't have to worry about the chocolate getting in the way.

    although databinding doesn't have the stigma it seems to have had before, i still blame some issues on this as well.

    what i really recommend is to work with code and create some template classes which you can reuse in your projects.  the appeal of the designer i realize though, is it does a lot of the work for you.  however, it leaves you with a lack of understanding of how it works.  you just know it works, if you don't need to modify its functions too much.

    hope this helps


    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Thursday, April 22, 2010 2:53 PM
  • Also try to Fill data after calling TableAdapterManager.UpdateAll()

    example:

     

     

    Me.TableAdapterManager.UpdateAll(Me.PerDeptDataSet)

     

     

    Me.TblSalaryTableAdapter.Fill(Me.PerDeptDataSet.tblSalary)

     it works 100%.

    regards,

     

     

     

     

     


    I  did this and I'm happy with it as I expect the database to remain small (ie sub 1000 records) so is fairly quick.

    However, potential speed issue aside, one problem I need to know how to resolve is how to make the application view the last record added. or last record I suppose,  as I dont do any sorting or anything special.

    Currently after the fill, the first record in the base is displayed, which is not very user friendly at all. They would proably want to give te recently added record a once over visually before moving on.  

    Can somebody please help with that code?

    Currently i have :

     Try
          Me.Validate()
          Me.ContactsDBBindingSource.EndEdit()
          Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
          Me.ContactsDBTableAdapter.Fill(Me.Database1DataSet.ContactsDB)
     	<strong><span style="text-decoration:underline">'what goes here to move to the last record please?<br/></span></strong>
        Catch ex As DBConcurrencyException
    
          Dim customErrorMessage As String
          customErrorMessage = "Concurrency violation" & vbCrLf
          customErrorMessage += CType(ex.Row.Item(0), String)
          MessageBox.Show(customErrorMessage)
    
        End Try
    

    Many thanks in advance. :)

     


    Rob
    Tuesday, November 16, 2010 6:49 PM
  • Also try to Fill data after calling TableAdapterManager.UpdateAll()

    example:

     

     

    Me.TableAdapterManager.UpdateAll(Me.PerDeptDataSet)

     

     

     

    Me.TblSalaryTableAdapter.Fill(Me.PerDeptDataSet.tblSalary)

     

     

     

     it works 100%.

    regards,

     

     

     


    I  did this and I'm happy with it as I expect the database to remain small (ie sub 1000 records) so is fairly quick.

    However, potential speed issue aside, one problem I need to know how to resolve is how to make the application view the last record added. or last record I suppose,  as I dont do any sorting or anything special.

    Currently after the fill, the first record in the base is displayed, which is not very user friendly at all. They would proably want to give te recently added record a once over visually before moving on.  

    Can somebody please help with that code?

    Currently i have :

     Try
    
       Me.Validate()
    
       Me.ContactsDBBindingSource.EndEdit()
    
       Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
    
       Me.ContactsDBTableAdapter.Fill(Me.Database1DataSet.ContactsDB)
    
     	<strong><span style="text-decoration:underline">'what goes here to move to the last record please?<br/></span></strong>
    
      Catch ex As DBConcurrencyException
    
    
    
       Dim customErrorMessage As String
    
       customErrorMessage = "Concurrency violation" & vbCrLf
    
       customErrorMessage += CType(ex.Row.Item(0), String)
    
       MessageBox.Show(customErrorMessage)
    
    
    
      End Try
    
    

    Many thanks in advance. :)

     


    Rob

    Hi all, I did it.
    i was trying to movelast on the tableadapter instead of the bindingsource. Doh!

    Me.ContactsDBBindingSource.MoveLast()
    

    The concurrency violation error is totally resolved from my point of view. thank you guys.

     

     


    Rob
    • Proposed as answer by AlexSrG2010 Saturday, February 26, 2011 10:21 PM
    Wednesday, November 17, 2010 1:48 PM

  • I too was facing this problem in one of my forms. It actually traced back to a problem with the WHERE clause of the SQL statement. When we drag a table on a form, .NET automatically generates SQL statements for SELECT, INSERT, UPDATE and DELETE. By default it generates arguments for all the columns of that table in the WHERE clause of these SQL statements. I was working with a table Doctor and its primary key was Doctor_ID. So I removed all the arguments (except my primary key, i.e. Doctor_ID) from the WHERE clauses of UPDATE and DELETE statements and the error quickly got removed.

    Here are the precise steps to follow:

    1. Open the DataSet in the designer.
    2. Click on the adapter portion of the relevant table. For example in my case I clicked on DoctorTableAdopter.
    3. The properties window will show the UpdateCommand and DeleteCommand for this adopter.
    4. Expand Update and Delete commands by clicking the + sign on their left. This would show the CommandText property for these commands.
    5. Click on the value entered against CommandText. It will show you the SQL statement for that command. Edit its WHERE clause and remove all other columns except the columns included in your primary key. For example in my case a rather lengthy WHERE clause (which was trying to compare more than a dozen columns) was modified to WHERE (Doctor_ID = @Original_Doctor_ID).

    I hope it would help.

    This is what I'm looking for, Nice writing, Thanks for your analysis!
    Sunday, February 20, 2011 1:18 AM
  •  Syrian Programmer wrote:

     

    Hi Every Body...

     

    I suffered alote of this error, searched many sites and alote of forums which was all useless.

     

    but finally I solved it.

     

    The problem was so simple.

     

    This error has two reasons :

     

    1-) Editing an Auto Increment Data Field.

    2-) Updating any row of a table which hasn't a Primary Key Field.

     

     

    I wish you all a simple programming.  

     

    Thanks all

     

    Regards

     

    Also try to Fill data after calling TableAdapterManager.UpdateAll()

    example:

    Me .TableAdapterManager.UpdateAll( Me .PerDeptDataSet)

    Me .TblSalaryTableAdapter.Fill( Me .PerDeptDataSet.tblSalary)

     

    it works 100%.

    regards,

    Fantastic! Thank you so much, I've been pulling out what little hair I have for days.

    Just FYI, I'm shaven, not bald :)

    Wednesday, April 4, 2012 7:53 PM
  • Solution by Syed Sami Naushahi works, but is not a very good solution as your application will now lose concurrency protection.

    In my case, probably because of a database design change, I had some columns that allowed NULL that I suspect at one time did not allow NULL. As a result, the WHERE clauses for the Delete and Update commands in the Table Adapter did not include the necessary NULL tests, and as a further result, raised Concurrency errors whenever a NULL value was encountered.

    To fix this, but retain the desirable aspects of concurrency management, I did the following:

      1.  Right click the Table Adapter and choose Configure...
      2.  Click on Advanced Options ... and note which options you have selected. Then uncheck all options.
      3.  Click OK. Click Finish.
      4.  Again, right click, choose Configure..., click on Advanced Options....
      5.  Recheck the options you noted as having been selected in step 2.
      6.  Click OK. Click Finish.

    This causes VS to regenerate all of the SQL statements for the table adapter and the regeneration will now reflect the actual state of the data base.


    -Rob

    Thursday, November 13, 2014 7:26 PM
  • This is exactly what happened to me. I had to change a field from not allowing nulls to allowing. The only way to fix it was to remove the field from the project then add it back in after I allowed the null. Thanks Charles.
    Sunday, November 1, 2015 12:53 PM