Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Violation of PRIMARY KEY constraint


Violation of PRIMARY KEY constraint

Author
Message
polo.csit
polo.csit
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 561
Hai, ALl

my store procedure is:

CREATE PROC SP_TD_SURVEY_RESULTS
   (
   @pin_sParam               varchar(10) ,   
   @pin_nResultID            int            ,
   @pin_nReferenceID         int            ,
   @pin_nSurveyID            int            ,
   @pin_sConductedPlace      varchar(20)      ,
   @pin_dtConductedPlace      datetime      ,
   @pin_nActionOperatorID      int               ,
   @pin_nMandalID            int               ,
   @pin_nAssemblyID         int               
   Wink
AS
BEGIN

--SV - Save New Agenda
IF @pin_sParam = 'SV'
BEGIN
   IF @pin_nActionOperatorID NOT IN(33,36)
   BEGIN
      EXEC SP_TD_GEN_NEXTID 'GN', 'TD_SURVEY_RESULTS', 'nResultID', @pin_nResultID OUT
            
      INSERT INTO TD_SURVEY_RESULTS (nResultID, nReferenceID,nSurveyID, sConductedPlace, dtConductedPlace , nSortOrder, bActive, nCreatedOperatorID, dtCreated,nMandalID,nAssemblyID)
      VALUES (@pin_nResultID   , @pin_nReferenceID, @pin_nSurveyID, @pin_sConductedPlace, @pin_dtConductedPlace, @pin_nResultID ,'Y', @pin_nActionOperatorID, getdate(),@pin_nMandalID,@pin_nAssemblyID)

      SELECT @pin_nResultID 'nResultID', 'Success' 'sStatus'
   END
   ELSE
      SELECT -999 'nResultID', 'You dont have write Permissions' 'sStatus'
END

END



Above storeprocedure i am using but in these application 50 users are used at a time.
The following the error is comming how to slove that one.......





Error Message:Violation of PRIMARY KEY constraint 'PK_TD_SURVEY_RESULTS'. Cannot insert duplicate key in object 'TD_SURVEY_RESULTS'. The statement has been terminated.
TraceLog:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at TDPORTAL.DataAccess.DAManager.ExecuteDataset(SqlCommand cmdObject) in D:\My Projects\TDPPORTAL2005\05-Development\SourceCode\TDDataAccess\DAManager.cs:line 40


regard's
polaiah M
vikas.saxena
vikas.saxena
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 24
since more than one user is inserting record at a time so in your tabe duplicate values are going in primary key column. you can use transactions to check the error or can use locking so that only one person at a time can use your table...

i guess it will help you
Dbs-887309
Dbs-887309
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 44
Before answering

What is the primary key of your table?

Is that user defined... or generated by any user defined function?
If So,
You may have to use the locking method.

Else

Try to set the primary key of your table with identity Insert - Yes

So it will automatically increment per insert..
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
The answer is the error message. You're trying to insert data that already exists and the primary key constraint, which ensures that the value in a column or columns is absolutely unique across all rows is preventing you. Figure out one of two things. 1) Is the primary key correct? 2) Is the data correct. One of the two is messed up.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
gwinters421
gwinters421
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 16
It looks like the problem is in the [SP_TD_GEN_NEXTID] procedure. If it is getting the next ID by getting the current ID from a table, and is not locking the table to do so, 50 concurrent calls to that SP will get the exact same value.

Can you post the definition of [SP_TD_GEN_NEXTID]?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search