none
LINQ to SQL class - Insertion problem RRS feed

  • Question

  • Hi Everyone,

    I'm writing a quiz system. In the system, a lecturer can add new papers, new quizzes to the database. Paper ID is my primary key in the Paper table. When I try to add a new paper with an existing paper ID in the class, the system catches the problem --"Violation of primary key constraint 'PK_Paper'. Can't insert duplicate key in 'dbo.Paper'. The statement has been terminated." So I change the Paper ID to a new PaperID that hasn't been inserted in the database. When I click on the insert paper button. I get the same catch feedback again and again. Has anyone had the same problem before? Help please!!

    //Here is the code to add a paper to the database

    public static void addPaper(String username, String paperID, String paperName, String area)
    {
    Paper newP = new Paper()
    {
    PaperID = paperID,
    PaperName = paperName,
    Area = area
    };


    data.Papers.InsertOnSubmit(newP);
    data.SubmitChanges();
    }

    //add paper button clicked

    private void button2_Click(object sender, EventArgs e)
    {
    String paperID = textBox4.Text;
    MessageBox.Show(paperID);
    String paperName = textBox5.Text;
    String area = textBox6.Text;

    try
    {
    if ((paperID == "") || (paperName == ""))
    MessageBox.Show("Please fill up the required fields");
    else
    {
    TSqlCalls.addPaper(username, paperID, paperName, area);
    MessageBox.Show("You have successfully added a new paper " + paperID + " " + paperName);
    textBox4.Clear();
    textBox5.Clear();
    textBox6.Clear();
    }
    }
    catch (Exception fe)
    {
    MessageBox.Show(fe.Message);
    }
    }
    • Changed type June Cui Monday, November 2, 2009 10:37 PM
    • Moved by Xiao-Min Tan – MSFT Thursday, November 5, 2009 6:44 AM (From:SQL Server Express)
    • Moved by Dan Benediktson Friday, November 6, 2009 5:46 PM (From:SQL Server Data Access)
    Monday, November 2, 2009 9:14 PM

Answers

  • Hi Shashuma,

    The exception message is obviously cuased by inserting existing PK into DB.
    Could you use debug to make sure the record of PK that you are inserting?
    May you insert another record in other code block which violate the PK constraint.

    You can also try to call method with clean logic of inserting. For example,

            private void button2_Click(object sender, EventArgs e)
            {

                 Paper newP = new Paper()
                {
                    PaperID = paperID,
                    PaperName = paperName,
                    Area = area
                };
               
                using(Yourcontext data = new Yourcontext())
               {
                data.Papers.InsertOnSubmit(newP);
                data.SubmitChanges();
                }
             }


    Best Regards
    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by June Cui Monday, November 9, 2009 7:57 PM
    Sunday, November 8, 2009 2:30 AM

All replies

  • Hi,

    Since you are using ADO.net to insert values into database, I suggest that you can start a trace in profile, and try to check the inserting statement,  copy it and execute it in SSMS directly, we can know whether the problem occurs in .net side or database side. Besides, you also need to check if there's any other constrain exists in your table.

    Thanks.
    Microsoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, November 6, 2009 3:29 AM
  • Hi Shashuma,

    The exception message is obviously cuased by inserting existing PK into DB.
    Could you use debug to make sure the record of PK that you are inserting?
    May you insert another record in other code block which violate the PK constraint.

    You can also try to call method with clean logic of inserting. For example,

            private void button2_Click(object sender, EventArgs e)
            {

                 Paper newP = new Paper()
                {
                    PaperID = paperID,
                    PaperName = paperName,
                    Area = area
                };
               
                using(Yourcontext data = new Yourcontext())
               {
                data.Papers.InsertOnSubmit(newP);
                data.SubmitChanges();
                }
             }


    Best Regards
    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by June Cui Monday, November 9, 2009 7:57 PM
    Sunday, November 8, 2009 2:30 AM
  • Hi Yichun_Feng,

    Thank you very much for your reply. I haven't had a chance to try out the code you wrote above. I will do it when I get my flash drive. In the mean time, I have a question. If you have to add a try catch to the code you wrote above, how would you do it?
    Sunday, November 8, 2009 4:43 AM
  • Hi Shashuma,

    You can just add around this line,
    data.SubmitChanges();


    Best Regards
    Yichun Feng
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 9, 2009 1:46 AM
  • Thank you very much, Yichun Feng. It works.
    Monday, November 9, 2009 7:57 PM