none
Deletes and Updates trying to do Insert RRS feed

  • Question

  • I am running SQL Server 2008 R2 on Windows 7(64 bit).  I have a Visual Stuio C# Windows application that uses LINQ to SQL to add records to 2 different tables and read them back .

    Now I am trying to Update and delete records but for each operation I get a Prim,ary Key Violation - can Not Insert duplicate key.

    Here is my Update Code:

    Email l_EmailRecord = DataBaseVariables.cl_DB.Emails.Single(l_rec => l_rec.EmailId == p_EmailRec.cl_EmailID);
                try
                {

                    l_EmailRecord.ConfigurationId                p_EmailRec.cl_ConfigID;

                    l_EmailRecord.EmailAddress = p_EmailRec.cl_Emailaddress;

                    DataBaseVariables.cl_DB.SubmitChanges();

                } // end of try

                catch (Exception l_Ex)

                {

                    DataBaseVariables.cl_Log.Write_Log_Record(LogFileConsts.C_DATABASE_ERROR, l_Ex.Message);

                    l_Updated = false;

                } // end of catch

     

    Here is my delete code:

                var l_EmailRecord = from l_rec in DataBaseVariables.cl_DB.Emails

                                     where l_rec.EmailId == p_EmailId

                                     select l_rec;

                try

                {

                    foreach (var l_Rec in l_EmailRecord)

                    {

                        DataBaseVariables.cl_DB.Emails.DeleteOnSubmit(l_Rec);

                    }

                    DataBaseVariables.cl_DB.SubmitChanges();

                } // end of try

                catch (Exception l_Ex)

                {

                    DataBaseVariables.cl_Log.Write_Log_Record(LogFileConsts.C_DATABASE_ERROR, l_Ex.Message);

                    l_Deleted = false;

                } // end of catch

     

    There is an insert operation prior to the calls for update and delete.


    Harry T Ulmer
    Saturday, November 26, 2011 1:46 PM

Answers

  • Hi harrytu,

    You can create a new context to do that. I appreciate if you can provide more code here, so we can help you more effectively.

    Best  Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by harrytu Monday, November 28, 2011 7:39 AM
    Monday, November 28, 2011 7:25 AM
    Moderator

All replies

  • Friend,
      I dont think that this code is giving you error! Do you have an insertion code prior to these operations as you mentioned, then probably that is creating the issue...
      Usually, after creating the dbml, if we change the field to primary key, this issue can happen and i faced it. But i have never faced this issue while updation and deletion....

     


    -- Thanks Ajith R Nair
    • Marked as answer by harrytu Saturday, November 26, 2011 3:22 PM
    • Unmarked as answer by harrytu Saturday, November 26, 2011 4:18 PM
    Saturday, November 26, 2011 2:36 PM
  • OK.  That is the problem.  I had a previous insert that failed due to a duplicate primary key.  I catch all exceptions.  My question is how do I clear the previous SubitChanges so they are no longer connected to the context?
    Harry T Ulmer
    Saturday, November 26, 2011 4:20 PM
  • Do you mean like, you want to ignore the previous insert statement?
    -- Thanks Ajith R Nair
    Saturday, November 26, 2011 4:37 PM
  • Yes, because the previous insert is still attached to the context. 

    So when I execute the next SubmitChanges, it tries to do the insert again.


    Harry T Ulmer
    Saturday, November 26, 2011 4:52 PM
  •    I haven't tried this scenario, i could see a Refresh method, but i dont think that it will help.

    Also try,

    yourContext.GetChangeSet().Inserts.Clear();

     

    Lets try; if it didnt workout, we need to re-assign the context....

     

     


    -- Thanks Ajith R Nair
    • Edited by Ajith R Nair Saturday, November 26, 2011 5:11 PM
    Saturday, November 26, 2011 5:07 PM
  • I tried this but got an error:

    Collection is read only.


    Harry T Ulmer
    Saturday, November 26, 2011 6:37 PM
  • Hi harrytu,

    Could you please provide your insert section? I tested on my computer but can't reproduce your isssue, here's my test code:

    class Program
        {
            static void Main(string[] args)
            {
                DataClasses1DataContext context = new DataClasses1DataContext();
                context.Tests.InsertOnSubmit(new Test { name = "123", status = 1 });
                context.SubmitChanges();
                update(context);
                delete(context);
            }
    
            static void update(DataClasses1DataContext context)
            {
                Test test = (from t in context.GetTable<Test>() where t.id == 7 select t).Single();
                test.name = "asdfgasgasdgasdf";
                context.SubmitChanges();
            }
    
            static void delete(DataClasses1DataContext context)
            {
                Test test = (from t in context.GetTable<Test>() where t.id == 9 select t).Single();
                context.Tests.DeleteOnSubmit(test);
                context.SubmitChanges();
            }
            
        }
    

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, November 28, 2011 6:26 AM
    Moderator
  • The problem is that the insert fails with duplicate primary key.  Then when I try to do a subsequent update or delete, they fail with Can not insert due to duplicate primary key.

    From other information I have looked up, it appears as though the context does not get cleared between operations.  I'm trying to figure out how to clear it.


    Harry T Ulmer
    Monday, November 28, 2011 7:18 AM
  • Hi harrytu,

    You can create a new context to do that. I appreciate if you can provide more code here, so we can help you more effectively.

    Best  Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by harrytu Monday, November 28, 2011 7:39 AM
    Monday, November 28, 2011 7:25 AM
    Moderator