Remove duplicate records from a DataSet

Question

User-507786106 posted

I have a dataset and would like to get only one item per row.  The dataset has duplications and I only need one item not duplications.

Sample Table Data

I only need one item not the duplications - Please help me fix the code below...

1  annualPartyOne

1  annualPartyOne

2  CandySiteOne

2  CandySiteOne

2  CandySiteOne

2  CandySiteOne

3  mansiteOne

Code:

 If ds.Tables(1).Rows.Count > 0 Then
                For Each row As DataRow In ds.Tables(1).Rows
                    Try
                        If row("MyParentID") = 1 Then
                            Me.ParentGroupDT = ds.Tables(1)           ' Get Parent Groups
                        End If
                    Catch ex As Exception
                        Me.errorFlag = True
                        Me.ErrorMessage = ex.Message
                    End Try
                Next
            End If

Table Results should be:  How do I accomplish this...

1  annualPartyOne

2  CandySiteOne

3  mansiteOne

4  bookSheftstwo

Answer 1

User-1831219222 posted

show you sql query to see how it's getting duplicate values.

otherwise, you can do delete from the datarow if find the duplicate value:

public DataTable RemoveDuplicateRows(DataTable dTable, string colName)
{
Hashtable hTable = new Hashtable();
ArrayList duplicateList = new ArrayList();

foreach (DataRow drow in dTable.Rows)
{
if (hTable.Contains(drow[colName]))
duplicateList.Add(drow);
else
hTable.Add(drow[colName], string.Empty);
}

foreach (DataRow dRow in duplicateList)
dTable.Rows.Remove(dRow);

return dTable;
}

protected void Button1_Click(object sender, EventArgs e)
{
string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection conn = new SqlConnection(strConn);
SqlDataAdapter da = new SqlDataAdapter("select * from emp", conn);
DataSet ds = new DataSet();
da.Fill(ds, "Emp");
DataTable dt = ds.Tables["Emp"];
dt = RemoveDuplicateRows(dt, "empname");
GridView1.DataSource = ds.Tables["Emp"].DefaultView;
GridView1.DataBind();
}

more:

http://dotnetguts.blogspot.com/2007/02/removing-duplicate-records-from.html

Answer 2

User-606151534 posted

hi

see this url

http://dotnetguts.blogspot.in/2007/02/removing-duplicate-records-from.html

http://geekswithblogs.net/ajohns/archive/2004/06/24/7191.aspx

http://kellyschronicles.wordpress.com/2008/08/06/eliminate-duplicate-rows-from-dataset-in-vb-net/

thank u

Answer 3

User-507786106 posted

awesome it works - thanks much....

Answer 4

User-1831219222 posted

please mark as answer if my post helps to solve your problem.

thanks!

Answer 5

User-996336415 posted

You can use the following SQL code, which demonstrate one way to make this that you need:

Copy not repeated rows from given table to new one, delete given table and rename second table

with the name of first one.

-- Create database for table (for test)
Create Database tsql;
Use tsql;
-- Create first table
Create table tb
(
    text varchar(30)
)
-- Insert your data into table tb
INSERT INTO tb(text) Values ('annualPartyOne');
INSERT INTO tb(text) Values ('annualPartyOne');
INSERT INTO tb(text) Values ('CandySiteOne');
INSERT INTO tb(text) Values ('CandySiteOne');
INSERT INTO tb(text) Values ('CandySiteOne');
INSERT INTO tb(text) Values ('mansiteOne');

-- Copy not repeated rows into second table
SELECT DISTINCT text INTO tb2
from tb;
-- Delete table tb
drop table tb;
-- Assign the name of second table to first one
sp_rename tb2,tb;
-- Let's see the result
SELECT * from tb;

Probably it will be more convenient to create stored procedure to perform this SQL script

O Zhelezov