Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > how asp handle primary key violation and re-try insert statement?

Reply
Thread Tools

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

 
 
John
Guest
Posts: n/a
 
      07-08-2006
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!!

 
Reply With Quote
 
 
 
 
Anthony Jones
Guest
Posts: n/a
 
      07-08-2006

"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.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!!
>



 
Reply With Quote
 
 
 
 
Mike Brind
Guest
Posts: n/a
 
      07-08-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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
Hibernate foreign key as primary key Lionel Java 9 08-10-2011 04:58 PM
EJB3 Composite Primary Key based on EJB references' key Anon Java 0 12-08-2006 04:59 PM
getting the value of the primary key on an insert evandela@bigpond.net.au ASP .Net 3 03-23-2006 10:10 AM
trap primary key error (ASP, SP and SQL2000) Mike D ASP General 10 10-22-2004 12:35 PM



Advertisments