[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Code inserts duplicate records

Posted on 2011-10-27
6
Medium Priority
?
159 Views
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)
        cnn.Open()
        cmd = New SqlCommand(sql, cnn)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        cnn.Close()
    End Sub

Open in new window

0
Comment
Question by:sammySeltzer
  • 3
  • 3
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37040435
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.
0
 
LVL 29

Author Comment

by:sammySeltzer
ID: 37040529
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37040594
Not sure I understand you. When a record is deleted, you will essentially do this (pseudocode)

If RecordAlreadyExistsInHistory() = False Then
   InsertIt()
End If
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Author Comment

by:sammySeltzer
ID: 37041020
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.
0
 
LVL 29

Author Comment

by:sammySeltzer
ID: 37041023
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.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 37041121
Set a breakpoint in the code and see how it is executing the code in RowDeleting event.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month20 days, 8 hours left to enroll

867 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