Handling a duplicate index value on insert
Dave
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
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'.
Mark Rae [MVP]
news:6cfe16ae-95c0-46fa...@g23g2000vbr.googlegroups.com...
> 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?
>
> Microsoft JScript runtime error:
> Sys.WebForms.PageRequestManagerServerErrorException: Cannot insert
> duplicate key row in object 'dbo.EPOCProjects' with unique index
> 'uiProjectNumber'.
>
> Best Practice?
Best practice? There really isn't one in this case.
You don't say what type of object "dbo.EPOCProjects" is but, from its name,
I'm assuming it's a class in either your BOL or DAL which is abstracting a
database interface, and you appear to be trying to insert two records /
objects / projects into the same object / datatable with the same key, which
is violating either the underlying table's primary key or one of its unique
indexes.
Therefore, it would appear that you have two choices, depending on whether
what you're trying to do valid or not. I.e. is it possible according to your
database design to have two projects with the same project number?
If it is, then you need to modify the database to allow it, either by
modifying or removing the unique index or by specifying another field or
(combination of fields) as the table's primary key.
If it isn't, then you need to include additional logic in your BOL and/or
DAL to check for key violation and warn the user accordingly.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
Dave
On Dec 16, 6:47 am, "Mark Rae [MVP]" <m...@markNOSPAMrae.net> wrote:
> "Dave" <Dave.Burk...@Jacobs.com> wrote in message
Mark Rae [MVP]
[please don't top-post]
>> You don't say what type of object "dbo.EPOCProjects" is
> Sorry... dbo.EPOCProjects is a table in SQL Server.
In which case, I've answered your question...
Alexey Smirnov
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)...........
...........
"Alexey Smirnov" <alexey....@gmail.com> wrote in message
news:79808065-95db-4bc4...@z7g2000vbl.googlegroups.com...
Alexey Smirnov
> /////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?