none
LINQ to SQL Insert issue RRS feed

  • Question

  • Hi I am struggling with an insert I am trying to perform using linq to sql. I have two tables related through a foreign key.

    Table 1 - 'Employee Header' has primary key employeeID

    Table 2 - 'Contact Details' has primary key rowID, and foreign key employeeID

    I have set both primary keys to auto-increment, but I am not sure how to set the foreign key value to replicate the primary key in the Employee Header table.

    The insert is initiated by pressing the save button on the form and my code is below:

    Private


    Sub ContactDetailBindingNavigatorSaveItem_Click( ByVal sender As System. Object , ByVal e As System. EventArgs ) Handles ContactDetailBindingNavigatorSaveItem.Click

     

    Dim db As New TigerERMSDataContext

     

    Dim EH As New EmployeeHeader

    EH.dateCreated = DateCreatedDateTimePicker1.Value

    EH.createdBy = CreatedByTextBox1.Text

    EH.status = StatusTextBox1.Text

     

    Dim CD As New ContactDetail

    CD.fName = FNameTextBox.Text

    CD.lName = LNameTextBox.Text

    CD.dateofBirth = DateCreatedDateTimePicker1.Value.ToShortDateString

    CD.age = AgeTextBox.Text

    CD.phone = PhoneTextBox.Text

    CD.email = EmailTextBox.Text

    CD.street = Street1TextBox.Text

    CD.street1 = Street1TextBox.Text

    CD.city = CityTextBox.Text

    CD.state = StateTextBox.Text

    CD.postcode = PostcodeTextBox.Text

    CD.country = CountryTextBox.Text

    CD.emergencyContact = EmergencyContactTextBox.Text

    CD.emergencyContactDet = EmergencyContactDetTextBox.Text

    db.EmployeeHeaders.InsertOnSubmit(EH)

    db.ContactDetails.InsertOnSubmit(CD)

    db.SubmitChanges()


    MessageBox .Show( "New record saved" )

     

    I would really appreciate some guidance on what I am missing here so I can make this work. I am only starting with vb and this has proved a real head scratcher for me.

    Thanks in advance,

    Blair

    • Edited by nzblair Thursday, March 10, 2011 10:45 AM presentation
    Thursday, March 10, 2011 10:40 AM

Answers

  • You're not linking your ContactDetail with your EmloyeeHeader. You need to add this line :

    CD.EmployeeHeader = EH;
    

    In that case you don't need db.EmployeeHeaders.InsertOnSubmit.

    Below you'll find a little example with products and categories from NorthWind. First I create a new Category, then a product and I set the Category-property of the Product to the newly created Category. I only call db.Products.InsertOnSubmit. If you check the database, you will see that both Category and Product have been saved to the database.

    using (var db=new NorthWindDataContext())
    {
      Category newCategory = new Category()
      {
        CategoryName = "Beer",
        Description = "Nice Beer"
      };
    
      Product newProduct = new Product()
      {
        Category = newCategory,
        ProductName = "WestVleteren 12",
        QuantityPerUnit = "6",
        ReorderLevel = 2,
        UnitPrice = 5,
        UnitsInStock = 4,
        UnitsOnOrder = 2
      };
    
      db.Products.InsertOnSubmit(newProduct);
      db.SubmitChanges();
    }
    
    

    (Pieken)Puil
    Thursday, March 10, 2011 7:04 PM
  • Hello Blair,

    There are two common reasons why this happens:

    (1) You are calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter.

    (2) The other common problem is that in Visual Studio, there is an option to copy your database file to the output folder of your project. Usually this is done with an Access MDB file, or a SQL Server MDF file, when you add the database file to your project.

    For these solutions, please see this article:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/316380ba-ceaf-493a-a54c-1f978b6a1322

    I hope this can help.

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 16, 2011 5:01 AM
    Moderator

All replies

  • you need to first add child entity to parent. E.g

    db.EmployeeHeader.Add(cd)

    and then try to perform db.EmployeeHeader.InsertOnSubmit(EH)

    Thursday, March 10, 2011 5:31 PM
  • You're not linking your ContactDetail with your EmloyeeHeader. You need to add this line :

    CD.EmployeeHeader = EH;
    

    In that case you don't need db.EmployeeHeaders.InsertOnSubmit.

    Below you'll find a little example with products and categories from NorthWind. First I create a new Category, then a product and I set the Category-property of the Product to the newly created Category. I only call db.Products.InsertOnSubmit. If you check the database, you will see that both Category and Product have been saved to the database.

    using (var db=new NorthWindDataContext())
    {
      Category newCategory = new Category()
      {
        CategoryName = "Beer",
        Description = "Nice Beer"
      };
    
      Product newProduct = new Product()
      {
        Category = newCategory,
        ProductName = "WestVleteren 12",
        QuantityPerUnit = "6",
        ReorderLevel = 2,
        UnitPrice = 5,
        UnitsInStock = 4,
        UnitsOnOrder = 2
      };
    
      db.Products.InsertOnSubmit(newProduct);
      db.SubmitChanges();
    }
    
    

    (Pieken)Puil
    Thursday, March 10, 2011 7:04 PM
  • Thanks for the responses, I got it working.

    However, this has led me to a new issue... Although the submitchanges code seems to be working I cannot see any new rows in the database.

    Any ideas on what I might be missing here?

    Blair

    Sunday, March 13, 2011 9:43 AM
  • Hello Blair,

    There are two common reasons why this happens:

    (1) You are calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter.

    (2) The other common problem is that in Visual Studio, there is an option to copy your database file to the output folder of your project. Usually this is done with an Access MDB file, or a SQL Server MDF file, when you add the database file to your project.

    For these solutions, please see this article:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/316380ba-ceaf-493a-a54c-1f978b6a1322

    I hope this can help.

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 16, 2011 5:01 AM
    Moderator