asked on

Code inserts duplicate records

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

Nasir Razzaq

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.

sammySeltzer

ASKER

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?

Nasir Razzaq

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

If RecordAlreadyExistsInHistory() = False Then
InsertIt()
End If

sammySeltzer

ASKER

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.

sammySeltzer

ASKER

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.

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial