Delete Rows from Dataset

Mike_Stevens
Mike_Stevens used Ask the Experts™
on
I am trying to delete rows from a Dataset based on a certain condition being met.  I am using the following code which validates the condition but not all of the rows that show be deleted are deleted.  I am hoping someone can shed some light on what I am doing wrong.

For i As Integer = 0 To ds.Tables("search_result").Rows.Count - 1

               Dim dr As DataRow = ds.Tables("search_result").Rows(i)
           
               If Check_Status(dr("IDNum")) = False Then
                        dr.Delete()
               End If
               
Next i

                ds.Tables("search_result").AcceptChanges()
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I didn't try this but my guess is when you delete a row you are changing the row count so some of the rows are being skipped.  You should probably mark the row for deletion in some way then come back and delete all the marked rows at once.  Maybe programatically add a boolean column and set it to true if it needs deleted.

Commented:
When you delete a row from a datatable or dataset, it marks the RowState appropiately, So in this case your marking these rows as "deleted". After this you would then call an update method to make the changes in your actual database. HOWEVER by explicitly calling the AcceptChanges method of your dataset/datatable, you are changing the RowState on each row back to "Unmodified".
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Author

Commented:
The changes that I am making are dont have to be save back to a dataset.   What i am doing is populating a dataset with records and then if certain records meet a specific criteria i want to remove the from the dataset populate a datagridview with the records in the dataset.

Commented:
Ok, in that case just remove the AcceptChanges line which is essientially Un-deleting your datarow records. If that still doesnt work, try the remove method instead of delete.

Author

Commented:
I removed the acceptchanges with no lock.  cant use remove on datarow

Commented:
If you step thru your code, are you sure any of the rows are being deleted? I would set a break point on Line:  dr.Delete(), just to confirm the delete to start.

Commented:
Also your deleting a variable of the datarow, so not sure if that will be persisted back to your dataset or not. I would do the delete straight from ds.Tables("search_result").Rows(i)

Author

Commented:
Yes...the delete function is deleting the rows that match the criteria for rows that should be deleted.

Commented:
Just guessing but I'm wondering if you have multiple datasets, where your deleting from one dataset but have the datagridview results bound to a different one.

Author

Commented:
Yes...the rows are being deleted

Commented:
Can you attach an example, so I can take a crack at it. Preferably with an xml file since I dont have your db access...

Author

Commented:
The dataset set is being populated correctly...the issue is with removing select rows.  He is the code that does that.

  For i As Integer = 0 To ds.Tables("search_result").Rows.Count - 1

              If Check_Status(ds.Tables("search_result").Rows(0).Item("IDNum").ToString) = True Then
                    ds.Tables("search_result").Rows(i).Delete()
              End If

 Next
Most Valuable Expert 2012
Top Expert 2014
Commented:
I have not read the whole thread but i think you need to change

If Check_Status(ds.Tables("search_result").Rows(0).Item("IDNum").ToString) = True Then

to
If Check_Status(ds.Tables("search_result").Rows(i).Item("IDNum").ToString) = True Then

Notice that Rows(0) has changed to Rows(i)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial