Violation of PRIMARY KEY constraint

  • Hai, ALl

    my store procedure is:

    CREATE PROC SP_TD_SURVEY_RESULTS

    (

    @pin_sParamvarchar(10) ,

    @pin_nResultIDint,

    @pin_nReferenceIDint,

    @pin_nSurveyIDint,

    @pin_sConductedPlacevarchar(20),

    @pin_dtConductedPlacedatetime,

    @pin_nActionOperatorIDint,

    @pin_nMandalIDint,

    @pin_nAssemblyIDint

    )

    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

  • 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

  • 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..

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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]?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply