[RESOLVED] OleDB Concurrency Violation in MS access
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: [RESOLVED] OleDB Concurrency Violation in MS access

  1. #1
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    [RESOLVED] OleDB Concurrency Violation in MS access

    Hi friends !
    I have a strange error when updating my database
    Database: MS access
    I'm using DataTable, DataAdapter and there is no other user who is able to access the database as it is stand alone on my laptop.

    I'm adding some DataRows and that works fine so after I have added them to the database using DataAdapter and Insert Command I'm using myTable.AcceptChanges() so the table knows this are the actual recods now.

    The table is offline and only filled when starting the program and the DataAdapter is used to update the real database when changes occur.

    Now if i do some changes on that just added data and I want to write this changes back to the database I'm getting an error
    'Concurrency Violation' which normally only should occur if some others access my database, isn't it ?

    If I'm cloing program and reopening it, so I'll get all the added recods read in again into my table and I'm doing the changes after that, then it ll woks properly.

    Any one knows what's I'm doing wrong?
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  2. #2
    Join Date
    Jul 2007
    Location
    Illinois
    Posts
    517

    Re: OleDB Concurrency Violation in MS access

    I have received Concurrency Violations when I tried to add a new row to a table which had a few null values in it. Ive also had it occur when in my DataGridView I only had say for example... 5 columns and the table had 7 columns. Its a pretty generic error sometimes and can boggle the mind.

    I would check to make sure the data that your sending back to the database has the same column count as what the table in the database is expecting.
    R.I.P. 3.5" Floppy Drives
    "I know not with what weapons World War III will be fought, but World War IV will be fought with sticks and stones." - Albert Einstein

  3. #3
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: OleDB Concurrency Violation in MS access

    Quote Originally Posted by RaleTheBlade View Post
    . Its a pretty generic error sometimes and can boggle the mind..
    You are such of right. It confuses a lot, especilly becaue it only occurs when I add the data and without restarting the application trying to change some of the just added data. Like for example a person does an entry and when it was done and stored he detects that he has done an error and corrects the data. Just a simple situation which always could happpen.
    Quote Originally Posted by RaleTheBlade View Post
    . ...I would check to make sure the data that your sending back to the database has the same column count as what the table in the database is expecting.
    Yea, there are no null fields and as I'm working with the DataTable, DataAdapter always all columns are used to be updated.

    I really dont get it into my mind.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  4. #4
    Join Date
    Jul 2007
    Location
    Illinois
    Posts
    517

    Re: OleDB Concurrency Violation in MS access

    Could you try these steps?

    1) Add some new data and save it to the data source
    2) Open the data source and check to see that the data has been added
    3) Restart the application and make sure the data shows up in the data grid

    It sounds like your OleDbAdapter is getting messed up and isnt able to update the records in the table. Do you have any sort of a unique primary key in the table? I remember having this same problem with an Access 2000 database and Visual Basic. I could add a new row of data and save it and it would update just fine, but when I went to edit that data the application went ape. I fixed it by removing my data-bound TextBox's and replacing them with a DataGridView and it worked fine...
    R.I.P. 3.5" Floppy Drives
    "I know not with what weapons World War III will be fought, but World War IV will be fought with sticks and stones." - Albert Einstein

  5. #5
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: OleDB Concurrency Violation in MS access

    Quote Originally Posted by RaleTheBlade View Post
    Could you try these steps?

    1) Add some new data and save it to the data source
    2) Open the data source and check to see that the data has been added...
    They are added fine
    Quote Originally Posted by RaleTheBlade View Post
    3) Restart the application and make sure the data shows up in the data grid
    ...
    If I do so all my classes are refilled again, DataTable keeps the new rows and all shows up correct in my Text and Listboxes ( its not databound to a grid )
    Then I can add a new set of data or edit, just as I want, but storing and editing again after storing, or adding a set of data, inserting it to the datasource and then editing them will also make troubles.

    BTW After updateing the data I'm doing mytable.AcceptChanges();
    Googeling one with a similar 'udate after update' or 'update after insert' fails problem wrote he got rid of it by rereading the stored data after update.

    I had thought, that AceptChanges() should have actualized the DataTable in a way all Data are actual and updated.
    But it seems not t be that way.
    Quote Originally Posted by RaleTheBlade View Post
    Do you have any sort of a unique primary key in the table?
    Yes sure and BTW its a 2003 database. Tomorrow I'll try to set the update command by use of Paramteres myself instead of using automatted creation of the CommandBuilder and the method how it creates the query. It seems me it compares each field of data instead only using the primary key in the where clause. And I'll tryto find out how the commandbuilder decides which form of where clause to use.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  6. #6
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: OleDB Concurrency Violation in MS access

    FOUND !!

    This is quite a damned system bug and should really be known.So I post it here I found it by simple adding the Update delegate of the DataAdapter and have gone through the whole prozess step by step

    In my database the Primary key is AutoValue so it increments automatically. But because of I'm still testing and I had removed lots of records it starts the first record added with an autoIndex of lets say 183 when I add the record in the MS Access Datatable.
    But this autoincrement value is not automatically added to the datatable there it obviously knows it is an AutoIncrement Value and it also knows its the fist enty in the table so it starts its autoincrement with zero.
    This way in my datatable I got records with primay keys 0 to 10
    while in the access the Autoincrement isn't resetted so there we start with 183 and end with 193

    When restarting the application, no problem as then it rereads all the records and they have the correct autoincremented Primary keys, if I just go on and edit the records then it tries to update records with Primary keys from 0 to 10 and could not find them.
    So it states Concurrency violation as for the logic 'the records must have been changed from elsewhere'

    IMHO this should eally be known by MS but i dont know how to report such problems.
    When records are inseted to the database, the datatable really should take care to copy the Pprimary keys when they are autoincrementing and not set by the program. And this should be done automatically.

    Thx for trying to help anyway
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  7. #7
    Join Date
    Jul 2007
    Location
    Illinois
    Posts
    517

    Re: [RESOLVED] OleDB Concurrency Violation in MS access

    Good, glad you fixed it There are quite a few problems youll continue to find with the .NET Framework... stuff that will drive you batty. Like my issue with the SerialPort class throwing an exception I couldnt catch. But its good that you got it resolved
    R.I.P. 3.5" Floppy Drives
    "I know not with what weapons World War III will be fought, but World War IV will be fought with sticks and stones." - Albert Einstein

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center