[RESOLVED] DATAROW Looping / Deleting

[Wesley008]

Alright, I have this piece of code:

Assignments_SQLSelect()
            Adapter.DeleteCommand = New OleDbCommand("DELETE FROM Assignments WHERE CIndex = @CIndex")
            Adapter.DeleteCommand.Connection = Connection
            Connection.Open()
            With Adapter.DeleteCommand
                .Parameters.Add("@CIndex", OleDbType.Integer, 5, "CIndex")
            End With
            For Each DRow As DataRow In DS.Tables(0).Rows
                If DRow.Item("Student").ToString.Contains(.lstNames.Items.Item(.RIndex).Text) Then
                    DRow = DS.Tables(0).Rows(.RIndex)
                    DRow.Delete()
                End If
            Next
            Adapter.Update(DS)
            DS.AcceptChanges()
            Connection.Close()

So what its suppose to do is Loop Through each Row in the Database and then IF the DRow Contains the .RIndex.Text Item then it will Delete (This is Fine, I did a Debug to see if it was finding the items & doing the IF Statment (and it Did)

Problem: Its only deleting the First Row that Contains that Item, not the Rest. So.. how do I fix this? I tried putting Adapter.Update and DS.ACcept in the LOOP statement but came up with a ERROR.

[stanav]

The problem is with the type of loop you're using... Don't use For Each loop. Use the regular For loop with a loop counter and looping it backwards

Dim DRow As DataRow = Nothing
For i As Integer = DataRow In DS.Tables(0).Rows - 1 To 0 Step -1
      DRow = DS.Tables(0).Rows(i)
      If DRow.Item("Student").ToString.Contains(.lstNames.Items.Item(.RIndex).Text) Then
           DRow.Delete()                
      End If            
Next

[Wesley008]

Why should I be using the For Loop instead of a For Each Loop ? Aren't they the same?

[stanav]

When you use a For Each loop, it internally obtains an enumeration of the items in the collection. Then in your loop, you delete an item, which causes the enueration of the items to change (for example, you delete item0, so item1 is now moved up to become item0) while the loop is still having the old enumeration of the items.
To get around this, you have to use a loop counter and looping backwards, so even the last item was deleted it won't affect the enumeration for the next item.

[Wesley008]

Alright Thank You for the Explaination.