Column is constrained to be unique. Value 123 already

I'm using sqlDataAdpater to call a stored procedure for insert
statements.
The stored procedure inserts into all columns except primary key, which
it then selects back at end.
The program does a number of these inserts and then reads back from
database, this all works okay. Later when I go to do more inserts I
get the error message
Column is constrained to be unique. Value 123 already
Below is the stored procedure.
Any help would be greatly appreciated.
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int =null output, @.SelectionOrig_ID int =null,
@.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
=null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
=null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
@.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
)
as
insert into [dbo].[Selection]
(
SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
string2, string3, string4, string5, string6, string7, Hidden, NameLong,
Team_ID, Percentage
)
values
(
@.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
@.Hidden, @.NameLong, @.Team_ID, @.Percentage
)
select * From Selection Where Selection_ID = @.@.IDENTITY
GOChange to this:
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- --
select @.Selection_ID Selection_ID
Return(0)
-- ---
but also consider the following, you can use the SP to do both Inserts and
Updates, switching based on whether or not you pass in a null or non-null
@.Selection_ID as follows
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
-- ---
This has the benefiy of ONLY Updating the columns for which you actually
pass non-null values to the SP.
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Missed one word in Raiserror statement
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has deleted or removed Selection %d.',
16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
"CBretana" wrote:
> Change to this:
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- --
> select @.Selection_ID Selection_ID
> Return(0)
> -- ---
> but also consider the following, you can use the SP to do both Inserts and
> Updates, switching based on whether or not you pass in a null or non-null
> @.Selection_ID as follows
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> If @.Selection_ID Is Null
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- ---
> Else If Exists (Select * From [dbo].[Selection]
> Where Selection_ID = @.Selection_ID)
> Update [dbo].[Selection] Set
> SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
> SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
> Event_ID = IsNull(@.Event_ID, Event_ID),
> Name = IsNull(@.Name, Name),
> Type = IsNull(@.Type, Type),
> Odd = IsNull(@.Odd, Odd)
> string1 = IsNull(@.string1, string1),
> string2 = IsNull(@.string2, string2),
> string3 = IsNull(@.string3, string3),
> string4 = IsNull(@.string4, string4),
> string5 = IsNull(@.string5, string5),
> string6 = IsNull(@.string6, string6),
> string7 = IsNull(@.string7, string7),
> Hidden = IsNull(@.Hidden, Hidden),
> NameLong = IsNull(@.NameLong, NameLong),
> Team_ID = IsNull(@.Team_ID, Team_ID),
> Percentage = IsNull(@.Percentage, Percentage)
> Where Selection_ID = @.Selection_ID
> -- ---
> Else
> Begin
> Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
> Return(-1)
> End
>
> Select @.Selection_ID Selection_ID
>
> Return(0)
> -- ---
> This has the benefiy of ONLY Updating the columns for which you actually
> pass non-null values to the SP.
> "dermot" wrote:
>
> "dermot" wrote:
>|||Script out the entire table if you could, just to make sure. I assume there
is no triggers, and that you know where the value 123 comes from?
If you can repeat the operation, use profiler and capture a trace of it
failing. Post that and we can see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1109971489.057346.206560@.l41g2000cwc.googlegroups.com...
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.