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

[John]

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!!

 

[Anthony Jones]

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.

 

[Mike Brind]

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.