delete all rows in a datatable

[jeff]

i am using ms access as my database

i can successfully delete a single record from a table using :

targetRow.Delete()
Try
reportDataAdapter.Update(reportDataSet, "table1" )
reportDataSet.AcceptChanges()
Catch ex As OleDbException
reportCompanySitesDataSet.RejectChanges()
MessageBox.Show(ex.Message)
End Try


however, i can't delete all records in a table at once using some simple and
fast way.

what i try is :
reportTable.Rows.Clear()

Try
reportDataAdapter.Update(reportDataSet, "table1" )
reportDataSet.AcceptChanges()
Catch ex As OleDbException
reportCompanySitesDataSet.RejectChanges()
MessageBox.Show(ex.Message)
End Try

it fails (without any runtime error)

if you have an example / suggestion, pls let me know.

thanks.

 

[Cor Ligthert]

Jeff,

You would have to use a for each loop

for each targetRow as datarow in reportdataset.tables("table1").rows
targetRow.delete
next

Your acceptchanges is not needed, because that is a part of the dataadapter,
and read as well this page for the rollback

http://support.microsoft.com/default.aspx?scid=kb;en-us;310351.

When you clear your datatables the rows are removed and therefore not
updated with a rowstate remove.

I hope this helps?

Cor

 

[jeff]

thanks mate for the suggestion.

actually i am using the loop method to delete all records in a table.
is there any other more effective way to do it as looping may not be so good
if the number of record in a table is huge.

many thanks.

 

[Guest]

Hi jeff

Why don't you just use a Command against your database and pass through a
DELETE query? It will certainly execute much faster...

Nigel Armstrong

 

[Cor Ligthert]

Jeff,

actually i am using the loop method to delete all records in a table.
is there any other more effective way to do it as looping may not be so
good
if the number of record in a table is huge.

Did you test how much time it took, a for loop is a very fast command.

I did give you a wrong command you can take this instead however although
that this is fast I would take the other option that I typed beneath it..
\\\\
For i As Integer = dt.Rows.Count - 1 To 0 Step -1
dt.Rows(i).Delete()
Next
///

When you want to delete all rows from a database table than you can use the
SQL delete command for that with a command.executenonquery

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_9lut.asp

http://msdn.microsoft.com/library/d...ledboledbcommandclassexecutenonquerytopic.asp

I hope this helps?

Cor

 

[jeff]

very useful. i now use the executenonquery method.

thanks all.