Click here to Skip to main content
15,749,072 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone,

I have multiple datagridviews on my form in a tabcontrol, each one contain the following columns: "ref", "Bestand zu KHK (HW)", "Bestand Menge" and "Bestandsart".

the problem is that "ref" contains repeated values, what I want to do is remove all the duplicated values and sum the corresponding "Bestand zu KHK (HW)", "Bestand Menge" into one.

What I have tried:


Dim i = t1 - 1
     Do Until i = t2
         Dim j = 0
         Do Until j = datagridviews(i).RowCount

             If Not datagridviews(i).Rows(j).Cells("ref").Value.Equals(0) Then
                 Dim s1
                 Dim s2

                 s1 = datagridviews(i).Rows(j).Cells("Bestand zu KHK (HW)").Value
                 s2 = datagridviews(i).Rows(j).Cells("Bestand Menge").Value

                 For t = j + 1 To datagridviews(i).RowCount - 1
                     If datagridviews(i).Rows(j).Cells("ref").Value.Equals(datagridviews(i).Rows(t).Cells("ref").Value) And datagridviews(i).Rows(j).Cells("Bestandsart").Value.Equals(datagridviews(i).Rows(t).Cells("Bestandsart").Value) Then
                         s1 = s1 + datagridviews(i).Rows(t).Cells("Bestand zu KHK (HW)").Value
                         s2 = s2 + datagridviews(i).Rows(t).Cells("Bestand Menge").Value

                         datagridviews(i).Rows(t).Cells("ref").Value = 0
                         datagridviews(i).Rows(t).Cells("Bestandsart").Value = 0
                         datagridviews(i).Rows(t).Cells("Bestand Menge").Value = 0
                         datagridviews(i).Rows(t).Cells("Bestand zu KHK (HW)").Value = 0

                     End If
                 datagridviews(i).Rows(j).Cells("Bestand zu KHK (HW)").Value = s1
                 datagridviews(i).Rows(j).Cells("Bestand Menge").Value = s2

                 j = j + 1

             ElseIf datagridviews(i).Rows(j).Cells("ref").Value.Equals(0) Then
                 j = j + 1
             End If

         BackgroundWorker1.ReportProgress((i - (t1 - 1) + 1) / (t2 - t1 + 1) * 100)
         i = i + 1

once I click a button to execute the code, I don't get an error but it returns partial sums meaning that I still see some repeated values and I have to press one more time to do the job.

currently there is 21 datagridviews and each contains around 1100 row.

can someone please help or point in the right direction.

thanks in advance.
Updated 22-Jun-17 7:15am

1 solution

You could use a .Datasource for your grid and use an SQL query with DISTINCT, example:
string customers = "SELECT DISTINCT * FROM Customers";

using (SqlConnection con = new SqlConnection("Data Source=MY-PC;Initial Catalog=northwind;Integrated Security=True"))
	DataSet ds = new DataSet();
	SqlDataAdapter da = new SqlDataAdapter(customers, con);
	da.Fill(ds, "Customers");
	dataGridView.AutoGenerateColumns = true;
	dataGridView.DataSource = ds;
	dataGridView.DataMember = "Customers";

It is also possible to set a filter on a DataSet (does not have to be a database query).

Imports System
Imports System.Xml
Imports System.Data
Imports System.Data.SqlClient

Public Class Program
    Public Shared Sub Main()
        Dim customers As String = "SELECT DISTINCT * FROM Customers"
        Using con As SqlConnection = New SqlConnection("Data Source=MY-PC;Initial Catalog=northwind;Integrated Security=True")
            Dim ds As DataSet = New DataSet()
            Dim da As SqlDataAdapter = New SqlDataAdapter(customers, con)
            da.Fill(ds, "Customers")
	    dataGridView.AutoGenerateColumns = true
	    dataGridView.DataSource = ds
	    dataGridView.DataMember = "Customers"
        End Using
    End Sub
End Class
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900