Handling a duplicate index value on insert

[Dave]

using ASP.Net
using .Net Framework 3.5
using C#
using SQL Server 2005

How does one handle a duplicate index value on insert from an ASP.Net
form that's nested in an UpdatePanel? Best Practice?


Thanks

 

[Dave]

sorry for the double post. The reason is because while inserting a
duplicate I get the following:

Microsoft JScript runtime error:
Sys.WebForms.PageRequestManagerServerErrorException: Cannot insert
duplicate key row in object 'dbo.EPOCProjects' with unique index
'uiProjectNumber'.

 

[Dave]

Sorry... dbo.EPOCProjects is a table in SQL Server.

 

[Guest]

sorry for the double post.  The reason is because while inserting a
duplicate I get the following:

Microsoft JScript runtime error:
Sys.WebForms.PageRequestManagerServerErrorException: Cannot insert
duplicate key row in object 'dbo.EPOCProjects' with unique index
'uiProjectNumber'.

It sounds like you try to insert twice the same value into the column
there a unique index exists. You can solve it by checking

IF NOT EXISTS (SELECT 1 FROM EPOCProjects WHERE ProjectNumber=xx)
INSERT INTO...

or you can create a trigger on insert and check the same thing...

 

[sloan]

/////IF NOT EXISTS (SELECT 1 FROM EPOCProjects WHERE ProjectNumber=xx)
INSERT INTO...//

He said he had Sql Server 2005.

If you ever move up to Sql Server 2008, then lookup the terms "Upsert" (or
"Merge" in BOL).

You can look them up now to learn the concepts, but you don't use the syntax
in 2005.

(Thus the "if not exists" check from Alexey is the correct
syntax/method)...........

............

sorry for the double post. The reason is because while inserting a
duplicate I get the following:

Microsoft JScript runtime error:
Sys.WebForms.PageRequestManagerServerErrorException: Cannot insert
duplicate key row in object 'dbo.EPOCProjects' with unique index
'uiProjectNumber'.

It sounds like you try to insert twice the same value into the column
there a unique index exists. You can solve it by checking

IF NOT EXISTS (SELECT 1 FROM EPOCProjects WHERE ProjectNumber=xx)
INSERT INTO...

or you can create a trigger on insert and check the same thing...

 

[Guest]

/////IF NOT EXISTS (SELECT 1 FROM EPOCProjects WHERE ProjectNumber=xx)
INSERT INTO...//

He said he had Sql Server 2005.

What was wrong with my example?