Weird dbconcurrency exception!

[Bassio]

Can somebody please help me?

My data adapter's update method uses a delete command stored procedure
but what happens is that only the first datarow marked for deletion is
deleted while the rest of the datarows throw a dbconcurrency exception
and the result is only one record deleted!!

Procedure:
procDelApp(pAppID INT) AS DELETE * FROM App WHERE AppID = pAppID;

Code:
Me.BindingContext(ds.Tables("App")).RemoveAt(pos)

Dim da As New OleDbDataAdapter

da.DeleteCommand = PrepareDeleteCommand_App

Try

da.Update(ds.Tables("App").GetChanges(DataRowState.Deleted))

Catch exc As Exception

Debug.Print(exc.Message)

End Try

 

[Cor Ligthert [MVP]]

Bassio,

The removeAt removes a datarow completly from a datatable.

Therefore it is not even possible to update (delete) that row

Use a "delete" method
http://msdn.microsoft.com/library/d...ml/frlrfsystemdatadatarowclassdeletetopic.asp

I hope this helps,

Cor

 

[Mark Ashton]

OleDbDataAdapter.Update will throw a DBConcurrencyException on the first row
that didn't affect any records.

Listening to the adapter RowUpdatedEvent may help by being able to examine
what command was executed, its parameter values and the number of
RecordsAffected. If RecordsAffected is 0 the DBConncurrencyException will
be thrown. (if -1 then either a SELECT statement was executed or 'set
nocount on' had happened).

 

[W.G. Ryan MVP]

Bassio - are you using RemoveAt with Rows that haven 't yet been added to
the datatable AND had acceptChanges called on them? B/c if you call
RemoveAt on rows that have only been added but you didn't call
AcceptChanges, then they'll be removed from teh collection.
private void button2_Click(object sender, System.EventArgs e)

{

DataSet ds = new DataSet("TestDataSet");

DataTable dt = new DataTable("Test");

ds.Tables.Add(dt);

DataColumn dc = new DataColumn("TestColumn", typeof(System.Int32));

dt.Columns.Add(dc);

DataRow dro = dt.NewRow();

dro[0] = 1;

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = 2;

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = 3;

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = 4;

dt.Rows.Add(dro);

dt.AcceptChanges(); //If you call this, then dt.Rows.Count will = 4

// And HasChanges will be true after the RemoveAt.

//However, if you don't call AcceptChanges, then it will be 2 and Yes
respectively.

BindingManagerBase bmb = this.BindingContext[dt];

bmb.RemoveAt(0);

bmb.RemoveAt(1);

MessageBox.Show(dt.Rows.Count.ToString());

MessageBox.Show(ds.HasChanges().ToString());

}

Could this be your problem?

 

[Bassio]

Thanks for the replies, but i am not deleting rows that haven 't yet
been added to
the datatable.

And i used the delete method, but with the same results.

And i already added a handler to the onrowupdated event:
Public Sub HandleRowUpdated(ByVal sender As Object, _
ByVal e As OleDbRowUpdatedEventArgs)

If e.StatementType = StatementType.Delete And e.Status =
UpdateStatus.ErrorsOccurred Then
e.Row.RejectChanges()
End If

End Sub

What happens is that when the event is fired for the first time it
doesn't catch an error, but when the second row begins it catches an
error !!!

Adding to the weirdness: I gave up and tried using ad-hoc sql
statement:
DELETE * FROM App WHERE AppID = ?;

What happened that it worked fine .. so what is the matter with the
stored procedure?

 

[Cor Ligthert [MVP]]

Bill,

In my opinion (as I know you have done in far past as well) are you mixing
up remove and delete.

RemoveAt removes direct from a table (it is the same as delete with after
that accept changes)
(It is not recomended to use that).

Delete removes directly a row from a table when it has the rowstate added
(can be another word)
Otherwise it stays in a table and get the rowstate deleted (can be another
word)

After an acceptchanges or an dataadapter.update from the table, all rows
with the rowstate "deleted" are removed than from the datatable.

Or I should understand what you write wrong.

Cor