?
Solved

How to write to 2 table in a dataset to access database with key field relationship set in database?

Posted on 2007-08-08
19
Medium Priority
?
131 Views
Last Modified: 2010-04-23
I have 2 tables in a dataset for an access database.  The 2 tables have a relationship set inside the database so I can implement cascading deletes.  The key field in table one and the key field in table 2 is the medical record number of the mother in 1 and the child in 2.  I write the data to the 1st table in the dataset and update the first table.  Then I write the data to the second table and when I update, I get error.  It will not write to the database because it expects the record in table one to be there because of the relationship that was set up.  Is there a way to finalize or close the connection to just the 1 table in order to write a record to the 2nd table?  Or should I do away with the key field relationsip inside the database and handle everything with code?

John
0
Comment
Question by:leachj
  • 9
  • 8
  • 2
19 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19655532
If you have a parent-child relationship between the tables, is there a reason that you can't write a record to the parent table before writing records to the child table?

Bob
0
 

Author Comment

by:leachj
ID: 19656790
I do write the record to the Mom table first.  Then write the baby table.  When it hits the update() for the baby table I get the error.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19656912
Which direction is the relationship set?  Mom to Baby?

Bob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:leachj
ID: 19656983
Yes.  The mom may have single, twins or triplets.  The babys would have the moms med record number stored with their record.  Of course they also have a unique mrn.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19656999
No, I mean how did you define the relationship?  What is the parent table, and what is child table?

Bob
0
 

Author Comment

by:leachj
ID: 19657029
tblMom is parent and tblBabys is child
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19657070
That is what we need to make sure of.

1) Open DataSet in the designer

2) Right-click on the relation, and select 'Edit Relation...'

3) Is 'tblMom' the parent table on the left, and 'tblBabys' the child table on the right?

Bob

0
 

Author Comment

by:leachj
ID: 19657110
yes
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19657244
How you are calling the TableAdapter.Update?  What is the order of the calls?

Bob
0
 

Author Comment

by:leachj
ID: 19657319
This is basically it.  After entering the data into the form, I click finish button....

 Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click
        Dim chkData As Boolean
        chkData = checkAllMomInfoEntered() ' Call function to check if all fields are filled in moms part
        If chkData = False Or chkData = Nothing Then
            Exit Sub                    ' If not, then bail out
        Else
            Call WriteMomRecords()  ' Everything looks good so  write the data to the database
        End If

            chkData = checkAllBabyInfoEntered()  ' Same here, but for the baby's
        If chkData = False Or chkData = Nothing Then
            Exit Sub
        Else
            Call WriteBabysRecords()
        End If
   ' finish up other stuff....
End Sub

Private Sub AddNewMomRow() ' Add a new row to the dataset and write it to the database
   ' code here ending with
   TA1.Update(DSet.tblMom) ' write the new row information to the actual database
End sub

Private Sub AddNewBabysRow()
   ' code here ending with
   TA2.Update(DSet.tblBabys)
End Sub
0
 

Author Comment

by:leachj
ID: 19657417
Sorry I left the 2 subs out.  I'm sure I am confusing you enough without missing pieces.  Here it is again..

 Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click
        Dim chkData As Boolean
        chkData = checkAllMomInfoEntered() ' Call function to check if all fields are filled in moms part
        If chkData = False Or chkData = Nothing Then
            Exit Sub                    ' If not, then bail out
        Else
            Call WriteMomRecords()  ' Everything looks good so  write the data to the database
        End If

            chkData = checkAllBabyInfoEntered()  ' Same here, but for the baby's
        If chkData = False Or chkData = Nothing Then
            Exit Sub
        Else
            Call WriteBabysRecords()
        End If
   ' finish up other stuff....
End Sub

Private Sub WriteMomRecords()
        If EditDataFlag = False Then
            Call AddNewMomRow()
            Exit Sub
        Else
             ' do something else
        End If
End Sub

Private Sub AddNewMomRow() ' Add a new row to the dataset and write it to the database
   ' code here ending with
   TA1.Update(DSet.tblMom) ' write the new row information to the actual database
End sub

Private Sub WriteBabysRecords()
        If EditDataFlag = False Then
            Call AddNewBabysRow()
        Else
            ' do something else
        End If
End Sub

Private Sub AddNewBabysRow()
   ' code here ending with
   TA2.Update(DSet.tblBabys)
End Sub
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19657539
Would it be possible to collect all the Mom rows, and all the Babys rows, and then call TA1.Update(DSet.tblMom), and then TA2.Update(DSet.tblBabys)?

Bob
0
 

Author Comment

by:leachj
ID: 19657685
I relocated them to the end of the finish button click event but got same message that a related record is required.....  checking the database when the program halts, before stopping it, the record is written into the tblMom so it appears to be in place.

Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click
        Dim chkData As Boolean
        chkData = checkAllMomInfoEntered() ' Call function to check if all fields are filled in moms part
        If chkData = False Or chkData = Nothing Then
            Exit Sub                    ' If not, then bail out
        Else
            Call WriteMomRecords()  ' Everything looks good so  write the data to the database
        End If

            chkData = checkAllBabyInfoEntered()  ' Same here, but for the baby's
        If chkData = False Or chkData = Nothing Then
            Exit Sub
        Else
            Call WriteBabysRecords()
            TA1.Update(DSet.tblMom)
            TA2.Update(DSet.tblBabys)
        End If
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19657830
Try this:

            TA1.Update(DSet.tblMom)
            DSet.tblMom.AcceptChanges()
            TA2.Update(DSet.tblBabys)

Bob
0
 

Author Comment

by:leachj
ID: 19657878
I had tried that before with no success but did so again.  Same result.
0
 
LVL 34

Accepted Solution

by:
Sancler earned 2000 total points
ID: 19658810
Can I suggest that you try a printout - e.g. Debug - of the BabysRecord you are trying to save before you try to save it and then, when the app hangs, after the MomRecord has been saved, try manually entering it into Access?  I'm wondering whether the problem is not that the Mom record is not there but whether there's something up with the Baby record.  And that test should give an answer on that.

Roger
0
 

Author Comment

by:leachj
ID: 19661541
Sancler,
You are absolutely correct.  I was passing the control to the MomMRN field instead of the control.text.

Thanks,
John
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19662463
Roger,

Thanks, for swooping in and saving the day after all that scraping and scratching.

Bob
0
 
LVL 34

Expert Comment

by:Sancler
ID: 19662823
Bob

Well it looked like you'd done a pretty good job in rounding up "the usual suspects".  There didn't seem many possibilities left.  ;-)

Roger
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question