Results 1 to 31 of 31

Thread: Insert records into two related tables

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Insert records into two related tables

    I have two table, Clients and Contacts, there is a one to many relationship betwwen clients to contacts whih has a foreign key.

    I have created this in my dataset with the relationship.

    How can i insert a new record into the clients table and at the same time add a new contact? I have tried updating the client table and catching the Client ID to use in the Contacts foreign key but it doesn't work. Is there a VB object that handles this?
    I am using the fields on the form rather than a datagrid.

    thankas

    john

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Insert records into two related tables

    It's actually very simple, but slightly less simple in certain cases. What database are you using?

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    It will also depend on how you have retrieved the data, TableAdapters/DataAdpters/DataReaders. How you have setup the form(s). If you have a one-to-many (parent/child) relation, how do you display the childs records without using a grid, do you use a second form.

    You need to provide more information and post the relevant code.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Gents,

    Sorry for the delay in replying.

    I've got two tables tblOpportunities and tblOppDetails.
    tblOppDetails has OpportunityID as a foreign key

    I've created a data set with the two tables linked

    Code:
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'save
            'SAVE RECORD
    
            Me.Validate()
            Select Case Me.EditType
    
                Case "New"
    
                    OpportunityCreatedByTextBox.Text = "JCM"
                    OpportunityCreatedDateTimePicker.Text = Now()
                    ClientIDTextBox.Text = Me.CID
                    OpportunityIDTextBox1.Text = Me.OpportunityIDTextBox.Text
    
                Case "Edit"
                    OpportunityIDTextBox1.Text = Me.OpportunityIDTextBox.Text
                    Me.OpportunityUpdatedByTextBox.Text = "JCM"
                    Me.OpportunityUpdatedDateTimePicker.Text = Now()
    
            End Select
    
            Me.TblOpportunityBindingSource.EndEdit()
            Me.TableAdapterManager.tblOpportunityTableAdapter.Update(DsOpps)
    
            If OppId = 0 Then
                Dim dt As DataTable = DsOpps.tblOpportunity.GetChanges(DataRowState.Added)
    
                OpportunityIDTextBox1.Text = dt.Rows(1).Item(0)
    
            End If
    
            Me.TblOppDetailsBindingSource.EndEdit()
    
            Me.TableAdapterManager.UpdateAll(DsOpps)
    
            REM trying to save the second table
            Me.TableAdapterManager.tblOppDetailsTableAdapter.Update(DsOpps.tblOppDetails)
    
            Me.Close()
        End Sub
    the penulimate line is me trying to get the second table in the dataset to save, but so far i can't figure it out.

    A second issue arises when i open the form as NEW and enter a new record in tblOpportunity and wish to get the OpportunityID to save as the foreign key in tblOppDetails. I suppose i could do this as a stored procedure o return the Opportunity ID but i was hoping this could be done via the datasets somehow.

    Please can you advise.

    many thanks

    John

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Insert records into two related tables

    It would be useful to know what database you're using. I probably should have asked about that in my previous post... like I did.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Insert records into two related tables

    And it better not be MS SQL ... because there is no such thing. Options include (but are not limited to):
    SQL Server, Access, MySQL, Oracle, No SQL, text file, Excel...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    SQL server 2012

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Insert records into two related tables

    In the database, set the foreign key to propagate on update and then regenerate your data source. What that means is that, when you get the new ID for a parent record in the DataSet after saving, that value will be automatically pushed to the child records to be saved too. Now you just need to actually get the new parent ID after saving. I can't recall whether there's a way to do that automatically with a typed DataSet but I think that there must be. You need to make sure that the InsertCommand contains a SELECT statement after the INSERT to retrieve the new ID into the PK column, e.g.
    sql Code:
    1. INSERT INTO SomeTable (Column1, Column2) VALUES (?, ?);
    2. SELECT SomeTableId = SCOPE_IDENTITY()

  9. #9

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Thanks

    i can get the new id form the parent record so i assume the code is doing what you have suggested.

    What i can't do is get the child table to save. I have put the id from the parent into the child foreign key field but can i get it to save.

    I image its very very simple but i can't figure it out. I am assuming that because the datatable allows you to have multiple related tables that it takes care of this but i wondering if my reasoning is wrong.

    Code:
     Me.TableAdapterManager.tblOppDetailsTableAdapter.Update(DsOpps.tblOppDetails)
    i am trying tor update the child with this line but whether its DsOpps or DsOpps.tblOppdetails makes no difference it doesn't save.

    Pease advise.

    Thanks

    John

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    Need more information than just "What i can't do is get the child table to save" What happens? If nothing, then say so. If there's an error, what error.

    Also, post one single line of code doesn't tell us anything. Post your current code.

  11. #11
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Insert records into two related tables

    I cant speak much on tableadapter, but using DataAdapter a typical setup may look like this (give or take)

    Code:
    Dim DtSet As New DataSet
    Dim ParentBS, ChildBS As New BindingSource
    
    Using SqlConn As New SqlConnection("connection string")
        Using ParentDA As New SqlDataAdapter("SELCT * FROM ParentTable", SqlConn)
            'Add schema info (will contain your AI info for identity)
            ParentDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ParentDA.Fill(DtSet, "ParentTable")
            ParentBS.DataSource = DtSet.Tables("ParentTable")
        End Using
    
        Using ChildDA As New SqlDataAdapter("SELCT * FROM ChildTable", SqlConn)
            'Add schema info (will contain your AI info for identity)
            ChildDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ChildDA.Fill(DtSet, "ChildTable")
        End Using
    
        With DtSet
            'Create relation
            .Relations.Add(New DataRelation("DataRelation1",
            .Tables("ParentTable").Columns("IdColumn"),
            .Tables("ChildTable").Columns("IdColumn"), False))
    
            ChildBS.DataSource = ParentBS
            ChildBS.DataMember = "DataRelation1"
        End With
    
        'Add a parent record
        With ParentBS
            .AddNew()
            DirectCast(.Current, DataRowView)("SomeColumn") = "something"
            .EndEdit()
        End With
    
        'Add a child record
        With ChildBS
            .AddNew()
            DirectCast(.Current, DataRowView)("SomeColumn") = "something"
            .EndEdit()
        End With
    
    End Using
    
    'Update parent table
    Using SqlConn As New SqlConnection("connection string")
        Using ParentTableDA As New SqlDataAdapter("SELECT * FROM ParentTable", SqlConn)
            Using ParentTableCB As New SqlCommandBuilder(ParentTableDA)
                ParentTableDA.Update(DtSet.Tables("ParentTable"))
            End Using
        End Using
    
        'update child table
        Using ChildTableDA As New SqlDataAdapter("SELECT * FROM ChildTable", SqlConn)
            Using ChildTableCB As New SqlCommandBuilder(ChildTableDA)
                ChildTableDA.Update(DtSet.Tables("ChildTable"))
            End Using
        End Using
    End Using

  12. #12

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Thanks but i'm still not understanding this.

    i've got a Dataset with two tables in it, tblOpportunity and tblOppDetails
    in the data set i have created the relationship between the the two so there is a foreign key in the tblOppDetail

    My code is as follows:
    Code:
     Private Sub FrmOpportunity_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Select Case Me.EditType
                Case "New"
                    Me.TblOpportunityTableAdapter.Fill(DsOpps.tblOpportunity)
                    Me.TblOppDetailsTableAdapter.Fill(DsOpps.tblOppDetails)
    
                    Me.TblOpportunityBindingSource.AddNew()
    
                    Me.TblOppDetailsBindingSource.AddNew()
    
                    ClientIDTextBox.Text = Me.CID
    
                Case "Edit"
                    Me.TblOpportunityTableAdapter.FillBy(DsOpps.tblOpportunity, OppId)
    
                    If OppDetailIDTextBox.Text = "" Then
    
                        REM this line throws an exception if i add the .tblOppdetails to the DSOpps
                        REM if i change it to just DSOpps it doesn't throw an exception but i'm still no further forward
    
                        Me.TblOppDetailsTableAdapter.Fill(DsOpps.tblOppDetails)
                        
                        Me.TblOppDetailsBindingSource.AddNew()
    
                        OppIDTextBox.Text = Me.OppId
                        OppNominalSizeTextBox.Text = "400"
                        OppDetailCreatedByTextBox.Text = "JCM"
                        OppDetailCreatedDateTimePicker.Text = Now()
    
                    End If
    
                    OpportunityUpdatedByTextBox.Text = "JCM"
                    OpportunityUpdatedDateTimePicker.Value = Now()
    
    
            End Select
    If we look just at the edit case for now
    I am opening the form which has the bound fields for both table from the dataset. The code above works correctly with the tblOpportunity
    BUT
    if i try and create a new record for the child i get the following:
    1. the child primary key id is -1 which i presume means its a new record
    2. none of the child fields will populate form my code
    3. if i manually enter data into the fields when i click save


    the save routine is as follows:
    Code:
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'save
            'SAVE RECORD
    
            Me.Validate()
            Select Case Me.EditType
    
                Case "New"
    
                    OpportunityCreatedByTextBox.Text = "JCM"
                    OpportunityCreatedDateTimePicker.Text = Now()
                    ClientIDTextBox.Text = Me.CID
                    OppIDTextBox.Text = Me.OpportunityIDTextBox.Text
    
                Case "Edit"
                    OppIDTextBox.Text = Me.OpportunityIDTextBox.Text
                    Me.OpportunityUpdatedByTextBox.Text = "JCM"
                    Me.OpportunityUpdatedDateTimePicker.Text = Now()
    
            End Select
    
            Me.TblOpportunityBindingSource.EndEdit()
            Me.TableAdapterManager.tblOpportunityTableAdapter.Update(DsOpps)
    
            If OppDetailIDTextBox.Text = "" Then
                Dim dt As DataTable = DsOpps.tblOpportunity.GetChanges(DataRowState.Added)
                OppIDTextBox.Text = OppId 'dt.Rows(1).Item(0)
            End If
    
            OppDetailCreatedByTextBox.Text = "JCM"
            OppDetailCreatedDateTimePicker.Text = Now()
    
            Me.TblOppDetailsBindingSource.EndEdit()
    
            'Me.TableAdapterManager.UpdateAll(DsOpps.tblOppDetails)
    
            REM trying to save the child record
            Me.TableAdapterManager.tblOppDetailsTableAdapter.Update(DsOpps.tblOppDetails)
    
            Me.Close()
        End Sub
    What am i missing?

    thanks

    John

  13. #13

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    on load when i'm trying to add a new child record
    Code:
    Me.TblOppDetailsTableAdapter.Fill(DsOpps.tblOppDetails)
    throws an exception:
    Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

    I find this somewhat confusing (i'm easily confused) because there is no child record yet, i'm creating one so until i save it how can there be a relationship error?

    thanks

    John

  14. #14
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    That error may have nothing to do with the current record your trying to create. You may have a record in the child table with a foreign key that doesn't exist in the parent table.

  15. #15
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    After looking at your code it seems your opening the parent table using a filter "OppId" but when you open the child table you retrieve all the records.
    Code:
    Me.TblOpportunityTableAdapter.FillBy(DsOpps.tblOpportunity, OppId)
    You can't have records in the child table that don't have an associated parent. Not when constraints are enabled.

  16. #16

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Thanks, I know i need the fk for the child record but i think the problem is that i'm not opening the table correctly. How do i open the child table without retrieving all the records?

    I have used the exact same code for both tables and i assumed that once i had the id to put in the fk field that the child would save.

    Thanks

    John

  17. #17

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    ....
    Last edited by johnmu; May 31st, 2019 at 02:52 PM. Reason: duplicate - your post reply button is causing this

  18. #18
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    How do i open the child table without retrieving all the records
    The exact same way you opened the parent table without retrieving all the records.

    Code:
    I have used the exact same code for both tables and i assumed that once i had the id to put in the fk field that the child would save.
    You can't do anything to the child table if you can't get the "Fill" method to work without error.

  19. #19

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    I think i my have found my issue.
    IN the child form the field that is bound to the foreign key seems ot be the the issue.
    I am passing in the value to the form when i load it and i can see that ID of the new child is -1 which means the addnew method has been invoked.

    But when i come to save the record i now get an exception saying the foreign key is missing. I am passing the value to the bound text box but it is text not an integer and i think this is the root of the problems.
    Code:
        Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            'SAVE RECORD
            'Me.TblClientTableAdapter.Update(tblclient)
            
    'this line seems to be the issue the value is not being passed to the underlying record
            OpportunityIDTextBox.Text = Me.OppID
    
    
            Me.Validate()
            Me.TblOppDetailsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(DsOpportunity)
    
            Me.Close()
        End Sub
    How can i inset the value into either the form field of directly into the underlying recordset?

    thanks

    John

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    It seems your talking about a different issue now. Has the issue with constraints error been solved? If so, then it would be best to start a new thread for this issue with a full and clear explanation and all the relevant code.

    If the constraints issue hasn't been solved then as I said, nothing in thechild table will work till you solve that problem.

  21. #21

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Thanks but the issue has not changed, i still can't add new records to the child tables within the dataset.

    I double checked and there is nothing wrong with my relationships.

    But what i discovered it that the value of the FK was not being passed to the databinder so I needed to use me.validate children to get the FK field to register the value.

    But it still doesn't let me insert a record in the child table. In the end I had to resort to creating a new dataset with the single table in it and then updating that. This works fine but clearly i'm still missing a trick.

    If you have any further thoughts i'd be most grateful.

    thanks

    John

  22. #22
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Insert records into two related tables

    Do you have a particular reason that you are using a TableAdapter rather than a DataAdapter?

  23. #23
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    Quote Originally Posted by johnmu View Post
    Thanks but the issue has not changed, i still can't add new records to the child tables within the dataset.

    I double checked and there is nothing wrong with my relationships.

    But what i discovered it that the value of the FK was not being passed to the databinder so I needed to use me.validate children to get the FK field to register the value.

    But it still doesn't let me insert a record in the child table. In the end I had to resort to creating a new dataset with the single table in it and then updating that. This works fine but clearly i'm still missing a trick.

    If you have any further thoughts i'd be most grateful.

    thanks

    John
    You need to post your current code, I have no idea what changes you've made since the last time you posted your code. My guess is you still haven't fixed the problem I pointed out to you in Post #15. You keep trying to say the problem is Inserting records but that's not the problem(maybe there's a problem with the record insert code but that's not relevant at this point). As I said before, if you get an error on the Child table "Fill" method then nothing from that point forward will work correctly.

    Look at your Edit code,
    Code:
                Case "Edit"
                    Me.TblOpportunityTableAdapter.FillBy(DsOpps.tblOpportunity, OppId)
    
                    If OppDetailIDTextBox.Text = "" Then
    
                        REM this line throws an exception if i add the .tblOppdetails to the DSOpps
                        REM if i change it to just DSOpps it doesn't throw an exception but i'm still no further forward
    
                        Me.TblOppDetailsTableAdapter.Fill(DsOpps.tblOppDetails)
                        
                        Me.TblOppDetailsBindingSource.AddNew()
    
                        OppIDTextBox.Text = Me.OppId
                        OppNominalSizeTextBox.Text = "400"
                        OppDetailCreatedByTextBox.Text = "JCM"
                        OppDetailCreatedDateTimePicker.Text = Now()
    
                    End If
    
                    OpportunityUpdatedByTextBox.Text = "JCM"
                    OpportunityUpdatedDateTimePicker.Value = Now()
    
    
            End Select
    You open the Parent table by only retrieving ONE record,
    Code:
    Me.TblOpportunityTableAdapter.FillBy(DsOpps.tblOpportunity, OppId)
    Though I have no idea where "OppId" codes from.

    But you retrieve ALL the records from the Child table, this will not work when Constraints are enabled.
    Code:
    Me.TblOppDetailsTableAdapter.Fill(DsOpps.tblOppDetails)
    You need to add a new query to the tblOppDetailsTableAdapter that retrieves only the records that match "OpId", then it would look something like this,
    Code:
    Me.TblOppDetailsTableAdapter.FillByOppId(DsOpps.tblOppDetails, OppId)
    One thing that I did notice in the Save routine is that you did call the EndEdit on Me.TblOppDetailsBindingSource.
    Code:
        Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            'SAVE RECORD
            'Me.TblClientTableAdapter.Update(tblclient)
            
    'this line seems to be the issue the value is not being passed to the underlying record
            OpportunityIDTextBox.Text = Me.OppID
    
    
            Me.Validate()
            Me.TblOppDetailsBindingSource..EndEdit
            Me.TblOppDetailsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(DsOpportunity)
    
            Me.Close()
        End Sub

  24. #24

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Wes4dbt,

    I've made so many changes to try and fgure this out that it was simplest to start a new form to keep this as simple as possible.

    Code:
    Public Class My_Std_Form4
        Private Sub TblClientBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
            Me.Validate()
            Me.TblClientBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.DsClientContact)
    
        End Sub
    
        Private Sub My_Std_Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'DsClientContact.tblClientContact' table. You can move, or remove it, as needed.
            'Me.TblClientContactTableAdapter.Fill(Me.DsClientContact.tblClientContact)
            'TODO: This line of code loads data into the 'DsClientContact.tblClient' table. You can move, or remove it, as needed.
            Me.TblClientTableAdapter.FillByCID(Me.DsClientContact.tblClient, -99)
            Me.TblClientBindingSource.AddNew()
            Me.TblClientContactBindingSource.AddNew()
    
    
        End Sub
    
        Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
    
            Me.ClientCreatedByTextBox.Text = G_UserName
            Me.ValidateChildren()
            TblClientBindingSource.EndEdit()
            TableAdapterManager.UpdateAll(DsClientContact)
    
    
    
            Me.ClientIDTextBox1.Text = Me.ClientIDTextBox.Text
            Me.ContactedCreatedByTextBox.Text = G_UserName
    
            Me.ValidateChildren()
            TblClientContactBindingSource.EndEdit()
            TableAdapterManager.UpdateAll(DsClientContact)
    
    
            Me.Close()
    
    
        End Sub
    - I'm using drag n drop form a dataset created in VS19 to add the fields to the form
    - I then set a fillby select query
    - following the feedback from you all I am using Fillby with a parameter value of -99 to get an empty record
    - then I addnew on the parent and child binders
    - then I end edit
    - Update
    - add the FK value form the parent
    - validate the children to get the value into the datarow
    - update again to save the child.

    it works but is it the best way?

    I'm trying to use as much Drag n drop as possible because i am rebuilding from scratch a large MS Access app and I'll never finish if i have to insert and configure each field manually.

    @KPMC - i'm using the table adaptor rather then the dataadapter because that seems to be the path i'm being sent down

  25. #25
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    There is nothing wrong with Drag n Drop if you know what your doing. The first thing is, make sure you drag the correct ClientContact table onto the form. In the datasource if you open the Client table so the table fields are displayed there should also be a clientContact table listed under the fields. This is the ClientContact table you want to drag onto the form (see example below). I don't know why you are using -99 and not retrieving any records. If you want to add a new record then just click the "+" on the bindingnavigator. There is no need for two Save methods. This is all you need,

    Code:
    Public Class My_Std_Form4
        Private Sub TblClientBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
            Me.Validate()
             Me.TblClientContactBindingSource.EndEdit()
            Me.TblClientBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.DsClientContact)
    
        End Sub
    
        Private Sub My_Std_Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Me.TblClientTableAdapter.Fill(Me.DsClientContact.tblClient)
            Me.TblClientContactTableAdapter.Fill(Me.DsClientContact.tblClientContact)
    
    
    
        End Sub
    End Class
    These are the very basics, if you don't understand these things then you need to do some studying and you'll also find lots of tutorials on the net.
    Last edited by wes4dbt; Jun 10th, 2019 at 01:38 PM.

  26. #26

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Wes,

    thanks but that doesn't work. I have done exactly as you say and the parent saves but not the child. No error, no messages it just doesn't save.

    How does the FK get passed form the parent to the child in your example?

    You ask why i was using the fillyby with a spurious parameter. this is because i don't want to load all the records from the db i just want to addnew so i presume i need to load the table schema.

    You are right there is a lot info on the web but (and this is why i'm asking on a forum) nothing that i can find gives me a simple step by step instruction as to how to do this very common thing. We're many many messages into this now and still I don't see how to get he child to save. Clealry i'm doing something wrong and I'm no coder but i have been dabbling for many many years with VB VB and access. I want to use VB.net and all the D&D and databinding to give me the fastest way to migrate form MSAccess to something more robust but this aspect of the data seems too me at least more complicated than expected.

    thanks

    John

  27. #27
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    How does the FK get passed form the parent to the child in your example?
    If you have your relation set in your dataset the FK will automatically bet inserted into the child record when you do an AddNew on on the Child bindingsource.

    You ask why i was using the fillyby with a spurious parameter. this is because i don't want to load all the records from the db i just want to addnew so i presume i need to load the table schema.
    So you odn't plan on Editing or Deleting these records?

    Anyway I found some tutorials by Clint MacDonald, here he starts talking about databinding and drag n drop. https://www.youtube.com/watch?v=yvx-p-QYoUg

    He has many more, don't worry about the fact he is using an SQL Server database. The steps are the same for both.

    Also this video does a reall good job of explaining https://www.youtube.com/watch?v=yvx-p-QYoUg It's old but nothing has really changed much.

    After some research I'd suggest starting with a new form. If your still having problems you would probably get more more help if you start a new thread. Post all relevant code, explain what you have done, what problems your having, if there is an error, what is the error, what line of code is causing the error.
    Last edited by wes4dbt; Jun 11th, 2019 at 08:58 PM.

  28. #28
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Insert records into two related tables

    I dont know about that tablemanager/updateall business, but it is likely there is no command builder, insert or update commands, I dont see any fk update/delete/cascade rules. It seems like you are relying on the designer to draw your tables/relations then figuring to apply the nuts and bolts in code? You should stick to Mr. Wizard, or stick to code, or at least that is my take on the situation.
    At any rate, there is much going on that we cant see here as mentioned. All the pieces required are in post#11.

    ... just figure I would give ya another post to ignore

    carry on...

  29. #29

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    kpmc,

    thanks for your posts and for #11 which i have noted for future use. I'm trying to stick to the wizard for now because its supposed to be the quickest way up this steep learning curve.

    The wizard does create the insert and update rules automatically, the tables are related in the designer and i'm DnD the correct fields as per wes4dbt screenshot.

    what is not happening is the automatic copy of the parentID to the FK of the child. Wes' last post strongly suggest that this should happen but its not in my case.

    Part of the problem is not knowing what i don't know so i'm guessing at the right way but if that doesn't work i'm left questioning if my approach is wrong or if its just a coding error.

    I will spend some time watching the links wes has suggested and come back.

    thanks for everyone's help

    John

  30. #30

    Thread Starter
    Junior Member
    Join Date
    May 2019
    Posts
    24

    Re: Insert records into two related tables

    Folks,

    a final update, I've solved the problem.

    ChildTablebindingsource.CURRENT("FKID")=1234

    something that was explain by kpmc a while back but i failed to spot it.

    I really do appreciate everyone's help along the way and it has certainly helped me understand vb.net and visual studio way better.

    I've got a long way to go so will undoubtedly tap you up for some more advice.

    Many thanks

    John

  31. #31
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Insert records into two related tables

    John,

    Glad you got your problem solved. But I do want to mention, if your Typed DataSet was setup correctly and the relation setup correctly, you shouldn't need "ChildTablebindingsource.CURRENT("FKID")=1234". It will be done automatically. Here is another working example,

    Went to my datasource window and dragged the Employee table (Details) onto the form.
    Then I dragged the ContactDetails table (DataGridView display) onto the form, I used the ContactDetails table that is the child table listed under Employees. See image in post 25

    This is the code that is created,
    Code:
    Public Class Form8
        Private Sub EmployeeBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles EmployeeBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.EmployeeBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.EmployeeDataSet)
    
        End Sub
    
        Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Try
                Me.EmployeeTableAdapter.Fill(Me.EmployeeDataSet.Employee)
                Me.ContactDetailTableAdapter.Fill(Me.EmployeeDataSet.ContactDetail)
            Catch ex as Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    End Class
    With just that you can Add/Edit/Delete parent or Child records. I should add though, if you add a new parent record, you need to save that record before adding a new child record when your using an auto increment type field as the parent primary key, unless you have the Relation setup to cascade updates and deletes. Also, when you setup the relation with constraints, the order in which you fill the datatables is important. Parent first, child second. See above. I added the Try/Catch, you should always use a Try/Catch when accessing data.



    So as I said, I'm glad your code is work. This is just something to think about going forward.
    Last edited by wes4dbt; Jun 21st, 2019 at 10:20 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width