datatable.clear

[Greg]

Can someone explain to me why the following segment of code will not remove
all rows from the datatable?
My application is a Winforms front end connecting to an Access mdb back end.

Public Sub ClearTable(ByVal pstrTableName As String)
prpGetDataSet.Tables(pstrTableName).Clear()
SaveRecords()
End Sub

Public Sub SaveRecords()
EstablishConnection()

'Update each table in the dataset
For Each dtData As DataTable In prpGetDataSet.Tables
UpdateDataBase(dtData.TableName)
Next
prpGetDataSet.AcceptChanges()

CloseConnection()
End Sub

Private Sub UpdateDataBase(ByVal pstrTblName As String)
Dim dbDataAdapter As New OleDbDataAdapter
dbDataAdapter = New OleDbDataAdapter("SELECT * FROM " & pstrTblName,
prpdbDataConnection)

'dynamically create the insert, update, and delete commands on the
table for the adapter
Dim myDataRowsCommandBuilder As OleDbCommandBuilder = New
OleDbCommandBuilder(dbDataAdapter)

'save the table changes
dbDataAdapter.Update(prpGetDataSet, pstrTblName)
prpGetDataSet.Tables(pstrTblName).AcceptChanges()

myDataRowsCommandBuilder = Nothing
dbDataAdapter = Nothing
End Sub 'UpdateDataSet

A datagrid is bound to the datatable. After the clear method is called, the
datagrid shows that the rows have been removed. However, when I close the
application and restart it, the previously removed rows return. This tells
me that the clear is not being saved to the database.

If I highlight all of the rows in the datagrid, delete them by pressing the
delete key then call the saverecords, they are physically removed. The call
to do this is as follows:
Private Sub SaveData()
Me.BindingContext(.prpGetDataSet, "tblMusic").EndCurrentEdit()
prpMdiParent.prpDataClass.SaveRecords()
End Sub

The only difference is that I call the EndCurrentEdit before calling the
save routine.

What am I missing?

TIA,
Greg

 

[Greg]

Got it,
Changed the ClearTable procedure to:
Public Sub ClearTable(ByVal pstrTableName As String)
For Each dr As DataRow In prpGetDataSet.Tables(pstrTableName).Select
dr.Delete()
Next
SaveRecords()
End Sub

I had tried the for each before, but without the Select at the end of the
tables object. It made the difference. Now how is that supposed to be
intuitive?

Greg

 

[Bernie Yaeger]

Hi Greg,

First of all, 'clear' clears a datatable, not the backend. Datatables are
disconnected - clearing them is no different from not putting anything into
them in the first place. You can say 'select * from dtable where 6 =7' and
then call update and expect that the backend is now an empty table.

Secondly, here's the more usual call:
For Each dr As DataRow In prpGetDataSet.Tables(pstrTableName).Rows
dr.Delete()
Next

That's pretty intuitive to me - .select without arguments simply selected *,
but .rows does that as well and makes more sense, since we're looping
through datarows.

HTH,

Bernie Yaeger