none
Linq Insert fails RRS feed

  • Question

  •  
     I am new to to using Linq and am trying to run an insert query. I continually am getting the error message
    Cannot insert the value NULL into column 'patientid_i', table 'webportalDb.dbo.studyTbl'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated

    I have a .dbml that I am using as my contextobject.  In this is my studytbl that is being inserted into. patientid_i column is a foreign key in the studytbl and is set to not allow nulls on purpose.

    I can see the generated sql in sql profiler when the code runs and the problem is the sql generated does not include patientid. Therefore it tries to insert a null for patientid (which is against the rules) when the row is created and fails. In my code however I do infact set this and i can step through the code and see that there is a value assigned to it at runtime. However the sql that linq generates is no good.What must I do to get the patientid field included into the generated sql?  

    Looked at this article which appeared very similar although i am not using .edmx (http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/61472a56-414f-4419-8582-7c8a9c7eb96e)

    Below is my code any help would be greatly appreciated.
    Jason
    ---------------------------------------------------------------------------------
    eCrystalPSGDBDataContext psgDB = new eCrystalPSGDBDataContext();
     studyTbl study = new studyTbl();
                        study.createdt_dt = DateTime.Now;
                        study.createuser_vc = HttpContext.Current.User.Identity.Name;
                        study.patientid_i = Convert.ToInt32(Request.QueryString["PatientID"]);
                   
                        study.studydt_dt = Convert.ToDateTime(StudyDateTextBox.Text);
                        study.studystatusid_i = (int)EStudyStatus.ESS_SCHEDULED;
                        study.studytypeid_i = 33;
                        study.lastmoddt_dt = DateTime.Now;
                        study.lastmoduser_vc = HttpContext.Current.User.Identity.Name;
                        study.note1_vc = "this record was inserted by LINQ";
                        study.primarycarephysicianid_i = Convert.ToInt32(PhysicianDDList.SelectedValue);
                        psgDB.studyTbls.InsertOnSubmit(study);
                        psgDB.SubmitChanges();

    Jason
    Wednesday, June 23, 2010 10:25 PM

Answers

  • I am not exactly sure where the issue was but I ended up simply taking the studytbl out of the .dbml. I readded the table. Saved the .dbml and the proper insert (with patientid) was generated . Error has gone away. Thank you for your suggestions.

    Jason


    Jason
    Friday, June 25, 2010 3:17 PM

All replies

  • Is the column marked as auto-generated in the L2S designer, but without a default constraint db-side? If so, change it to not-auto-generated, or give it a default constraint in the db.

     

     

     


     

    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

    Thursday, June 24, 2010 3:10 AM
    Answerer
  • I am not exactly sure where the issue was but I ended up simply taking the studytbl out of the .dbml. I readded the table. Saved the .dbml and the proper insert (with patientid) was generated . Error has gone away. Thank you for your suggestions.

    Jason


    Jason
    Friday, June 25, 2010 3:17 PM