Primary Key Violation

Question

I cannot insert record because of primary violation which is find. However I want to update record if the primary or record is duplicate. So how to check if record is duplicate with this table having 4 primary keys? Can someone assist me on how to tackle this. There are four fields that are primary keys in this table. The goal is to update record is exist add record if it doesn't exist. Primary keys are mary below. this.data.Color.Objects.Add( new ControlObject() { COLOR_ID= colorId //primary key COLOR_OBJNAM = colorName //primary key COLOR_Key = colorKey //primary key COLOR_REVISION = colorRevision //primary key Value = value } );

Nikunj Satasiya · Answer

Hello devid You can Check Same Condition by using simple sql statment without using stored procedure in C# by check same condition as per i provided in my first solution... you should check COLOR_ID in your database if you can get COLOR_ID then You shoud write Update statment like update your table_name set .... something like this or else you should insert data like insert into your_table_name .... something like this.

David Smith · Answer

Can you show me that in c sharp code

Manas Mohapatra · Answer

1. Before you add record check if records exists with your primary keys(COLOR_ID COLOR_OBJNAM COLOR_Key COLOR_REVISION). 2. Otherwise use a Stored Procedure which will have condition check for primary keys(As provided 1st answer by Nikunj)

Nikunj Satasiya · Answer

Hello David Smith A Singal Table can contain only one primary key constraint other is unique key. In Your case i think colorId is primary Key right ? If yes then other colornam colorkey colorRevision is unique key and that may have null values and may not be unique. Primary Key is a unique key which is identifier of the record within table. Your main goal is to update record is exist else you want to insert record in database right ? Then You shoud try this stored procedure. CREATE PROCEDURE [dbo].[COLOR_INSERT_UPDATE] @COLOR_ID INT  --primary key @COLOR_OBJNAM VARCHAR ( 50) @COLOR_Key VARCHAR ( 50) @COLOR_REVISION VARCHAR ( 50) @Value VARCHAR ( 50) @ReturnValue INT =0 OUT AS SET NOCOUNT ON IF NOT EXISTS( SELECT COLOR_ID FROM YOUR_TABLE_NAME WHERE COLOR_ID = @COLOR_ID) BEGIN INSERT INTO YOUR_TABLE_NAME ( COLOR_OBJNAM COLOR_Key COLOR_REVISION Value ) VALUES ( @COLOR_OBJNAM @COLOR_Key @COLOR_REVISION @Value ) SET @ReturnValue = @@IDENTITY IF @@ERROR 0 BEGIN SET @ReturnValue = 0 -- 0 IS FOR ERROR END END ELSE BEGIN UPDATE YOUR_TABLE_NAME SET COLOR_OBJNAM = @COLOR_OBJNAM  COLOR_Key = @COLOR_Key COLOR_REVISION = @COLOR_REVISION  Value = @Value WHERE COLOR_ID = @COLOR_ID SET @ReturnValue = 1 IF @@ERROR 0 BEGIN SET @ReturnValue = 0 -- 0 IS FOR ERROR END END