469,267 Members | 1,643 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

delete all rows in a datatable

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.
Nov 21 '05 #1
5 23849
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...b;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" <ir******@yahoo.com>
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.

Nov 21 '05 #2
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.

"Cor Ligthert" <no************@planet.nl> 在郵件
news:%2****************@TK2MSFTNGP09.phx.gbl 中撰寫...
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...b;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" <ir******@yahoo.com>
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.


Nov 21 '05 #3
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

"jeff" wrote:
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.

"Cor Ligthert" <no************@planet.nl> 礎b繞l瞼籀
news:%2****************@TK2MSFTNGP09.phx.gbl 瞻瞻翹繞翹g...
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...b;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" <ir******@yahoo.com>
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.



Nov 21 '05 #4
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/de...de-dz_9lut.asp

http://msdn.microsoft.com/library/de...querytopic.asp

I hope this helps?

Cor
Nov 21 '05 #5
very useful. i now use the executenonquery method.

thanks all.

"Cor Ligthert" <no************@planet.nl> 在郵件
news:OX**************@TK2MSFTNGP09.phx.gbl 中撰寫...
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/de...de-dz_9lut.asp
http://msdn.microsoft.com/library/de...querytopic.asp
I hope this helps?

Cor

Nov 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by baylor | last post: by
2 posts views Thread by Mark Jones | last post: by
2 posts views Thread by Doug Versch | last post: by
4 posts views Thread by papaja | last post: by
5 posts views Thread by rn5a | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.