-
May 13th, 2019, 03:11 PM
#1
Thread Starter
Junior Member
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
-
May 13th, 2019, 06:47 PM
#2
Re: Insert records into two related tables
It's actually very simple, but slightly less simple in certain cases. What database are you using?
-
May 13th, 2019, 09:27 PM
#3
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.
-
May 24th, 2019, 11:49 AM
#4
Thread Starter
Junior Member
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
-
May 24th, 2019, 12:12 PM
#5
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.
-
May 24th, 2019, 02:07 PM
#6
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
-
May 26th, 2019, 04:22 AM
#7
Thread Starter
Junior Member
Re: Insert records into two related tables
-
May 26th, 2019, 07:21 AM
#8
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:
INSERT INTO SomeTable (Column1, Column2) VALUES (?, ?); SELECT SomeTableId = SCOPE_IDENTITY()
-
May 28th, 2019, 11:53 AM
#9
Thread Starter
Junior Member
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
-
May 28th, 2019, 02:41 PM
#10
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.
-
May 28th, 2019, 03:29 PM
#11
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
-
May 31st, 2019, 03:59 AM
#12
Thread Starter
Junior Member
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:
- the child primary key id is -1 which i presume means its a new record
- none of the child fields will populate form my code
- 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
-
May 31st, 2019, 04:31 AM
#13
Thread Starter
Junior Member
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
-
May 31st, 2019, 12:55 PM
#14
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.
-
May 31st, 2019, 01:13 PM
#15
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.
-
May 31st, 2019, 02:39 PM
#16
Thread Starter
Junior Member
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
-
May 31st, 2019, 02:40 PM
#17
Thread Starter
Junior Member
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
-
May 31st, 2019, 03:04 PM
#18
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.
-
Jun 3rd, 2019, 06:07 AM
#19
Thread Starter
Junior Member
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
-
Jun 3rd, 2019, 11:52 AM
#20
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.
-
Jun 8th, 2019, 06:16 AM
#21
Thread Starter
Junior Member
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
-
Jun 8th, 2019, 12:34 PM
#22
Re: Insert records into two related tables
Do you have a particular reason that you are using a TableAdapter rather than a DataAdapter?
-
Jun 8th, 2019, 03:41 PM
#23
Re: Insert records into two related tables
Originally Posted by johnmu
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
-
Jun 10th, 2019, 08:41 AM
#24
Thread Starter
Junior Member
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
-
Jun 10th, 2019, 01:23 PM
#25
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.
-
Jun 11th, 2019, 03:36 AM
#26
Thread Starter
Junior Member
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
-
Jun 11th, 2019, 12:14 PM
#27
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.
-
Jun 11th, 2019, 01:24 PM
#28
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...
-
Jun 12th, 2019, 04:45 AM
#29
Thread Starter
Junior Member
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
-
Jun 21st, 2019, 02:15 PM
#30
Thread Starter
Junior Member
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
-
Jun 21st, 2019, 03:57 PM
#31
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|