Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: Remove duplicate from database

    hi
    i have a window application, wherein i have to choose the database,its table and rowfields present in the table at runtime. Now i want to remove all the duplicates from database. my database is access and whichever row field the user chooses the duplicates should be removed leaving the latest updated row. Please help me my code is as below..
    string str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtPath.Text;
    OleDbConnection con = new OleDbConnection(str);

    string t = "select * from " + cmbtables.SelectedItem.ToString();
    OleDbDataAdapter adt = new OleDbDataAdapter(t, str);
    con.Open();
    DataSet ds = new DataSet();


    adt.Fill(ds);

    DataTable dt = ds.Tables[0];
    con.Close();
    dt = RemoveDuplicateRows(dt, cmbRow.SelectedItem.ToString());


    private 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;
    }
    when i use datatable.update it is giving me error pls pls pls help me its urgent

    hoping for your co-operation
    Thank you

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049


    If it is urgent, get a local developer to help you, not post it on a forum!

    What error are you getting and on what line of code?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you provide some sample data and required results? It'd be nice to know things like field names and the rules that make a row duplicate (is it if a single field, e.g. surname, a combination of fields e.g. forename + surname, or an entire row, e.g. all employee details identical that make this a duplicate in your application).
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    P.S. oh and you should definately declare a unique constraint across these columns to stop duplicates being entered in the first place.
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ Definitely!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Oct 2008
    Posts
    2
    Quote Originally Posted by georgev
    Can you provide some sample data and required results? It'd be nice to know things like field names and the rules that make a row duplicate (is it if a single field, e.g. surname, a combination of fields e.g. forename + surname, or an entire row, e.g. all employee details identical that make this a duplicate in your application).
    Thanks
    As my application is working at runtime user is not aware about the row fields present in the table untill he selects the rowfield to delete duplicates. However example is i have a table name Client with row fields ClientName, ClientAddress,ClientPhNum,ClientFaxNum.

    The data can be
    ClientName ClientAdd ClientPhNum ClientFaxNum
    Sarah Iowa 123456 234567
    David Texas 234876 987654
    Sarah Iowa 123456 234567

    so herein sarah data is duplicated. now if user clicks clientphnum then all the duplicate rows having same clientphnum should be removed..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •