April 12, 2004 at 11:49 am
We have an ASP application that went live on 1/1/04. Since it has gone live, there have been (4) instances where duplicate rows have been inserted. Everything in the row is the same except for the key field and created_date date/time stamp which is a few milliseconds different. The User of the application does not the ability to click submit more than one time for an insert. The ASP code does not have loops in them. The Stored Procedures do not have loops in them. I cannot figure out where the iteration can be happening? Also, we cannot replicate the problem. It has happened (4) times on (3) tables. We followed ridged standards during the development of the ASP and the SP code so they are all pretty much the same but the bug does not happen through-out the system.
We are using Windows 2000, IIS 5, and SQL Server 2000 SP 4.
Has anyone heard of a bug like this?
Thanks in Advance for your time,
Steve DiDomenico
Nashua, NH
April 12, 2004 at 12:01 pm
Steve,
I presume that you are using JavaScript to keep the user from clicking the submit button twice (double-clicking). If the user has JavaScript disabled this will no longer be effective, thus allow the undesired event to occur. This can easily be tested by disabling JavaScript in your browser's security settings.
Hope it helps,
David Matthews
Arlington, TX
April 12, 2004 at 1:50 pm
Hi David,
The "submit" is an HTML button. All scripting is done Server Side with VBScript. After a "submit" button is clicked, the insert is done and the page is then redirected to a view of the newly inserted record. There is no way a User could click submit twice. That's why we can't figure out where the iteration is happening.
Steve
April 12, 2004 at 11:47 pm
Hi Steve,
I experienced same problem when I used submit button(image of submit type) and form was posted with use of javascript. When the combination of 2 was used there were duplicate records inserted but when we changed the submit to simple image and used javascript for posting the form the problem was resolved.
Hope this Helps !
April 13, 2004 at 6:33 am
Do any of your tables with duplicates have a text column, and if so is the "textinrow" option turned on (which is not the default)?
Developers here reported a problem with executing an INSERT statement on a table which contains a text column with textinrow option OFF where 2 rows are inserted - the second exactly a duplicate of the 1st except for the identity p.k. column and CreatedDate (different by milliseconds). They were able to reproduce the problem once by executing the INSERT in query analyzer, but the problem is rare. I'm curious to know whether the textinrow option affects this.
Jeff
April 13, 2004 at 7:02 am
Users could always use the BACK button to go back to your form page and click the submit button again. Also if the processing of the form is taking a while, they may click the stop button and re-submit the form. Maybe the page had an ASP error during processing, but after the actual insert and they re-submitted the form page. There are a number of reasons why it's not impossible to re-submit a form that results in dupe records. All are pretty difficult to prevent 100%.
April 13, 2004 at 8:09 am
Others gave you the reason why this occurs, but the way to avoid this situation would be to key off another field within the table and check against sometype of timestamp datetime field and a customer id type to see if a particular customer has within a relativly short timespan (e.g. within a few seconds maybe) already made an entry.
Still not a guarntee but will eliminate the problem a majority of the time. Eg. keying off a customer_id
CREATE TABLE some_table(
customer_id int NOT NULL,
added datetime NOT NULL)
IF NOT EXISTS(SELECT TOP 1 * FROM some_table WHERE customer_id = @customer_ID AND added > DATEADD(s,-1,GETDATE()))
INSERT INTO some_table VALUES (@customer_id, GETDATE())
April 13, 2004 at 11:10 am
for_himanshu – We are not using any javascript, it’s pure html, please see previous post.
burton jb – “text in row” is set to default, off.
milz – The milliseconds are too close together for a User to be doing this by clicking the back button and then clicking a submit button again. It’s an intranet app that runs in a split second time frame. Also the navigation does not return them to a “create” page, it sends them to a “view” page of the new insert. Please see previous post.
Andy – We have given thought to checking for a dup in this fashion. It creates additional coding at the ASP level to be able to report to the User when a “true dup” is being entered.
Thanks for all the posts.
We are now researching “Phantom Inserts.”
April 13, 2004 at 11:49 am
This is an ASP problem, I once had it on a production server, but not my development machine. Here's how I worked around it:
Function SaveRec()
SaveRec = false
' Kludge the bug - check if this session already saved this record
if Session("InsertCheck") <> Empty then
if Session("InsertCheck") = FormDataStamp() then
' This data already inserted successfully, just return with true
SaveRec = True
exit function
end if
end if
' Setup the stored proc input here
err.clear
m_adoCmd.Execute()
if err <> 0 then
' handle error situation
end if
if m_adoCmd(0) = 1 then
SaveRec = True
Session("InsertCheck") = FormDataStamp()
end if
End Function
Function FormDataStamp()
' Save a string of data composed of input fields
FormDataStamp = m_sName & m_sTitle & m_sDivId & m_sAddress
End Function
You know, if Bill Gates had a nickel everytime a developer tackeled one of these bugs, oh wait, he does!
Fred Peters
April 13, 2004 at 4:48 pm
for_himanshu – We are not using any javascript, it’s pure html, please see previous post.
But you are using objects to access your database...
Please describe your ADO or POOL connection type and methods. This could have an effect on your data inserts.
Coach James
April 14, 2004 at 12:43 pm
Andy – We have given thought to checking for a dup in this fashion. It creates additional coding at the ASP level to be able to report to the User when a “true dup” is being entered.
I assumed that you were doing this work within a sproc already...if you were, then you really should not need any ASP code changes, keeping the dup grunt work within
April 14, 2004 at 1:02 pm
Hi Andy,
Thanks for the posts.
Yes, the insert is at the SP level and the code you suggest is simple and it will work - to a degree. As I mentioned before, we did think of this. Then we thought, what if a User enters information that is really a dup and it's not related to the problem I mentioned in my first post? Where is the error message for the User? I think it's reported to the User at the ASP level from a value passed back from the SP.
Before we install any solution, we are trying to find out why this happens.
Thanks again,
Steve
April 15, 2004 at 2:47 pm
We have tracked two of the Four instances of this problem to have happened exactly the same time that an ARCServe Backup was running. This backup is run every two hours while the Users are entering data.
Has anyone ever heard of a backup causing something like this?
Thanks,
Steve DiDomenico, Nashua, NH
April 15, 2004 at 2:54 pm
To n3fp and Coach James. Thanks for the posts. I am looking into your suggestions.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply