Click here to Skip to main content
15,890,947 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi ,

from my c# code I am getting below error when i try to call my procedure.
I tried to debug it but not getting where is the mistake is exactly ?


{Violation of PRIMARY KEY constraint PK__#B12AD42__19093A2B4B4AF11E. Cannot insert duplicate key in object dbo.@TCategory. The duplicate key value is (0).\r\nThe statement has been terminated.}


SQL
ALTER PROCEDURE [dbo].[spGetAllProjectAndTaskForUser]
	  @EmpID INT 
	, @UserID int
	, @ProjectName NVARCHAR(MAX)
	, @Status XML
	, @Category XML
	, @Priority XML
	, @Location XML	
	, @IsAdvanceSearch BIT = NULL
	, @StartDate DateTime
	, @EndDate DateTime
	, @CreUserEmpID INT 
AS
SET NOCOUNT ON
BEGIN	
    DECLARE @TStatus TABLE(StatusID INT PRIMARY KEY)
	DECLARE @TCategory TABLE(CategoryID INT PRIMARY KEY)
	DECLARE @TPriority TABLE(PriorityID INT PRIMARY KEY)

	IF (@Status IS NOT NULL)
	BEGIN
		INSERT INTO @TStatus(StatusID)
		SELECT DataTable.ItemCol.value('C1[1]','int')
		FROM @Status.nodes('//T/R') AS DataTable(ItemCol)
	END
	ELSE
	BEGIN
		INSERT INTO @TStatus(StatusID)
		SELECT DISTINCT l.LookupID FROM [Lookup] l
		INNER JOIN LookupType lt ON l.Type = lt.LookupTypeID	
		WHERE  lt.Code = 'TaskStatus'
	END

	IF (@Category IS NOT NULL)
	BEGIN
		INSERT INTO @TCategory(CategoryID)
		SELECT DataTable.ItemCol.value('C1[1]','int')
		FROM @Category.nodes('//T/R') AS DataTable(ItemCol)
	END
	ELSE
	BEGIN
		INSERT INTO @TCategory(CategoryID)
		SELECT ToDoCategoryId From ToDoCategory
	END

	IF (@Priority IS NOT NULL)
	BEGIN
		INSERT INTO @TPriority(PriorityID)
		SELECT DataTable.ItemCol.value('C1[1]','int')
		FROM @Priority.nodes('//T/R') AS DataTable(ItemCol)
	END
	ELSE
	BEGIN
		INSERT INTO @TPriority(PriorityID)
		SELECT DISTINCT l.LookupID FROM [Lookup] l
		INNER JOIN LookupType lt ON l.Type = lt.LookupTypeID	
		WHERE  lt.Code = 'TaskPriority'
	END

	SELECT 
	('P' + CAST(p.ProjectID as Varchar(10))) AS ID,
	p.ProjectID,
	0 AS TaskID,
	p.Name,
	p.Detail,
	p.StartDate,
	p.EndDate,
	GetDate() AS AlertDate,
	p.PriorityId as PriorityId,
	lp.Type as PriorityType, 
	lp.Code as PriorityCode, 
	lp.Text as PriorityText,
	p.StatusId as StatusId,
	ls.Type as StatusType, 
	ls.Code AS StatusCode,
	ls.Text AS StatusText,
	p.CategoryID as CategoryID, 
	lc.ToDoCategoryID as CategoryType, 
	lc.Code AS CategoryCode,
	lc.Name AS CategoryText,
	p.Pecentage AS Complete, 
	null AS Weightage, 
	null AS ParentID , 
	0 AS Type , 
	p.CustomerID, 
	c.FirstName + ' ' + c.LastName AS CustomerName,
	p.CustomerLocationID, 
	l.Name AS LocationName,
	ISNULL(c.FirstName,'') + ' ' + ISNULL(c.LastName,'') + ' ' + ISNULL(l.Name,'') AS Assosiation
	FROM Project p
	INNER JOIN [ToDoCategory] lc on p.CategoryID = lc.ToDoCategoryID
	INNER JOIN [Lookup] ls on p.StatusId = ls.LookupID
	INNER JOIN [Lookup] lp on p.PriorityId = lp.LookupID
	INNER JOIN @TCategory lct ON  p.CategoryID = lct.CategoryID
	INNER JOIN @TStatus lcs ON  p.StatusId = lcs.StatusID
	INNER JOIN @TPriority lcp ON  p.PriorityID = lcp.PriorityID
	LEFT OUTER JOIN Customer c on p.CustomerID = c.CustomerID
	LEFT OUTER JOIN CustomerLocation l on p.CustomerLocationID = l.CustomerLocationID
	WHERE p.EmployeeID = @EmpID AND p.IsInActive = 0 
	AND (@ProjectName IS NULL OR (p.Name LIKE '%' + @ProjectName + '%' OR p.Detail LIKE '%' + @ProjectName + '%'))
	AND (@StartDate IS NULL OR p.StartDate >= @StartDate)
	AND (@EndDate IS NULL OR (p.EndDate IS NULL OR p.EndDate <= @EndDate))
	AND (((@IsAdvanceSearch IS NULL OR @IsAdvanceSearch = 0) AND p.Pecentage < 100) OR @IsAdvanceSearch = 1 )
	UNION ALL
	SELECT ('T' + CAST(t.TaskId as Varchar(10))) AS ID,t.ProjectID AS ProjectID,t.TaskId, 
	t.Name,t.Notes,t.StartDate,t.EndDate,AlertDate,
	t.PriorityId,lp.Type as PriorityType,lp.Code AS PriorityCode,lp.Text AS PriorityText,
	t.StatusId,ls.Type AS StatusType,ls.Code AS StatusCode,ls.Text AS StatusText,
	0 as CategoryID, '' as CategoryType, '' AS CategoryCode,'' AS CategoryText,
	t.Pecentage AS Complete, t.Weigtage AS Weightage, ('P' + CAST(p.ProjectID as Varchar(10)))  As ParentID, 1 AS Type,
	0 AS CustomerID, ''  AS CustomerName,
	0 AS LocationID, '' AS LocationName, '' AS Assosiation
	FROM Project p
	INNER JOIN Task t on t.ProjectId = p.ProjectId
	INNER JOIN [Lookup] lp on t.PriorityId = lp.LookupID
	INNER JOIN [Lookup] ls on t.StatusId = ls.LookupID
	INNER JOIN @TCategory lct ON  p.CategoryID = lct.CategoryID
	INNER JOIN @TStatus lcs ON  t.StatusId = lcs.StatusID
	INNER JOIN @TPriority lcp ON  t.PriorityID = lcp.PriorityID
	WHERE p.EmployeeID = @EmpID AND p.IsInActive = 0  AND t.IsInActive = 0
	AND (@ProjectName IS NULL OR (p.Name LIKE '%' + @ProjectName + '%' OR p.Detail LIKE '%' + @ProjectName + '%'))
	AND (@StartDate IS NULL OR p.StartDate >= @StartDate)
	AND (@EndDate IS NULL OR (p.EndDate IS NULL OR p.EndDate <= @EndDate))
	AND (((@IsAdvanceSearch IS NULL OR @IsAdvanceSearch = 0) AND p.Pecentage < 100) OR @IsAdvanceSearch = 1 )
END

GO
Posted
Updated 6-Jan-16 17:48pm
v2
Comments
PIEBALDconsult 6-Jan-16 23:49pm    
Doesn't it also tell you the line number? How about running it in SSMS?

As explained by d@nish you are inserting duplicate categories in @TCategory table.

A quick solution could be to add a distinct clause in the select query

SQL
INSERT INTO @TCategory(CategoryID)
SELECT DISTINCT DataTable.ItemCol.value('C1[1]','int')
FROM @Category.nodes('//T/R') AS DataTable(ItemCol)
 
Share this answer
 
Comments
Torakami 7-Jan-16 1:48am    
Seems to be working now , but do you really think this is correct way to achive or any other best alternative is possible ??
_Asif_ 7-Jan-16 1:57am    
Told you already, its a quick solution, correct way would be to identify why your Category xml has duplicate entry in the first place.
Torakami 7-Jan-16 10:02am    
Anyways ... thanks for your solutions .. It solves my problem .Sometimes quick fixes are better way to deliver your product on time
As the message states, you are trying to insert duplicate values in @TCategory table which is not allowed. Take a look at all the insert statements for this table in your code.

It might be a good idea to check for duplicates before really doing the insert. In case running select statement is something you see as an overhead for whatever reasons, you can add exception handling around the insert commands and handle exceptions like these as per your need.
 
Share this answer
 
All is in the error message:
Quote:
{Violation of PRIMARY KEY constraint PK__#B12AD42__19093A2B4B4AF11E. Cannot insert duplicate key in object dbo.@TCategory. The duplicate key value is (0).\r\nThe statement has been terminated.}
You have a field with a PRIMARY KEY constraint. It means that each row must have a different value for this fields.
You already have a row with value (0) for this field and you try to put a second row with the same value, it is forbidden.
 
Share this answer
 
Comments
Torakami 7-Jan-16 1:47am    
Yes , that i understand . But any way to solve this. How can i check to avoid this.
Patrice T 7-Jan-16 1:51am    
You have to check if the value exist before inserting the new row.
esther ngaire 20-Mar-21 1:36am    
What is value 0
Patrice T 20-Mar-21 2:21am    
Is in error message in question.
esther ngaire 20-Mar-21 1:38am    
How do I check if the value exists

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