Concurrency Violation Problem

[fraggelsmurf]

Hi,

I am working on a library database in VB.NET (framework 2.0). My main file has a datagrid connected to a dataset with all the records from an Access 2000 database. I want to be able to edit the data in this datagrid and in the dataset and save the changes to the DB. But i get 3 kind of errors when I add new rows and then delete/modify them:
- Concurrency Violation
- IndexOutOfRangeException (datagridview)
- "Delete row information cannot be accessed through the row"

Since I have used the graphical editor in MS VB 2005 Express Edition to create datagrids, datasets, dataadapters, bindingsources and more. I have little idea what the problem might be. I know that it works if i fill the dataset after each edit but that would slow it down way too much since i got bunch of records and each record has 39 columns.

To update the DB i use following code:

If Me.LibraryDBDataSet.HasChanges Then
            Try
                Me.LIBRARYTableAdapter.Update(Me.LibraryDBDataSet)
            Catch ex As Exception
                MsgBox(ex.Message, , "Save Error")
            End Try
        End If

The key is an incremental integer which i have set to go negative in the datagrid/dataset. When i add a new record, i first save all values except key to DB, then I retrieve the correct key from DB and add it to the dataset/datagrid. When doing this, I can't use the normal update so I use acceptchanges instead (EditDB.LibraryDBDataSet.LIBRARY.AcceptChanges()).

I don't know what to do, I am way to fresh to VB.NET to understand how the ADO.NET architecture works. Please point me to the right direction.

Some extra code:

For deleting:

EditDB.LibraryDBDataSet.LIBRARY.Rows(currentRow).Delete()

For adding new row:

EditDB.LibraryDBDataSet.LIBRARY.Rows.Add(row)

 

[fraggelsmurf]

This thread says that the Configuration Wizard that might be problem
http://www.vbdotnetforums.com/showthread.php?t=7313&highlight=concurrency+violation

If I would write my own update, delete, insert, and select commands. How would I start? Do I need to create connection and open and close?

 

[fraggelsmurf]

I turned off the optimistic concurrency in the configuration wizard and haven't been able to recreate any of the errors since (...enyoing that I have been struggling with this for days and once I start a thread about it, I solve it right away).

 

[cjard]

I turned off the optimistic concurrency in the configuration wizard and haven't been able to recreate any of the errors since (...enyoing that I have been struggling with this for days and once I start a thread about it, I solve it right away).

Turning off OC isnt quite a "solution" - OC prevents you from editing rows that someone else already edited, hence turning it off can lead to problems in multi-user environments. Interestingly, I thought that a datatable associated with an autonumber id field in access, would automatically get its own new value after the query was run

 

[jwh]

I'm not sure if that is the case when using access. It is the case when using SQL Express though.


The difference comes in the ability to perform more than one SQL query in one transaction. (specifically, Access does not support Procedural T-SQL)

You will notice that in the advanced options when setting up the Query, there is a checkbox saying 'Refresh the datatable' - Unfortunately unless Procedural T-SQL is supported, this option is grayed out.

Are you attached to Access for a reason, or are you able to move DB's? I would recommend SQL Express over access 9 times out of 10, and probably SQL Compact edition for that other time!

PS - SQL Compact does not supportt Procedural T-SQL either - hence my knowledge on this one, and it's the reason I lost 2 days on my project trying to get the datatables to refresh their indexes when updated - I gave up and went for SQL Express.