Stop Duplicate Records From Enter Table

  • I have an application that works well on fast internet connections but on dial up, the user can push the send button twice.  Until I get that fixed, I wanted to at least stop duplicate submissions from being recieved.

    CODE:  The stored procedure needs to loom something like this, although this has all kind of problems.

    UPDATE _MailIn

    SET Processed = 777

    FROM _MailIn A

    LEFT JOIN MailIn B

    ON A.Username = B.UserName

    WHERE A.Username = B.UserName AND

    A.Operation = B.Operation AND

    A.DateTimeIn <= (B.DateTimeIn less 45 seconds)

    So if a record is received within 45 seconds of a record previously received, that has the same uUserName and Operation, UPDATE Processed to 777.

    _MailIn TABLE:

    SeqId   UserName   Operation   Processed  DateTimeIn

    1       Kim        Add         1          05/01/28 09:00:00 

    2       Kim        Add         1          05/01/28 09:00:47

    3       Kim        Subtract    1          05/01/28 09:01:00

    4       Ali        Subtract    1          05/01/28 09:01:01

    5       Kim        Subtract    0          05/01/28 09:01:30

    #2 is ok because it was received after 45 seconds.

    #3 is ok, even though received within 45 seconds, it was a different "Operation."

    #4 is ok, even though received within 45 seconds, it was a different "UserName."

    #5 should have processed changed to 777.  It was received within 45 seconds of record #3 that has the same Operation and UserName.

    Thanks!

  • I'm thinking something like this.  I used 2000 for testing, changed table name to _fixdate. 

    SELECT

    Seq as Seq,

    UserName as UserName,

    Operation as Operation,

    DateIn as DateIn

    INTO ##TempDupStop

    FROM _FixDate

    WHERE Processed  <> 0

    AND DATEDIFF(ss,DateIn,GETDATE()) < 2000

    UPDATE _FixDate

    SET Processed = 777

    FROM _FixDate A

    LEFT JOIN ##TempDupStop B

    ON A.Username = B.UserName

    WHERE A.Username = B.UserName AND

    A.Operation = B.Operation AND

    A.Processed = 0

    DROP TABLE ##TempDupStop

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply