How to filter rows based on DGV column with a string array?

Question

I am trying to sort through DGV rows and filter out rows where current employee (number) is found anywhere in Employees column array.

I tried below code; messagebox shows correctly first loop "2" "3" "4" 5" then gets kicked out?? (ie doesn't show next loop "3" "5" "6")

I'm left with a blank DGV.

Am I going about this correctly?

 Try
            Using AdptJobOp As New OleDbDataAdapter(sql, con)
                dtJobOp = New DataTable()
                AdptJobOp.Fill(dtJobOp)
For Each drJobOp As DataRow In dtJobOp.Rows                  

                    arr = Split(drJobOp.Item("Employees").ToString, ",")
                    For a As Integer = 0 To arr.Length
                        MessageBox.Show(arr(a).ToString)
                        If arr(a) = number.ToString Then
                            count = True
                        End If
                    Next a

                    If count = True Then
                       dtJobOp.Rows.Remove(drJobOp)
                    End If

                Next drJobOp
 End Using
        Catch ex As Exception
        End Try

Jobop   Employees  Details
1        2,3,4,5   dsdijfij dfjsdofj
2        3,5,6     ajfsdi jsoifjo siddj
3        6,7,3,8   sajdfssdjf ldj

Search Employees = 5 then resulting DGV should be:

Jobop   Employees  Details
1        2,3,4,5   dsdijfij dfjsdofj
2        3,5,6     ajfsdi jsoifjo siddj

Search Employees = 2 then resulting DGV should be:

Jobop   Employees  Details
1        2,3,4,5   dsdijfij dfjsdofj

Answer 1

The problem is that you are removing rows from the datatable inside a ForEach loop of those same rows. You can't use a foreach loop for this.

You have two choices. To remove the rows try something like:

Dim i As Integer = dtJobOp.Rows.Count - 1
While i >= 0
    'Where OKToDelete is whatever your code wants to test on
    If OKToDelete(dtJobOp.Rows(i)) Then dtJobOp.Rows.Remove(dtJobOp.Rows(i))
    'or use dtJobOp.Rows.RemoveAt(i) if you prefer
    i -= 1
End While

But do you really need to remove the rows from the table or just not display them in the DataGridView?

If the latter, then just set the dtJobOp as your DataGridView datasource and then use:

dtJobOp.Select("your filter expression")

to simply select what you want to see. This assumes of course you are able to convert your code above into some simple filter expression, which may not be possible.

Answer 2

(Posted a solution on behalf of the question author).

This answer worked for me:

 Private Function OKToDelete(dr As DataRow) As Boolean
        Dim ans = True
        Dim arr() = Split(dr.Item("Employees").ToString, ",")
        For a As Integer = 0 To arr.Length - 1
            If arr(a).ToString = number.ToString Then
                ans = False
            End If
        Next a
        Return ans
    End Function