dbconcurrencyexception when inserting record into Access database

[dawatson]

I'm using VB 2008 with an Access database.
A form has a bound datagridview control that has a tableadapter/bindingsource for the its datassource.

I'm having a problem when inserting a new record. The record has a primary key which is an Access autonumber datatype.

There is a dbconcurrencyexception thrown.

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

I'm the only only user using the database. So the error isn't occuring because another user has accessed the same record.

I've researched the issue and found a solution that required code to be added to dataset xsd file to return the new primary key autonumber field.
This code is working correctly. The correct primary key is returned.
The record is added after the first cell in the grid has a value entered, and the focus moves to the next cell. However, after entering a value for the next cell and moving the focus again, the dbconcurrency error occurs.

I've checked both the datagridview column and the field in the bindingsource
and both have the correct primary key/autonumber value for the new record.

I'm at a loss to find a solution. Code below.

Private Sub dgvAccessories_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgvAccessories.CurrentCellDirtyStateChanged
        DirtyAccessory = True
    End Sub

Private Sub dgvAccessories_CellLeave(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvAccessories.CellLeave
        If Not formLoading AndAlso DirtyAccessory = True Then
            SaveAccessory()
            UpdateAccessoryCosts(e.RowIndex)
        End If
    End Sub

 Private Sub SaveAccessory()

        Try
            Validate()
            bsProductAccessories.EndEdit()
            'Error occurs on the next line
            taProductAccessories.Update(DsProducts.ProductAccessories)
            DirtyAccessory = False
        Catch ex As System.Exception
            DirtyAccessory = False
         End Try

    End Sub

-- This code in the dataset xsd file

Namespace dsProductsTableAdapters
Partial Class ProductAccessoriesTableAdapter
Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
Try
  ' Include a variable and a command to retrieve the identity value
 ' from the Access database.
 Dim newID As Integer = 0
 Dim idCMD As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", Me.Connection) ' _connection)

 If args.StatementType = StatementType.Insert Then
  ' Retrieve the identity value and store it in the CategoryID column.
       newID = CInt(idCMD.ExecuteScalar())
        args.Row("ProductID") = newID
        'args.Row.AcceptChanges()
End If
End Sub
End Class

 

[cjard]

Your problem, given that youre trying to insert a new record, is that the InsertCommand is not being used!

If you really are trying to insert, then the InsertCOmmand of the tableadapter is fired to insert the row. If the row.RowState is Modified (rather than Added), then the UpdateCommand will be called.

So right now, youre somehow putting a row in the dataset then modifying it, rather than adding it.. THe TableAdapter thinks it has to run an UPDATE query because your row is modified. No row exists to update, so the concurrency checker code is raising an exception. Examine how the row comes to be in the datatable in the first place, and why it is getting a RowState of Modified, rather than Added

Note1: You shouldnt need to use SELECT @@IDENTITY, the tableadapter wizard should do this for you (see the first page of the wizard, advanced button, "refresh the dataset" radiobutton)

 

[dawatson]

Adapter wizard and insert command

Hi,

Thanks for your reply.

I think you are referring to the "Refresh the data table" checkbox on the Advanced options of the wizard.

When using an Access database, the adapter wizard has greyed out this checkbox and the option isn't available.

For the insert problem, I'll have to check out your reply. After reading the doc and also using the bound controls on other forms, I understood that when tableadapter.update runs it checks to see if the tableadapter is in insert mode or update mode and runs the correct command.

You can verify this by setting up a bound datagridview, create a save button with the code bindingsource.endedit, tableadapter.update(ds.datatable).
Then insert a record in the datagridview, the record is inserted.

Maybe in my code there has been to an insert. I'll check it and get back to you.

Thanks,
DW

 

[dawatson]

This link describes how the tableadapter update statement work.

How to: Update Data Using a TableAdapter

After the data in your dataset has been modified and validated, you probably want to send the updated data back to a database. In order to send the modified data to a database, you call the Update method of a TableAdapter. The adapter's Update method will update a single data table and execute the correct command (INSERT, UPDATE, or DELETE) based on the RowState of each data row in the table.

 

[dawatson]

I've tried to find code that returns the rowstate but can't find it.
Which object do I need the rowstate from? The datagridview?, tableadapter?, bindingsource? How do I return the rowstate?

 

[cjard]

immediately before you call tableAdapter.Update, write this into the Immediate Window:


?myDS.MyTable(0).RowState


If there are many rows in the table, probably better to write this:

?myDS.MyTable.GetChanges(DataRowState.Added).Count
?myDS.MyTable.GetChanges(DataRowState.Modified).Count

 

[cjard]

This link describes how the tableadapter update statement work.

How to: Update Data Using a TableAdapter

After the data in your dataset has been modified and validated, you probably want to send the updated data back to a database. In order to send the modified data to a database, you call the Update method of a TableAdapter. The adapter's Update method will update a single data table and execute the correct command (INSERT, UPDATE, or DELETE) based on the RowState of each data row in the table.

Mmm.. nuisance that MS called it Update() because it has nothing to do with UPDATE query. They should have called it PersistToDB() or something

 

[cjard]

When using an Access database, the adapter wizard has greyed out this checkbox and the option isn't available.

Apologies for giving this advice.. I was reasonably sure that it wasnt greyed out on Access :/

 

[dawatson]

cjard

I've used ?myDS.MyTable(0).RowState for my dataset/table and the return value is always 2-unchanged. Regardless of where I check the value, before or after the endedit and update commands.

I've tried the syntax for myDS.MyTable.GetChanges(DataRowState.Added).Count
and an error 'count' is not a member of 'DataTable' is returned. I couldn't find any other property that would return a valid I could use.

 

[cjard]

How many rows are in the table? If there is only one, your code is OK. If there are more than one, you'll need to get the rowstate of all of them or as you say, use GetChanges.

If GetChanges returns a datatable you should say:

.blah.GetChanges(Added).Rows.Count


A generic datatable (returned from getchanges) doesnt have a Count property, it has a Rows collection, which has a count. A typed datatable generated by the dataset generator shortcuts TypedTable.Count -> baseTable.Rows.Count

 

[dawatson]

I don't think the tableadapter used in bound controls has the count property.
It must be that only datatables have the count property.

 

[dawatson]

I wanted to thank you for your help. I've corrected most of the problems with this issue corrected. I had to add retrieving the primary key for all related grids, and to set up the classes correctly.

 

[cjard]

I don't think the tableadapter used in bound controls has the count property.
It must be that only datatables have the count property.

A tableadapter is a device that transfers data between a strongly typed datatable and a database. It stores no data of its own and hence is not used in bound controls, nor does it have a count property because it holds nothing countable

At the risk of repeating myself: It is only strongly typed datatables that have a .Count property, and this property is a short-cut to the .Count property of the .Rows collection. Hence a standard generic DataTable DOES NOT have a .Count property. It has a .Rows collection that has a .Count property:

stronglyTypedDataTable.Count
weaklyTypedDataTable.Rows.Count

 

[cjard]

I had to add retrieving the primary key for all related grids

The wizard usually warns you if you ahvent retrieved the primary key for a particular table when crafting the SELECT query. Failure to include the PK in the selection list means the wizard is not able to automatically create the UPDATE or DELETE queries, because you are not retrieving any data that can uniquely identify one row