Click here to Skip to main content
15,900,725 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi all

Please can anyone help me...
I am getting this error.I am working on a windows application and when I am uploading the file
first time it uploads perfectly but when I upload the same file the upload fails and throws me this exception:


"
SQL
"Violation of UNIQUE KEY constraint 'UniqueVisit'.
Cannot insert duplicate key in object 'dbo.Visit\r\nThe statement has been terminated.
\r\nThe 'Visit_Insert'
procedure attempted to return a status of NULL, which is not allow...



Please can anyone advice me on this I tried but not able to fix the issue.

Here is my table

SQL
CREATE TABLE [dbo].[Visit](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[VerId] [bigint] NULL,
	[ClientId] [bigint] NOT NULL,
	[VisitDate] [datetime] NOT NULL,
	[VisitId] [char](10) NULL,
	[Child] [nvarchar](50) NULL,
	[CurrentMeth] [nvarchar](50) NULL,
	[FollUp] [nvarchar](50) NULL,
	[GestAge] [nvarchar](50) NULL,
	[MedicalRef] [nvarchar](50) NULL,
	[Sensitive] [nvarchar](50) NULL,
	[MarketingRef] [nvarchar](50) NULL,
	[Category] [nvarchar](50) NULL,
 CONSTRAINT [aaclientTransaction_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UniqueVisit] UNIQUE NONCLUSTERED 
(
	[ClientId] ASC,
	[VisitId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Visit]  WITH NOCHECK ADD  CONSTRAINT [FK_clientTransactions_Client] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Client] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Visit] NOCHECK CONSTRAINT [FK_clientTransactions_Client]
GO
ALTER TABLE [dbo].[Visit]  WITH NOCHECK ADD  CONSTRAINT [FK_ServicePointStatistic_Version] FOREIGN KEY([VersionId])
REFERENCES [dbo].[Version] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Visit] CHECK CONSTRAINT [FK_ServicePointStatistics_Version]


Stored Procedure to insert

SQL
ALTER Procedure [dbo].[Visit_Insert]
	@VerId bigint, 
	@ClientId bigint, 
	@VisitDate datetime, 
	@VisitId char(10),
	@Child nvarchar(50),
	@CurrentMeth nvarchar(50),
	@FollUp nvarchar(50),
	@GestAge nvarchar(50),
	@MedicalRef nvarchar(50),
	@Sensitive nvarchar(50),
	@MarketingRef nvarchar(50),
	@Category nvarchar(50),
	@Id bigint OUTPUT
	
AS

insert into Visit  
					(VerId,
					ClientId,
					VisitDate,
					VisitId,
					Child,
					CurrentMeth,
					FollUp,
					GestAge,
					MedicalRef,
					Sensitive,
					MarketingRef,
					Category)
					values
					(@VerId,
					@ClientId,
					@VisitDate,
					@VisitId,
					@Child,
					@CurrentMeth,
					@FollUp,
					@GestAge,
					@MedicalRef,
					@Sensitive,
					@MarketingRef,
					@Category)
SET @Id = @@IDENTITY

RETURN @Id




Thanks
Posted
Updated 29-Jul-13 0:37am
v4
Comments
[no name] 29-Jul-13 6:23am    
Help you with what? The error message tells you exactly what the problem is.
babli3 29-Jul-13 6:26am    
Can you please suggest me , do I need to change the Stored procedure or table because I am quite new into development... so thought someone could guide me on this.
Thanks
Ankur\m/ 29-Jul-13 6:49am    
Use the 'Reply' link beside the comment to reply to it (the way I did. Notice the indention created by system for a reply). Hitting 'Having Question or Comment' won't notify the original commenter and he may not every know about your reply.
babli3 29-Jul-13 6:50am    
Ok

You have created a unique non-clustered index using the statement CONSTRAINT [UniqueVisit] UNIQUE NONCLUSTERED. This enforces a unique constraint on the index key you specified.

You must be inserting duplicate values in these columns which is giving this error.
 
Share this answer
 
Comments
babli3 29-Jul-13 6:36am    
Thanks ! Actually if I try to upload the same file again then it should delete the old one and update the new one...
Do I need to remove the unique nonclustered index ...sorry i am quite new into development. Thanks
Ankur\m/ 29-Jul-13 6:46am    
Thanks ! Actually if I try to upload the same file again then it should delete the old one and update the new one...
In that case, just update the file content column rather than inserting a whole new row.
Do I need to remove the unique nonclustered index ...
Depend on your requirements - do you want the columns to be unique or not?
I would suggest you read more about unique non-clustered indexes before you decide on anything.
babli3 29-Jul-13 6:50am    
Ok Thanks
Hi,
Problem is not with your table structure or your stored procedure, but problem is in the logic you are using.
If you want your SP to update the existing file or delete and insert then you should change your logic.
your present SP is doing what you have designed it to do.

I would suggest you to use following structure..

if exist(condition)
begin
update statements
end
else
begin
insert statement
end
and about "Do I need to remove the unique non clustered index" I would say that it totally depends on your need. If you don't need unique data in column then you can remove it.
I agree with Ankur.. read little more about constraints..

http://www.w3schools.com/sql/sql_constraints.asp[^]

http://databases.about.com/od/sqlserver/a/constraints.htm[^]

http://odetocode.com/articles/79.aspx[^]
 
Share this answer
 
Comments
babli3 29-Jul-13 7:03am    
Thank You...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900