none
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records RRS feed

  • Question

  • hi , please i need your help out there,

    i know that this error is very obviusly to you but i could not trouble shout of what is going wrong.

    here is my problem. i have access 2007 table lets named it TABLEA which i have some fields , the first one is named ID and is type of autoNumber and is set to primary key. well everything works very well update delete select ect.. until i decide to change THE ID column to type TExt. in my VB Code at least am sure that i change all neccesary steps to regognize my new ID , and when am telling to the binding source to add a new record i am also set the bindingsource.current("ID")=someID

    am succesfully select any row and display it to my VB screen but i cannot update it. the Strange think is that if i from my application select to add a new product i can save it ,retreive it and save it again normally as many times i want. BUT FOR my old records that was having already a values in the ID i can only select from them and i cannot update them. my save Procudure select procedure is the same for both,and for new records and for old records. Please i will appriciated it for any help.

    Here Is my Select Procedure

     Public Sub LoadDocumentDetailsBYID(ByVal ID As String)
            Dim CmdString As Odbc.OdbcCommand = New Odbc.OdbcCommand("Select * from DocMngrDocumentDetails where ID='" & ID & "'", CN)
            DocumentDetailsTA = New Odbc.OdbcDataAdapter(CmdString)
            DocumentDetailsDS.Clear()
            DocumentDetailsTA.Fill(DocumentDetailsDS, "MyTable")
            DocumentDetailsBS.DataSource = DocumentDetailsDS.Tables("MyTable")
            DocumentDetailsBuilder.DataAdapter = DocumentDetailsTA 
            BindControls()
            
        End Sub

    and here is my update procedure

    DocumentDetailsBS.EndEdit()
    DocumentDetailsTA.Update(DocumentDetailsDS.Tables("MyTable"))


    stelios ----------


    • Edited by stelios84 Tuesday, February 28, 2012 9:47 AM
    Tuesday, February 28, 2012 9:45 AM

Answers

  • This would imply that the DataRow should be added as new in the database and that the old one should be removed. 

    I don't know if there are currently still databases where you can change (primary key) Id's, it would mean that relation tables should endless times be reconstructed.

    But what you want does not exist in Ado Net.

    AFAIK You have to delete the row and to add a new one if the primary key changes.


    Success
    Cor

    • Marked as answer by stelios84 Sunday, March 4, 2012 6:28 PM
    Saturday, March 3, 2012 9:02 AM

All replies

  • Rows which have the rowstate unchanged are not handled by the dataadapter, why would it?

    Be aware however that this message is mostly given if you have updated the data already, so the rowstate is changed and you have again done an update. Than the original data does not match anymore to the updated data.


    Success
    Cor


    Tuesday, February 28, 2012 4:13 PM
  • hi cor thanks for the reply, am really stack on this . there 2 scenarios here. let me give a better a example of what is happen

    assume Table Customer
    ID - is primarykey and is autoIncrement
    Name - Text
    Surname - Text
    Assume that this table has 2 records
    record # 1 ID Name Surname
                     2  George Brown
    Record #2  3  Michael Drew

    Now am deciding to change the Type of the primary key field
    Table Customer
    ID - Primary key / Type Text
    Name - text
    Surname - text
    my table customer now has still 2 records
    record # 1 ID Name Surname
                     2  George Brown
    Record #2  3  Michael Drew

    Now am running my VB Application
    BindingSource.AddNew
    BindingSource.Current("ID")=SomeUniqueID (DateTime for example)
    BindingSource.Current("Name")=Maria
    BindingSource.Current("Surname")=Brown

    BindingSource.EndEdit
    TableAdtapter.Update(MyDataSet.table("MyTable"))
    Succesfully update it.

    BUT in case am trying to update record #1 or record #2 (then the error occures.)
    Am not sure but it make me think that this may be a bug of access 2007 ? i dont know..
    i hope to explain  my issue clearly ,am not very good in english. so please forgive me.

    any help?


    stelios ----------

    Tuesday, February 28, 2012 6:55 PM
  • Hi stelios,

    Welcome to MSDN Forum.

    I'm afraid I've no Access2007 in my machine, I have tested in Access2010. I define the primary key as Text type, when I insert a record, I assign DateTime.Now().ToString() to it, insert successfully. Then I try to update the record, it also works well. Below is my code.

    using(OleDbConnection cont = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\v-alll\Documents\Database1.accdb")) { OleDbCommand cmd = new OleDbCommand(); string insertCommand = "update TestTable set FirstName=@FirstName where LastName='Allen'"; OleDbParameter parFirst = new OleDbParameter("@FirstName", "Lee"); cmd.Connection = cont; cmd.CommandText = insertCommand; cmd.Parameters.Add(parFirst); cont.Open(); cmd.ExecuteNonQuery(); Console.Write("OK"); Console.Read(); }

    By the way, please try to create a new project and just test modifying record with primary key of Text type, to make sure whether the it is really related to Access2007 or your code. If it related to the code, please upload the project to SkyDrive and post the link here, I'll help you to test it. If it related to Access2007, I suggest you to ask in Access For Developer Forum, there are more specialists of Access there.

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, March 1, 2012 4:23 AM
    Moderator
  • Stelios,

    The ID name with an autoincrementer is changed after the update with a DataAdapter. 

    If I have problems like yours I mostly do direct after the update of the datatable 

    DataTable.Clear
    Adapter.Fill(TheDataTable)

    It also takes care that changes done by somebody else are included after the last update so anyway something to do.


    Success
    Cor

    Thursday, March 1, 2012 10:11 AM
  • Allen thanks for your time.. what you did is very nice,  but please let me ask you something first.
    as you said , you have defined a table and you have also defined a primary key as text.

    Can you TEST the follow?
    Can you first create a table and the primary key set it as AutoNumber - AutoIncrement TRUE , then try add 2 or 3 records.
    After you are done with this, do the following .
    Change the type of the primary key to text with out Removing your old records you just insert. this normally will not affect you ID's becuase will convert the number to strings.
    Then try now from you application to retreive one of those old records you hav and update them. here is where i have the problem.
    And then try again also from your application to add a new record. here also mine is working.

    also allen if i use a custom update like you did i have no problem. but if i use the CommandBuilder generated is where the problem occures.

    am really thank you for you time allen and core.

    i will also post my thread there allen thank you very much.


    stelios ----------




    • Edited by stelios84 Thursday, March 1, 2012 1:39 PM
    Thursday, March 1, 2012 1:33 PM
  • Hi stelios84,

    I have tested as you mentioned. Below is the code,

     static void Main(string[] args)
            {
                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\v-alll\Documents\Database2.accdb"))
                {
                    string selectCommand = "select ID,FirstName,LastName from TestTable";
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = con;
                    cmd.CommandText = selectCommand;
                    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    dt.Rows[0]["FirstName"] = "Zhang";
                    adapter.Update(dt);
                    Console.WriteLine(dt.Rows[0]["FirstName"].ToString());
                    Console.Read();
                }
            }

    My test steps as below
    1. Create a database table named 'TestTable' (ID(AutoNumber,Primary Key), FirstName(Text), LastName(Text));

    2. Write code to insert 3 records into the database;

    3. Change the type of the ID column as Text and didn't delete the records;

    4. Write code to insert 1 record;

    5. Write the code above to update a record;

    Everything works well. Is the steps I did correctly? The different is I used Access2010, not 2007.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Friday, March 2, 2012 6:43 AM
    Moderator
  • as i read you did exact what i did allen but yes the difference is that we user other offices version. i dont know what else to do, i fix that manualy by creating onother table with my fields that i want and am reading and writing from one table to the other in order to fix it and then when i done to delete the unwanted table. am still wondering what is going wrong. but am really thanks for your time helping me.


    stelios ----------

    Friday, March 2, 2012 7:10 AM
  • stelio,

    Did you also try what I wrote?


    Success
    Cor

    Friday, March 2, 2012 7:16 AM
  • forgive me cor ,i dont understand what i should do in your example can you please help me?

    stelios ----------

    Friday, March 2, 2012 7:20 AM
  • BindingSource.EndEdit
    TableAdtapter.Update(MyDataSet.table("MyTable"))
    MyDataSet.Table("MyTable").Clear
    TableAdtapter.Fill(MyDataset.Table("MyTable"))
    I had the idea I wrote this alreeady.

    Success
    Cor

    Friday, March 2, 2012 7:44 AM
  • yes cor i did that already but the error message appears on update statment

    BindingSource.EndEdit
    TableAdtapter
    .Update(MyDataSet.table("MyTable")) <- Concurrency violation exception .
    MyDataSet
    .Table("MyTable").Clear
    TableAdtapter
    .Fill(MyDataset.Table("MyTable"))

    but as i messioned this dose not affect for any new records for example 
    BindingSource.Addnew
    BindingSource.Current("ID")=DateTime.now

    BindingSource.Current("Name")="SomeName"
    BindingSource.Current("Surname")="SomeSurname"

    BindingSource.EndEdit
    TableAdtapter
    .Update(MyDataSet.table("MyTable"))
    MyDataSet
    .Table("MyTable").Clear
    TableAdtapter
    .Fill(MyDataset.Table("MyTable"))
    Everything works perfectly

    but for the records that the ID was convert from autonumber to text i have the problem


    stelios ----------

    Friday, March 2, 2012 7:54 AM
  • This would imply that the DataRow should be added as new in the database and that the old one should be removed. 

    I don't know if there are currently still databases where you can change (primary key) Id's, it would mean that relation tables should endless times be reconstructed.

    But what you want does not exist in Ado Net.

    AFAIK You have to delete the row and to add a new one if the primary key changes.


    Success
    Cor

    • Marked as answer by stelios84 Sunday, March 4, 2012 6:28 PM
    Saturday, March 3, 2012 9:02 AM
  • hi cor, what you said is what i did in order to bypass my problem and continew working normally, for my case i did not have any relation between my table ,but if i has then yes i should restructure them in order to work ok. But onother user Allen has reply on this post saing that hi did excatly the same with Office 2010 and no problem occures. how ever i guess that is nothing else to do  ,the problem is gone by creating onother table with my proper fields that i want , and then i loop for all the records i have to insert them into the new table and then remove the old table.

    Am really thank you for your time .

    stelios ----------


    • Edited by stelios84 Sunday, March 4, 2012 6:28 PM
    Sunday, March 4, 2012 6:27 PM
  • Stellios, 

    Allen is not updating in his code, he is inserting every time new, as the primary key is datetime you can do that endless times (it is in Net precise in 100th-nanoseconds.. 

    A normal way to create log files but has noting to do with updating in the sense of database accessing.


    Success
    Cor

    Sunday, March 4, 2012 7:28 PM