Problem with TableAdapterManager.UpdateAll()

[LucaLinari]

Goodmorning,

I developed a simple database application in order to test the
TableAdapterManager functionalities (Visual Studio 2008 – Framework 3.5 –
language VB); my application connects to an Access DB and I also use a typed
dataset (“DBRelDataSet”).
In my application I have 2 related tables “Autori” (Parent Table) and
“Libri” (Child Table) and I drag and drop these tables in a form (“Form1”) so
they appears as DataGridView.

My first purpose was to retrieve the Access Database counter value
(autonumber) each time the user insert a new record in the DataGridView
(“Autori” or “Libri”). To do this I wrote the following code lines in the
DataGridView_UserAddedRow event:

Private Sub LibriDataGridView_UserAddedRow(ByVal sender As Object, ByVal e
As System.Windows.Forms.DataGridViewRowEventArgs) Handles
LibriDataGridView.UserAddedRow
Try
Me.Validate()
Me.AutoriBindingSource.EndEdit()
Me.LibriBindingSource.EndEdit()

Dim LibriGetChanges As DataTable =
DBRelDataSet.Libri.GetChanges(DataRowState.Added)

If Myconnection.State = ConnectionState.Closed Then
Myconnection.Open()
End If

LibriTableAdapter.Connection = Myconnection
LibriTableAdapter.Update(LibriGetChanges)

DBRelDataSet.Libri.Merge(LibriGetChanges)

Myconnection.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

Private Sub AutoriDataGridView_UserAddedRow(ByVal sender As Object,
ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles
AutoriDataGridView.UserAddedRow
Try
Me.Validate()
Me.AutoriBindingSource.EndEdit()
Me.LibriBindingSource.EndEdit()

Dim AutoriGetChanges As DataTable =
DBRelDataSet.Autori.GetChanges(DataRowState.Added)

If Myconnection.State = ConnectionState.Closed Then
Myconnection.Open()
End If

AutoriTableAdapter.Connection = Myconnection
AutoriTableAdapter.Update(AutoriGetChanges)

DBRelDataSet.Autori.Merge(AutoriGetChanges)

Myconnection.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

AND then:

Namespace DBRelDataSetTableAdapters

Partial Public Class LibriTableAdapter

Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
If e.StatementType = StatementType.Insert Then
Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY",
Myconnection)
e.Row("IDlibro") = CInt(cmdGetIdentity.ExecuteScalar)
e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub

End Class

Partial Public Class AutoriTableAdapter

Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
If e.StatementType = StatementType.Insert Then
Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY",
Myconnection)
e.Row("IDautore") = CInt(cmdGetIdentity.ExecuteScalar)
e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub

End Class

Finally my second purpose was to save all the changes; so in the
BindingNavigatorSaveItem_Click I wrote the following code lines:

Private Sub AutoriBindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
AutoriBindingNavigatorSaveItem.Click
Try
Me.Validate()
Me.AutoriBindingSource.EndEdit()
Me.LibriBindingSource.EndEdit()

If Myconnection.State = ConnectionState.Closed Then
Myconnection.Open()
End If

Me.TableAdapterManager.Connection = Myconnection
Me.TableAdapterManager.UpdateAll(Me.DBRelDataSet)

Myconnection.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

I Have two questions:

1) In order to retrieve the Access autonumber value each time the user
insert a new record in the datagridview I implemented the
DataGridView_UserAddedRow event. Is this method the most correct or there is
another method more efficient ?
2) When I click on the BindingNavigatorSaveItem in order to save the changes
the following error occur if previously I inserted a new line in a
DataGridView:
“Concurrency violation: the UpdateCommand affected 0 of the expected 1
records”
I don’t know to resolve this problem and the cause , can anyone give me
indications ?

Thanks a lot
Luca

--
Luca Linari

[Drummer]

I bind a text box,txtDisclosuresKey, to the key (auto-numbered) and I can
get the value from it. BUT be sure to update the textbox and there you have
it!

Me.txtDisclosuresKey.Update()
Me.txtDisclosuresKey.Refresh()

[LucaLinari]

Thanks but my problem is not resolved !

There is always the error : “Concurrency violation: the UpdateCommand

affected 0 of the expected 1 records”

can anyone test my application and give me suggestions ?

--
Luca Linari

[Ken Foskey]

On Wed, 11 Nov 2009 01:02:01 -0800, LucaLinari wrote:

> Thanks but my problem is not resolved !
>
> There is always the error : “Concurrency violation: the UpdateCommand
> affected 0 of the expected 1 records”
>
> can anyone test my application and give me suggestions ?

I have a number of solutions:

a) Remove optomistic concurrency. Configure, advanced, check box.
Understand the risks first though.

b) remove the primary keys from the update command. (Table adapter,
update sql) Updating the key even to the same value on some DB's raises
problems (this is my common problem)

c) Check the data row state and add it to the table if it is detached.
I was using binding sources and had to move last to get it to add
properly.

I don't have you original post so I hope this helps.

Ken