Code inserts duplicate records

Posted on 2011-10-27
Last Modified: 2012-05-12
Greetings mates,

I am completely baffled by this. It looks simple, and should have been simple but for some weird reason, it isn't.

It is a bit embarrassing but I have to get this up and running soon.

What I am trying to do basically is when users delete a record, grab the ID of the just deleted record, and insert it into our history table.

This works except that it *always* inserts duplicate records into the History table.

When I run the exact same code in SSMS query window, it works as expected - inserts one record at a time.

This leads me to believe there is some bug in my .net code but I can't see it.

Any help appreciated.

Below is all there is to it:

Thanks alot in advance

    Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
        Dim connStr As String = ConfigurationManager.ConnectionStrings("Connstr").ConnectionString
        Dim cnn As SqlConnection
        Dim cmd As SqlCommand
        Dim sql As String = ""

        ' Indicate whether the delete operation succeeded by grabbing the ID of last deleted record. 

        Dim ID As Integer = CInt(e.Keys(0))
        'So, who deleted a record?. We will find out by looking at the History table
        sql += "Insert into DEDS ([employee_ID],[charity_code],[check_amt],[one_time],[bi_weekly],[cash],[donate_choice],[date_stamp],[amb_approved],[chcknum]) "
        sql += " SELECT History.[employee_ID],[charity_code],[check_amt],[one_time],[bi_weekly],[cash],[donate_choice],[date_stamp],[amb_approved],[chcknum] from History where employee_id ='" & tmpEmplID & "' and year(date_stamp) = year(getdate()) and [ID] = " & ID & " "
        cnn = New SqlConnection(connStr)
        cmd = New SqlCommand(sql, cnn)
    End Sub

Open in new window

Question by:sammySeltzer
    LVL 83

    Expert Comment

    May be the event is firing multiple times. Have you stepped through it?

    One quick fix is to run a select count based on this id and only insert if count 0.
    LVL 28

    Author Comment

    Thanks CodeCruiser.

    Not too keen on that solution though.

    This means that if the count is > 0, it doesn't get inserted into the history table and the a record has already been deleted!

    I think we miss a chance of capturing the deleted record.

    Am I off on a tangent?
    LVL 83

    Expert Comment

    Not sure I understand you. When a record is deleted, you will essentially do this (pseudocode)

    If RecordAlreadyExistsInHistory() = False Then
    End If
    LVL 28

    Author Comment

    Right, I understand what you are trying to do but the problem is duplicate records are inserted concurrently.

    In other words, the insert statement is not adding to an existing record.

    I don't know I am making sense at all. Please tell me if I am not.

    If RecordAlreadyExistsInHistory() = False is telling me ( but I could be wrong) that is the historytable to see if for instance a record with id of 300 already exists there.

    In my case, I am still running a test which means that I first delete any record in the History table before intiating the insert.

    So, infact when I run a debug,  I see something like:

    Insert into histtory table (field1, field2, field3, ...fieldN)
    Select field1, field2, field3, ...fieldN from originalTable where id =300
    Insert into histtory table (field1, field2, field3, ...fieldN)
    Select field1, field2, field3, ...fieldN from originalTable where id =300

    Notice that the 2 insert statements.

    If however, I copy the SAME code to query window, only one record gets inserted.

    There is something in my code, not SQL, that is generated this double insert.
    LVL 28

    Author Comment

    Also, when that happens, record has already been deleted from Orignal table and is no longer retrievable.

    If this happens in actual production environment, I am in big trouble.
    LVL 83

    Accepted Solution

    Set a breakpoint in the code and see how it is executing the code in RowDeleting event.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now