Remove row in datagridview if row index contains in a string

I am cycling through a datgridview row to check for duplicates on Badge Numbers, "Clocked  IN" as well as datetime.
I would like to remove the rows once it finds duplicates, but for now, I add the row.index to a string and have a string "1, 3, 5"
Then recycle through the rows and remove the index after I break up the string.
Is there a more efficient way in this approach?

 Dim RowCount As String = ""
        For i = 0 To Me.DataGridView2.RowCount - 1
            If DataGridView2.Rows(i).Cells(8).Value = "Clocked IN" Then

                Dim LID As String = DataGridView2.Rows(i).Cells(0).Value
                Dim jb As String = DataGridView2.Rows(i).Cells(1).Value
                Dim BDG As String = DataGridView2.Rows(i).Cells(3).Value
                Dim CStat As String = DataGridView2.Rows(i).Cells(8).Value
                Dim Date1 As Date = DataGridView2.Rows(i).Cells(7).Value
                Dim d2 As String = Date1.ToString("MM/dd/yy")
                Dim j1 As Integer = Me.DataGridView2.RowCount - 1
                For Each row As DataGridViewRow In DataGridView2.Rows


                    If ((row.Cells(3).Value)) = BDG Then  ' Badge Numbers Agree

                        If (row.Cells(8).Value) = "Clocked OUT" Then ' Status is Clocked Out
                            Dim date2 As Date = row.Cells(7).Value ' Set Date2 to convert
                            If (date2.ToString("MM/dd/yy")) = d2 Then   ' Date Values equal out
                                If (row.Cells(1).Value) = jb Then 'Job Numbers equal out
                                    'If (row.Cells(8).Value) = "Clocked IN" Then

                                    DataGridView2.Rows(i).Cells(10).Value = row.Cells(7).Value ' Set ClockOut to the ScanTime
                                   RowCount = RowCount + row.Index.ToString + ","  ' Add row to remove from gridview as the clock


                                End If

                            End If


                        End If

                    End If


                Next
            End If
        Next
BKennedy2008Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Dustin SaundersDirector of OperationsCommented:
How is the data getting to the DataGridView?  Off the cuff, sounds like you should be cleaning up your results in your select statement.
0
 
BKennedy2008Author Commented:
I am using a SQL view to gather the data, and would have to match that data up as well. I didn't have time to write the SQL View, but the native SQL View is


SELECT     dbo.Tbl_ScannedLabor.EntryID, dbo.Tbl_ScannedLabor.Job#, dbo.tbl_JCJobs.JobName, dbo.Tbl_ScannedLabor.Badge#, dbo.LaborRoster.Name, dbo.SubcontractorRates.Company,
                      dbo.Tbl_ScannedLabor.LaborClass, dbo.Tbl_ScannedLabor.ScannedTime, CASE WHEN (dbo.Tbl_ScannedLabor.ClockInOut)
                      = 'True' THEN 'Clocked IN' ELSE 'Clocked OUT' END AS ClockedStatus
FROM         dbo.LaborRoster INNER JOIN
                      dbo.SubcontractorRates ON dbo.LaborRoster.CompanyID = dbo.SubcontractorRates.CompanyID INNER JOIN
                      dbo.Tbl_ScannedLabor INNER JOIN
                      dbo.tbl_JCJobs ON dbo.Tbl_ScannedLabor.Job# = dbo.tbl_JCJobs.Job# ON dbo.LaborRoster.Badge = dbo.Tbl_ScannedLabor.Badge#
WHERE     (dbo.Tbl_ScannedLabor.IsDeleted = 0)
0
 
Dustin SaundersDirector of OperationsCommented:
So, you can attach a row number to each item to find 'dupes' where ClockInOut, Badge#, and DateTime all match.  Then, you just select the first instance of those.

Here I assign a number to each row to count the dupes (I ordered them by EntryID), then added to the WHERE clause 'AND RN = 1'.  Now I have the unique results.

You can either select out of that to get rid of RN, or what would be easier is just dropping the column after the fact.
SELECT ROW_NUMBER() OVER (PARTITION BY sl.Badge#,sl.ClockInOut,sl.ScannedTime ORDER BY sl.EntryID) AS "RN"
	,sl.EntryID
	,sl.Job#
	,jc.JobName
	,sl.Badge#
	,lr.Name
	,sr.Company
	,sl.LaborClass
	,sl.ScannedTime
	,CASE WHEN (sl.ClockInOut) = 'True' THEN 'Clocked IN' ELSE 'Clocked OUT' END AS "ClockedStatus"
FROM dbo.LaborRoster lr
INNER JOIN dbo.SubcontractorRates sr ON lr.CompanyID = sr.CompanyID
INNER JOIN dbo.Tbl_ScannedLabor sl ON lr.Badge = sl.Badge#
INNER JOIN dbo.tbl_JCJobs jc ON sl.Job# = jc.Job#
WHERE sl.IsDeleted = 0 AND RN = 1

Open in new window


You might want to look in to what is creating these dupe entries though instead of just filtering around them; and potentially clean them out of your database if can be done cleanly or is desired.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Dustin SaundersDirector of OperationsCommented:
FYI you can use the 'CODE' format to wrap your code in a text block to make it more readable.
0
 
Dorababu MSenior Software EngineerCommented:
You can modify this logic as per your need
String searchValue = "somestring";
int rowIndex = -1;
foreach(DataGridViewRow row in DataGridView1.Rows)
{
    if(row.Cells[1].Value.ToString().Equals(searchValue)) //search value will be you required string that is getting from your query
    {
        rowIndex = row.Index;
       DataGridView1.Rows.Remove(DataGridView1.Rows[rowIndex ]);
        break;
    }
}

Open in new window

0
 
BKennedy2008Author Commented:
Thanks Dustin, I took your advice, and instead of trying to manipulate data in a gridview, its best to do it at the SQL level.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.