Results 1 to 5 of 5

Thread: How to ignore saving records that can make duplicate before inserting to database?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    How to ignore saving records that can make duplicate before inserting to database?

    I have to import or copy the records from one database to another. My problem is it continuously insert records even if they are already existing. I want to control inserting duplicate records. I want them to be ignored and only those records that are not existing in database will be inserted. Any suggestion or modification in my code is highly appreciated. Thank you.

    This is my initial code:

    Code:
    Dim con1 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database1.accdb")
        Dim con2 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database2.accdb")
    
        con2.Open()
        Dim CompQuery As String = "SELECT COUNT(*) FROM sampletable WHERE FirstName = @FirstName AND LastName = @LastName"
        Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con2)
        compCommand.Parameters.AddWithValue("@FirstName", "FirstName")
        compCommand.Parameters.AddWithValue("@LastName", "LastName")
        
        If Convert.ToInt32(compCommand.ExecuteScalar()) > 0 Then
        'ignoring duplicate entries msgbox("Some records fail to save because it would create duplicate      
        entries!")
            Exit Sub
        Else
    
            'Create the data adapter with a SelectCommand using the first connection.
            Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, LastName FROM table1 ", con1)
            'Add the InsertCommand with the second connection. 
            da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (FirstName, LastName) VALUES (@FirstName, @LastName)", con2)
            'Add the insert parameters.                                                                                                 
            da.InsertCommand.Parameters.Add("@FirstName", OleDb.OleDbType.VarChar, 50, "FirstName")
            da.InsertCommand.Parameters.Add("@LastName", OleDb.OleDbType.VarChar, 50, "LastName")
                       'Keep the records in a state where they can be inserted into the destination table.
            da.AcceptChangesDuringFill = False
            Dim dt As New DataTable
            'Get the data from the source database.
            da.Fill(dt)
            'Save the data to the destination database.
            da.Update(dt)
            MsgBox("Data Added!")
    
            con1.Dispose()
            con2.Dispose()
    
        End If
    Last edited by ronelpisan; Jun 24th, 2020 at 09:49 AM. Reason: Typographical error in code.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: How to ignore saving records that can make duplicate before inserting to database

    I'm confused... I'm not sure what your code is even supposed to be doing. It looks like it checks a table in one database, to see if ONE given name exists. IF it doesn't then it copies ALL of the names from another table in a DIFFERENT database and inserts them into the first table that was checked. I'm not sure how that makes any sense.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Lively Member
    Join Date
    Jun 2018
    Posts
    80

    Re: How to ignore saving records that can make duplicate before inserting to database

    Why not just fetch "Firstname" and "Lastname" from DB1 and DB2, concatenate them and compare both lists, if one is different copy user from DB1 to DB2.

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: How to ignore saving records that can make duplicate before inserting to database

    @ronel

    you have asked the same question in this thread and got an answer
    http://www.vbforums.com/showthread.p...ase-to-another

    why open a second thread?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: How to ignore saving records that can make duplicate before inserting to database

    The other thread is better. It doesn't look like this one would contribute enough to make them worth merging, so I just closed this one.

    If you're looking for a magic solution, there isn't one. One way or another, you will have to figure out whether the item is a duplicate and only take action if it is not. The solution that ChrisE provided is probably the way I'd go if this was a large merge. Getting the set of new records (those found in the source and not the destination), then doing inserts only from that result set, is likely to be faster than checking for duplication row by row. Constraints would be useful if this will happen often, but I wouldn't bother with that for a one off.
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width