How to prevent making duplicated rows in datagridview table

[blueye89]

Hello,
Any idea how to do that?

[wes4dbt]

Bound or unbound? You need to FULLY explain what your trying to achieve .

[blueye89]

Bound or unbound? You need to FULLY explain what your trying to achieve .

I used unbounded mode. I made columns and rows programmatically. When datagridview is populated I need to remove all duplicated rows. All rows need to be unique.

I tried to make with this code:

 'Dim i, j As Integer

        'For i = 0 To DataGridView1.Rows.Count - 1
        '    For j = 0 To DataGridView1.Rows.Count - 1
        '        If i.ToString = j.ToString Then
        '        Else
        '            DataGridView1.Rows.RemoveAt(j)
        '        End If
        '    Next
        'Next

Thanks in advance.

[louvelle]

You could use the SQL DISTINCT to remove duplicate values.

[wes4dbt]

You could use the SQL DISTINCT to remove duplicate values.

That's true if the data is from a database. If that's the case, why is it unbound. The question that needs to be answered before we can help is, Where is the data that you use to populate the dgv coming from.

[Zelot]

Why don't you just bind it and if there are duplicates check the object that you are binding to your grid, if there are really duplicates in it then check your query.

[blueye89]

That's true if the data is from a database. If that's the case, why is it unbound. The question that needs to be answered before we can help is, Where is the data that you use to populate the dgv coming from.

The data that I use to populate dgv is in one text document which I opening using "open file dialog". So, everytime when I click on button "open" and shows path of .txt file(everytime is diferent one), and when file is loaded, dgv is populated automaticaly but with lot of duplicates. I need to remove all duplicates by first column and than dgv will show only unique data.

That's all.
Thanks

[Zelot]

Can you post an example content of your textfile.

[blueye89]

Can you post an example content of your textfile.

Textfile consist words separated by ",". For me it's not a problem to split all text by "," and import words into the datagridview. Problem is how to remove duplicates after that. Better solution for me is how to prevent importing of duplicates. Any of solution will be ok.

[wes4dbt]

blueye89,

You keep making us pry the necessary information from you bit by bit. Zelot, asked to see an example of the data but you didn't provide it. The amount of columns or the amount of rows or is it just one column that has to be checked for duplicates. All these things can effect how to solve this problem. Remember, we know absolutely nothing about your project. Seeing an example of the data and the code you use to load the dvg would be very helpful.

[blueye89]

blueye89,You keep making us pry the necessary information from you bit by bit. Zelot, asked to see an example of the data but you didn't provide it. The amount of columns or the amount of rows or is it just one column that has to be checked for duplicates. All these things can effect how to solve this problem. Remember, we know absolutely nothing about your project. Seeing an example of the data and the code you use to load the dvg would be very helpful.

Hello wes4dbt,


Here is a code.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Name As New DataGridViewTextBoxColumn
        Name.Name = "Name"
        Name.HeaderText = "Name"
        Name.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        Name.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        Name.Width = 200
        Name.SortMode = 0
        DataGridView1.Columns.Add(Name)

    End Sub

    Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        'Open TXT
        Dim openFileDialog1 As New OpenFileDialog

        openFileDialog1.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        openFileDialog1.Title = "Text Documents"
        openFileDialog1.Filter = "Text Documents (*.txt)|*.txt"
        openFileDialog1.FilterIndex = 1
        openFileDialog1.RestoreDirectory = True

        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            'Clear DGW
            DataGridView1.Rows.Clear()

            Dim stFilePathAndName As String = openFileDialog1.FileName
            TextBox1.Text = stFilePathAndName
            Dim sr As IO.StreamReader = New IO.StreamReader(stFilePathAndName)
            Dim line As String
            Do While Not sr.EndOfStream
                line = sr.ReadLine()
                Dim aryTextFile() As String
                aryTextFile = line.Split(",")
                For i = 0 To UBound(aryTextFile)
                    DataGridView1.Rows.Add(aryTextFile(i))
                Next i
                If line = Nothing Then Continue Do ' This line skips blanks
            Loop
            sr.Close()
        Else
            openFileDialog1.Dispose()
        End If
    End Sub
    
    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

[blueye89]

I have solution which removes only one duplicate by single click on button.
I need to remove it all.

    For intI As Integer = 0 To DataGridView1.Rows.Count - 1
            For intJ As Integer = intI + 1 To DataGridView1.Rows.Count - 1
                If DataGridView1.Rows(intI).Cells(0).Value = DataGridView1.Rows(intJ).Cells(0).Value Then
                    DataGridView1.Rows.RemoveAt(intI)
                    Exit Sub
                End If
            Next
        Next

[wes4dbt]

Is there some reason you don't remove the duplicates before you load the dgv. Anyway, an Array has a method named "Distinct". This will remove all duplicates.

Dim arrDistinct() as String = aryTextFile.Distinct.ToArray

Then clear the dgv and reload using arrDistinct.

[TnTinMN]

Textfile consist words separated by ",". For me it's not a problem to split all text by "," and import words into the datagridview. Problem is how to remove duplicates after that. Better solution for me is how to prevent importing of duplicates. Any of solution will be ok.

Here is an example based on a DataGridView that has two columns (Name and Age); however the number of columns is not relevant. It creates some data and then loops through that data adding only unique rows to the DataGridView.

Read about the DataGridViewRow.CreateCells Method . In the example code, I add the data as an Object array, but the "values" parameter is defined as a ParamArray that will allow you list each value separately. i.e.

r.CreateCells(dgv1, "Mary", 22)

Private Sub Test()
   ' simulated data 
   Dim dataSource As New List(Of Object()) '= { {"Joe", 24}, {"Joe", 26}, {"Joe", 28}}
   With dataSource
      .Add(New Object() {"Joe", 24})
      .Add(New Object() {"Joe", 26})
      .Add(New Object() {"Joe", 24})
      .Add(New Object() {"Bill", 24})
      .Add(New Object() {"Joe", 24})
   End With
 
   'Simulate reading data
   Dim r As DataGridViewRow
   For Each datagroup As Object() In dataSource
      r = New DataGridViewRow
      r.CreateCells(dgv1, datagroup) ' creates the cells per dgv1 row template and adds values
      If Not RowExists(dgv1, r) Then dgv1.Rows.Add(r) ' add only if row is unique
   Next
End Sub
 
Private Function RowExists(ByVal dgv As DataGridView, ByVal r As DataGridViewRow) As Boolean
   Dim ret As Boolean
   For Each row As DataGridViewRow In dgv.Rows
      If row.IsNewRow Then Continue For
      ret = DgvRowValuesEqual(row, r)
      If ret Then Exit For
   Next
   Return ret
End Function
 
Private Function DgvRowValuesEqual(ByVal r1 As DataGridViewRow, ByVal r2 As DataGridViewRow) As Boolean
   Dim ret As Boolean = (r1.Cells.Count = r2.Cells.Count)
   If ret Then
      For i As Int32 = 0 To r1.Cells.Count - 1
         Dim bothNull As Boolean = (r1.Cells(i).Value Is Nothing) And (r2.Cells(i).Value Is Nothing)
         If Not bothNull Then
            If (r1.Cells(i).Value Is Nothing) OrElse (r2.Cells(i).Value Is Nothing) Then
               ret = False
               Exit For
            ElseIf r1.Cells(i).Value.GetType IsNot r2.Cells(i).Value.GetType Then
               Throw New Exception(String.Format("Cell value types are different. r1 is {0} and r2 is {1}.", r1.Cells(i).Value.GetType.Name, r2.Cells(i).Value.GetType.Name))
            ElseIf Comparer.Default.Compare(r1.Cells(i).Value, r2.Cells(i).Value) <> 0 Then
               ret = False
               Exit For
            End If
         End If 'bothNull
      Next
   End If
 
   Return ret
End Function

[elielCT]

would this work?

Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        'Open TXT
        Dim openFileDialog1 As New OpenFileDialog
 
        openFileDialog1.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        openFileDialog1.Title = "Text Documents"
        openFileDialog1.Filter = "Text Documents (*.txt)|*.txt"
        openFileDialog1.FilterIndex = 1
        openFileDialog1.RestoreDirectory = True
 
        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            'Clear DGW
            DataGridView1.Rows.Clear()
 
            Dim stFilePathAndName As String = openFileDialog1.FileName
            TextBox1.Text = stFilePathAndName
            Dim sr As IO.StreamReader = New IO.StreamReader(stFilePathAndName)
            Dim line As String
            Do While Not sr.EndOfStream
                line = sr.ReadLine()
                Dim aryTextFile() As String
                aryTextFile = line.Split(CChar(","))
                For i = 0 To UBound(aryTextFile)
                    Dim dataRow As New DataGridViewRow
                    dataRow.Cells(0).Value = aryTextFile(i)
 
                    If DataGridView1.Rows.Contains(dataRow) Then Continue For
                    DataGridView1.Rows.Add(dataRow)
                Next i
                If line = Nothing Then Continue Do ' This line skips blanks
            Loop
            sr.Close()
        Else
            openFileDialog1.Dispose()
        End If
    End Sub

[blueye89]

would this work?

Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        'Open TXT
        Dim openFileDialog1 As New OpenFileDialog
 
        openFileDialog1.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        openFileDialog1.Title = "Text Documents"
        openFileDialog1.Filter = "Text Documents (*.txt)|*.txt"
        openFileDialog1.FilterIndex = 1
        openFileDialog1.RestoreDirectory = True
 
        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            'Clear DGW
            DataGridView1.Rows.Clear()
 
            Dim stFilePathAndName As String = openFileDialog1.FileName
            TextBox1.Text = stFilePathAndName
            Dim sr As IO.StreamReader = New IO.StreamReader(stFilePathAndName)
            Dim line As String
            Do While Not sr.EndOfStream
                line = sr.ReadLine()
                Dim aryTextFile() As String
                aryTextFile = line.Split(CChar(","))
                For i = 0 To UBound(aryTextFile)
                    Dim dataRow As New DataGridViewRow
                    dataRow.Cells(0).Value = aryTextFile(i)
 
                    If DataGridView1.Rows.Contains(dataRow) Then Continue For
                    DataGridView1.Rows.Add(dataRow)
                Next i
                If line = Nothing Then Continue Do ' This line skips blanks
            Loop
            sr.Close()
        Else
            openFileDialog1.Dispose()
        End If
    End Sub

It's not working.

dataRow.Cells(0).Value = aryTextFile(i)

[blueye89]

Solution:

   For intI As Integer = DataGridView1.Rows.Count - 1 To 0 Step -1
            For intJ As Integer = DataGridView1.Rows.Count - 1 To intI + 1 Step -1
                If DataGridView1.Rows(intI).Cells(0).Value = DataGridView1.Rows(intJ).Cells(0).Value Then
                    DataGridView1.Rows.RemoveAt(intJ)
                End If
            Next intJ
        Next intI

[elielCT]

sorry i shouldve mentioned it wasnt tested, it was just to give you an idea..

after trying to modify it, i couldnt get it to work correctly.. seems when you compare rows even if they have the same exact values it returns false.. almost as if it was comparing the instance.. even though there is an implicit method to check for instance equality.. so i turned to linq.. after some tries i couldnt get it so i did a little search as found this: http://stackoverflow.com/a/3322497.. so now the code looks like this and it works to prevent duplicates:

Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        'Open TXT
        Dim openFileDialog1 As New OpenFileDialog
 
        openFileDialog1.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        openFileDialog1.Title = "Text Documents"
        openFileDialog1.Filter = "Text Documents (*.txt)|*.txt"
        openFileDialog1.FilterIndex = 1
        openFileDialog1.RestoreDirectory = True
 
        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            'Clear DGW
            DataGridView1.Rows.Clear()
 
            Dim stFilePathAndName As String = openFileDialog1.FileName
            TextBox1.Text = stFilePathAndName
            Dim sr As IO.StreamReader = New IO.StreamReader(stFilePathAndName)
            Dim line As String
            Do While Not sr.EndOfStream
                line = sr.ReadLine()
                Dim aryTextFile() As String
                aryTextFile = line.Split(CChar(","))
                For i = 0 To UBound(aryTextFile)
                    'to hold value used in iteration
                    Dim lineNum = i
                    'to hold duplicate entry, so we can test for it
                    Dim duplicateEntry As IEnumerable(Of Object)
 
                    If Me.DataGridView1.RowCount > 0 Then
                        duplicateEntry = From theRow In DataGridView1.Rows, _
                   theCell In CType(theRow, DataGridViewRow).Cells _
              Where CType(theCell, DataGridViewCell).Value.ToString = aryTextFile(lineNum) _
              Select theCell
 
                        If duplicateEntry.Count > 0 Then
                            Continue For
                        End If
 
                    End If
 
                    DataGridView1.Rows.Add(aryTextFile(i))
                Next (i)
                If line = Nothing Then Continue Do ' This line skips blanks
            Loop
            sr.Close()
        Else
            openFileDialog1.Dispose()
        End If
    End Sub

[Zelot]

Another variation of what @wes4dbt is suggesting. Load your content into an object then use distinct and then bind it to your grid. This will simplify what you are doing.

var contents = new List<String>();
// file read goes here 
var data = contents.Distinct().Select(c => new { Name = c }).ToList();
dgv.DataSource = data;

[blueye89]

Thanks to all, I appreciate you helping me.