how asp handle primary key violation and re-try insert statement?

Discussion in 'ASP General' started by John, Jul 8, 2006.

  1. John

    John Guest

    The ASP application inserts transaction records in transaction table
    with the system time as the primary key. However, it is possible to
    have primary key violation because the records in transaction table
    come from different sources. The application can show error message and
    the user can file a transaction again manually, but I want the
    application can have multiple re-tries to perform insert statement
    until there is no primary key violation.

    Here's the code, but I get stuck what code should i put to re-try the
    insert statement, a for loop?and perhaps re-try 3 times before showing
    the primary key violation error message to the user?

    <%
    set conn = CreateObject("ADODB.Connection")
    conn.open "<connection string>"
    sql = "insert into p_tran values (convert(varchar, GETDATE(), 109),
    amount)"
    conn.execute(sql)

    if err.number <> 0 then
    Response.Write err.description
    end if

    conn.close
    set conn = nothing

    %>

    Errors
    ========
    Microsoft OLE DB Provider for SQL Server error '80040e2f'
    Cannot insert duplicate key row


    Please advise. thanks!!
    John, Jul 8, 2006
    #1
    1. Advertising

  2. "John" <> wrote in message
    news:...
    > The ASP application inserts transaction records in transaction table
    > with the system time as the primary key. However, it is possible to
    > have primary key violation because the records in transaction table
    > come from different sources. The application can show error message and
    > the user can file a transaction again manually, but I want the
    > application can have multiple re-tries to perform insert statement
    > until there is no primary key violation.
    >
    > Here's the code, but I get stuck what code should i put to re-try the
    > insert statement, a for loop?and perhaps re-try 3 times before showing
    > the primary key violation error message to the user?
    >


    No you should change you design and stop using the system time as a primary
    key.

    > <%
    > set conn = CreateObject("ADODB.Connection")
    > conn.open "<connection string>"
    > sql = "insert into p_tran values (convert(varchar, GETDATE(), 109),
    > amount)"
    > conn.execute(sql)
    >
    > if err.number <> 0 then
    > Response.Write err.description
    > end if
    >
    > conn.close
    > set conn = nothing
    >
    > %>
    >
    > Errors
    > ========
    > Microsoft OLE DB Provider for SQL Server error '80040e2f'
    > Cannot insert duplicate key row
    >
    >
    > Please advise. thanks!!
    >
    Anthony Jones, Jul 8, 2006
    #2
    1. Advertising

  3. John

    Mike Brind Guest

    John wrote:
    > The ASP application inserts transaction records in transaction table
    > with the system time as the primary key. However, it is possible to
    > have primary key violation because the records in transaction table
    > come from different sources. The application can show error message and
    > the user can file a transaction again manually, but I want the
    > application can have multiple re-tries to perform insert statement
    > until there is no primary key violation.
    >
    > Here's the code, but I get stuck what code should i put to re-try the
    > insert statement, a for loop?and perhaps re-try 3 times before showing
    > the primary key violation error message to the user?
    >
    > <%
    > set conn = CreateObject("ADODB.Connection")
    > conn.open "<connection string>"
    > sql = "insert into p_tran values (convert(varchar, GETDATE(), 109),
    > amount)"
    > conn.execute(sql)
    >
    > if err.number <> 0 then
    > Response.Write err.description
    > end if
    >
    > conn.close
    > set conn = nothing
    >
    > %>
    >
    > Errors
    > ========
    > Microsoft OLE DB Provider for SQL Server error '80040e2f'
    > Cannot insert duplicate key row
    >
    >


    On Error Resume Next
    Do Until Err.Number = 0
    conn.execute(sql)
    Loop

    ....although this is just a sticking plaster over the gaping wound.
    Raising errors is expensive, and using a PK that's likely to raise this
    sort of error is poor design. Change your PK to something that's much
    more likely to be unique.

    --
    Mike Brind
    Mike Brind, Jul 8, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    3
    Views:
    2,421
    Mark Rae
    Mar 23, 2006
  2. John Salerno
    Replies:
    20
    Views:
    820
    John Salerno
    Aug 11, 2006
  3. Anon
    Replies:
    0
    Views:
    1,356
  4. Replies:
    4
    Views:
    367
    Roedy Green
    Jul 14, 2007
  5. Lionel
    Replies:
    9
    Views:
    3,367
Loading...

Share This Page