Going MAD when trying to delete from datatable.

Question

I've tried everything - except the working solution of course. I want to delete some or all rows from a datatable and the update that back to the access database before adding new rows (BTW, adding works fine). I have used foreach () to do row.delete() and also tried datatable.clear(). They both seem to work fine, but the problem arises when I want to update the database through the oleDBdataadapter. int res=oleDbAdaptMatchings.Update(dtMatchings); //Save to database The above line either does nothing in the database or whines about the auto-generated insert/delete/update commands. Yes, I am using the CommandBuilder. The table in question has no PK. Is that really necessary for such a simple procedure? I have tried setting my own PK for the table to avoid the dataadapter whining: DataColumn[] priKeys=new DataColumn[3]; priKeys[0] = dtMatchings.Columns["systemID"]; priKeys[1] = dtMatchings.Columns["nyckellista"]; priKeys[2] = dtMatchings.Columns["cylinderlista"]; dtMatchings.PrimaryKey = priKeys; The adapter couln't care less. So, What code lines are needed for something like this? I have read examples in at least 15 places on the web but nothing works. What is the difference between ...update(datatable) and ...update(dataset)? ARRGH! Please help ! Exactly what lines of code do I need? And why? /Björn

Administrator · Answer

Thanks everyone. Apparently a PK is needed. That helped. I will have to settle for that and change the db design I did not want to change. /Björn

Administrator · Answer

There is only one solution I know about: if you don't have PK on your database table, then don't use CommandBuilder. Create UpdateCommand yourself. Mike

Administrator · Answer

Thanks, but... this may help me see what's going on in the table, but WHY do I need to do si much just to delete a few rows? Please help me on the ADO code side. /Björn

Administrator · Answer

How to use WriteXml (see help): myDataSet.WriteXml("c:\myFile.xml",XmlWriteMode.DiffGram); and see xml file with InternetExplorer ... it contains datatables and changes in them. How to use events (You are interested in deleting): In DataTable: RowChanging, RowChanged, RowDeleting, RowDeleted In DataAdapter: RowUpdating, RowUpdated and an example (as in help): myTable.RowDeleted += new DataRowChangeEventHandler( myDeleteHandler); ... private void myDeleteHandler(object sender,DataRowChangeEventArgs e) { Console.WriteLine( "Row_Deleted Event: name={0}; action={1}", e.Row["name", DataRowVersion.Original], e.Action ); }

Administrator · Answer

Thanks for the hint but I have already tested with/without the AcceptChanges. About the other hints; I am too much of a beginner to understand them. It can't be this hard to delete a few rows from an Access DB! Thanks. /Björn

Administrator · Answer

I don't know what's wrong but I have similar error: last record deleted from datatable sometimes(!) isn't deleted from table. One silly question: may be there's AcceptChanges() method before Update()? Try to watch row status with debugger, by WriteXml(... DiffGram) or events on DataAdapter or DataTable. May be it can help.