[RESOLVED] OleDB Concurrency Violation in MS access

[JonnyPoet]

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?

[RaleTheBlade]

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.

[JonnyPoet]

. 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.

. ...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.

[RaleTheBlade]

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...

[JonnyPoet]

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

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.

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.

[JonnyPoet]

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

[RaleTheBlade]

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