VB 2008 - saving master/detail data in a transaction

Dear Experts,
Am trying to save data in a master/detail form using transaction scope and not been sucesful.

Have a look at my codebelow and advise.
            Using scope As TransactionScope = New TransactionScope
                Me.GardenInvoiceDtlBindingSource.Position = Me.GardenInvoiceHdrBindingSource.Position

            End Using
            MessageBox.Show("Record Saved", "Save record", MessageBoxButtons.OK, MessageBoxIcon.Information)
            GroupBox2.Enabled = True
        Catch ex As TransactionAbortedException
            MessageBox.Show(ex.Message, "Tea Tracking | Save Record-Transaction Aborted", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch ex1 As ApplicationException
            MessageBox.Show(ex1.Message, "Tea Tracking | Save Record-Application exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch ex2 As Exception
            MessageBox.Show(ex2.Message, "Tea Tracking | Save Record", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

Open in new window

LVL 12
Norman MainaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

>and not been sucesful.

Any error?
Norman MainaAuthor Commented:
Am getting a constraints exception.
The  keys referenced in the exception are the primary key of the master table and the foreign key or the details table.

In MS reference, it states all I have to do is call the bindingsources endedit of the parent then update the parent,then call the endedit of the child bindingsource before updating.
This is what am doing and getting this exeption -dont know what am doing wrong.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_GardenInvoiceDtl_GardenInvoiceHdr". The conflict occurred in database "TeaTracking", table "dbo.GardenInvoiceHdr", column 'GardenID'. The statement has been terminated.

Open in new window

Norman MainaAuthor Commented:
what is ive been used to doing is force user to saving the master first and then save the child after the master records are in.

But i know theres a more elegant way of doing this -including the use of transactions so that if entry of master fails-entry of child fails too.

Any ideas would be appreciated.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

If there is an error when saving master, an exception will be generated and your call to update child table will not be executed anyway.
Norman MainaAuthor Commented:
But is I save the master only,it will save and then i can save the details.
But if I want to save them together sequentially,its not working...master first and then details to follow.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Norman MainaAuthor Commented:
no other answer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.