locked
Remove duplicate records from a DataSet RRS feed

  • 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

    Wednesday, February 15, 2012 2:29 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 15, 2012 2:30 PM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 15, 2012 2:30 PM
  • User-507786106 posted

    awesome it works - thanks much....Smile

    Wednesday, February 15, 2012 3:16 PM
  • User-1831219222 posted

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

    thanks!

    Wednesday, February 15, 2012 3:17 PM
  • 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

    Wednesday, February 15, 2012 3:35 PM