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

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
Sat Jul 08 06:07:46 CDT 2006


"John" <javacc1@gmail.com> wrote in message
news:1152342285.562838.172410@s13g2000cwa.googlegroups.com...
> 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!!
>

Mike
Sat Jul 08 06:11:33 CDT 2006


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