PrimaryKey Violation Error---SP is working Perfectly

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi everyone ,

I tried to insert XML data into my SQL Table.For that i wrote SP,my SP is perfectly working. I think pblm is in coding side.
At the time of executing the Code, i got error.After completion of this line dAdapter.Fill(dset); statement has not been return,it's directly goes to Catch .Then i got this type of error

error:

SQL

Violation of PRIMARY KEY constraint 'PK_ESS_Discipline'. Cannot insert duplicate key in object 'dbo.EMS_M_Discipline'. The duplicate key value is (DIS000554).
The statement has been terminated.

But in my Table There is no record like 'DIS000554'

Code:

C#

public DataSet uploadToSqlTable(string xmlDoc)
    {

        DataSet dset;
        SqlDataAdapter dAdapter;
        SqlCommand command = new SqlCommand();

        try
        {
            dAdapter = new SqlDataAdapter(spNameForXlUpload, con);
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = con;
            command.CommandText = spNameForXlUpload;
            command.Parameters.Add(new SqlParameter("@XMLItemsDoc", xmlDoc.Trim()));
            //command.Parameters.Add(new SqlParameter("@Disc_Code", Disc_Code));
            
            dAdapter.SelectCommand = command;
            dset = new DataSet();
            dAdapter.Fill(dset);           
            return dset;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }

can anyone help me to do this.

Thank's in advance....

Posted 24-Aug-12 21:04pm

pranathis012

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santhosh Kumar Jayaraman · Answer 1 · 2012-08-24T21:16:00

Solution 1

SQL

Violation of PRIMARY KEY constraint 'PK_ESS_Discipline'. Cannot insert duplicate key in object 'dbo.EMS_M_Discipline'. The duplicate key value is (DIS000554).
The statement has been terminated.

The error message is very clear.
You are trying to insert duplicate value for the primary key..Check Your table EMS_M_Discipline and find whether what ever primary key you pass now is already there in table.

Posted 24-Aug-12 21:16pm

Santhosh Kumar Jayaraman

Comments

[no name] 25-Aug-12 3:24am

I already mentioned in my question there is no duplicate value in my table & my SP is also perfectly working.
If i insert duplicate value means in my SP also it shows the same error na..,but my SP is working perfectly

Santhosh Kumar Jayaraman 25-Aug-12 3:24am

which is the primary key for ur table?

[no name] 25-Aug-12 3:27am

Disc_code

Santhosh Kumar Jayaraman 25-Aug-12 3:29am

WHere you are passing it? I believe its not an identity column..When you are trying to insert what is the value you pass for disc_code?

[no name] 25-Aug-12 3:36am

ALTER PROCEDURE EMS_M_Discipline

@XMLItemsDoc xml,

--@Disc_code VARCHAR(10)
AS
BEGIN

SET NOCOUNT ON

DECLARE @XML_Hndl int,@xPath Varchar(200)

--VARCHAR INCREMENTED BY 1

DECLARE @Disc_Code VARCHAR(10)
DECLARE @NID INT
DECLARE @CNT INT
SET @Disc_Code = (SELECT ISNULL(MAX(DISC_CODE),0) FROM EMS_M_DISCIPLINE)

--THIS GIVE U MAX NUMERIC ID FROM THE ALPHANUMERIC ID
SELECT @NID = CAST(SUBSTRING(MAX(DISC_CODE),4, LEN(MAX(DISC_CODE))) AS INT) FROM EMS_M_DISCIPLINE

--HERE U INCRESE THE VLAUE TO NUMERIC ID BY 1
SET @CNT = @NID + 1

--GENERATE ACTUAL APHANUMERIC ID HERE

SET @Disc_Code = (SELECT REPLACE(@Disc_Code,@NID,@CNT))

set @xPath='/NewDataSet/Table1'

exec sp_xml_preparedocument @XML_Hndl OUTPUT,@XMLItemsDoc

select Disc_Code,Disc_Description,Disc_Short_Desc

into #tempUplodItems
FROM OPENXML(@XML_Hndl,@xPath,2)
with(Disc_Code varchar(15), Disc_Description varchar(100),Disc_Short_Desc varchar(100))

exec sp_xml_removedocument @XML_Hndl

BEGIN TRAN XMLINSERT

INSERT INTO dbo.EMS_M_Discipline
(
Disc_Code, Disc_Description, Disc_Short_Desc, Active_Tag, Created_By, Created_On, Modified_By, Modified_On, Disc_Type
)

SELECT @Disc_Code,Disc_Description,Disc_Short_Desc,'Y',718,GETDATE(),718,GETDATE(),'' from #tempUplodItems

COMMIT TRAN XMLINSERT

END

[no name] 25-Aug-12 3:37am

this is the SP i wrote,plz help me i don't know how to pass the parameters.I'm a fresher plz help to do this.

Thank u..

Santhosh Kumar Jayaraman 25-Aug-12 3:48am

You no need to pass parameter. In your stored proc you take last id and making it as +1, so its fine. But can you put a print next to this
SET @Disc_Code = (SELECT REPLACE(@Disc_Code,@NID,@CNT))
and check what value is coming here and check its already present in database.

[no name] 25-Aug-12 3:53am

YAH , i'm checking that's working fine.

If the previous value is 'DIS000553' means it's increment by 1.
Then @Disc_Code value is 'DIS000554'.

Santhosh Kumar Jayaraman 25-Aug-12 3:57am

I have noticed its just an insert proc right? You are not returning anytables, then why cannot you use ExecuteNonQuery()

command.CommandType = CommandType.StoredProcedure;
command.Connection = con;
command.CommandText = spNameForXlUpload;
command.Parameters.Add(new SqlParameter("@XMLItemsDoc", xmlDoc.Trim()));
cmd.ExecuteNonQuery();

Then select from table and fill it in dataadapter

[no name] 25-Aug-12 4:09am

I'M TRYING THIS also.I got like this type of error
Violation of PRIMARY KEY constraint 'PK_ESS_Discipline'. Cannot insert duplicate key in object 'dbo.EMS_M_Discipline'. The duplicate key value is (DIS000553).
The statement has been terminated.

Santhosh Kumar Jayaraman 25-Aug-12 4:10am

It definitely looks like increment is not happening in your procedure properly

[no name] 25-Aug-12 4:12am

k thnk u