Updating database!

Hi, Im fetting abit desperate here, I had my form running fine abut soomethings happened and i don't  know what?

I have one form with three datagrids all with parent child relationships.  Now when i make changes to the dataset and update them to the via the dataAdapter I am getting this error.  Here is the senario

Form1(Parent to Datagrid1)(Quote)
Datagrid1(Parent to datagrid 2)(Model)
Datagrid2(Parent to datagrid 3)(Option)
Datagrid3(child to Datagrid 2)(Accessory)

The problem is when I add a new Quote then select my models, and Options and, Accessory i try to save the new information I get this error.
"You cannot add or change a record because a related record is required in a table"  It won't let me save the Options or Accessories.  I will only save the first parent chid relationship.  I can return to edit the form and add an Option to a  Model that has been saved already and the same with the accessorie.

Can anyone please help...............I getting pretty desperate....i have to present this in a week.
Thanks everyone.
Here is my update method.....

______________________________________________

 Public Sub UpdateQuote()
        'this method will deal with all the updates for the Quote Dataset.
        'Delete Child Records First
        'Check to see if there are any records to deletes to update.
        If Me.DsQuote1.HasChanges Then
            Try
                'end the edit state of the dataset.

                Dim mDialog As DialogResult
                mDialog = MessageBox.Show("Do you want to save the changes", "Quote", MessageBoxButtons.YesNo)
                If mDialog.OK Then
                    'update the child Accessory deletes
                    If Not Me.DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Deleted) Is Nothing Then
                        'if there are rows to be deltele, do it here
                        Dim dtDeletes As DataTable
                        dtDeletes = DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Deleted)
                        'update the database through the dataAdapter
                        Me.DaQuoteAccessory.Update(dtDeletes)
                    End If
                    'Delete the Options Child records
                    If Not Me.DsQuote1.Tables("Quote_Option").GetChanges(DataRowState.Deleted) Is Nothing Then
                        'if there are rows to be deltele, do it here
                        Dim dtDeletes As DataTable
                        dtDeletes = DsQuote1.Tables("Quote_Option").GetChanges(DataRowState.Deleted)
                        'update the database through the dataAdapter
                        Me.DaQuoteOption.Update(dtDeletes)
                    End If
                    'Delete the Model Child records
                    If Not Me.DsQuote1.Tables("Quote_Models").GetChanges(DataRowState.Deleted) Is Nothing Then
                        'if there are rows to be deltele, do it here
                        Dim dtDeletes As DataTable
                        dtDeletes = DsQuote1.Tables("Quote_Models").GetChanges(DataRowState.Deleted)
                        'update the database through the dataAdapter
                        Me.DaQuoteModel.Update(dtDeletes)
                    End If
                    'Update the Add Edit or delete actions for all the parents
                    If Not DsQuote1.Tables("Quote").GetChanges() Is Nothing Then
                        Me.DaQuote.Update(DsQuote1, "Quote")
                    End If

                    'Add the Model Children
                    If Not DsQuote1.Tables("Quote_Models").GetChanges(DataRowState.Added) Is Nothing Then
                        Dim dtTable As DataTable
                        dtTable = DsQuote1.Tables("Quote_Models").GetChanges(DataRowState.Added)
                        Me.DaQuoteModel.Update(dtTable)
                    End If
                    'Add New Option Children additions
                    If Not DsQuote1.Tables("Quote_Option").GetChanges(DataRowState.Added) Is Nothing Then
                        Dim dtTable As DataTable
                        dtTable = DsQuote1.Tables("Quote_Option").GetChanges(DataRowState.Added)
                        Me.DaQuoteOption.Update(dtTable)
                    End If
                    'Add New Accessory children rows
                    If Not DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Added) Is Nothing Then
                        Dim dtAccessory As DataTable
                        dtAccessory = DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Added)
                        Me.DaQuoteAccessory.Update(dtAccessory)
                    End If
                    'Update Model Children Edits.
                    If Not DsQuote1.Tables("Quote_Models").GetChanges(DataRowState.Modified) Is Nothing Then
                        Dim dtTable As DataTable
                        dtTable = DsQuote1.Tables("Quote_Models").GetChanges(DataRowState.Modified)
                        Me.DaQuoteModel.Update(dtTable)
                    End If
                    'Update Option Children edits
                    If Not DsQuote1.Tables("Quote_Option").GetChanges(DataRowState.Modified) Is Nothing Then
                        Dim dtTable As DataTable
                        dtTable = DsQuote1.Tables("Quote_Option").GetChanges(DataRowState.Modified)
                        Me.DaQuoteOption.Update(dtTable)
                    End If
                    'Update New Accessory child rows
                    If Not DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Modified) Is Nothing Then
                        Dim dtTable As DataTable
                        dtTable = DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Modified)
                        Me.DaQuoteAccessory.Update(dtTable)
                    End If
                    DsQuote1.AcceptChanges()
                    MessageBox.Show("Sucessful", "Action Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
            Catch ex As DBConcurrencyException
                MessageBox.Show(ex.ToString, "error in Quote Update")
                Dim customErrorMessage As String
                customErrorMessage = "Concurrency violation" & vbCrLf
                customErrorMessage += CType(ex.Row.Item(0), String)
                MessageBox.Show(customErrorMessage)
                ' Replace the above code with appropriate business logic
                ' to resolve the concurrency violation
            End Try
        End If

    End Sub

---------------------------------------------------------------------------
Nerdy_Girl88Asked:
Who is Participating?
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.

Jerry_PangCommented:
"You cannot add or change a record because a related record is required in a table"

this is a database error not in your code.

in case of add record

You cannot add a new record if the parent table does not exist yet.
sample
Department (parent)--> employee(child)

Department
ADMIN
SOFTWARE
HARDWARE

Employee
Jerry - ADMIN
Alex - SOFTWARE
Mike - SOFTWARE
Roel - HARDWARE

Insert - a new employee
Insert employee-Nerdy with department-SUPERMODEL
You cannot insert in employee with a new department yet. you have to insert it in the department first before you can insert in employee.
You have to insert SUPERMODEL in department first before you insert the employee.

In case of Edit.
Sample
EDIT - department of JERRY from ADMIN to ADMINS
You cannot edit to ADMINS because there is no record yet in DEPARTMENT table.
or
EDIT - admin ADMIN to ADMINs
You cannot change a record because a JERRY's record is using ADMIN



Solution 1 (for ADD records)
1)INSERT Record in Parent first before you insert a new record in child table.

Solution 2 (for edit records)
USE Cascade UPDATE
EDit the relationship of parent child
then check the CASCADE UPDATE.
each time you edit the DEPARTMENT table, automatically the related record will also update

Enable cascade delete also if you like.
0
Nerdy_Girl88Author Commented:
Hi, thanks, I have code in my program that updates in the parent child order, but it dosen't reconize that a new parent has been added.  How can i see what data is being update to the database while im debugging?  This might help me understand.  I have tried quick watch, but imi not sure on what object.

thanks nerdy SUPERMODEL
0
Jerry_PangCommented:
hmm. im not so expert yet with vb(is this vb?).

i have encountered this problem before, what i do is
do an applychanges to the database before continuing.

Save Parent data ->applychanges to the db
Save Child data ->applychanges to the db
Save 2nd Child data ->applychanges to the db
Save GrandChild data -> applychanges to the db

Additional feature that we need is the rollback transaction if there where any errors.
Algorithm is something like this
Begin Transaction
Save Parent data ->applychanges to the db
Save Child data ->applychanges to the db
Save 2nd Child data ->applychanges to the db
Save GrandChild data -> applychanges to the db

if there are any errors
-> Rollback Transactions
Else
-> Commit Transactions
endif

from your code i guess DsQuote1.AcceptChanges() saves the changes to the database is that right?
Try adding that line after Me.DaQuoteModel.Update()
that way, the Parent Data will now inserted in DB, so Child records can use them
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nerdy_Girl88Author Commented:
Im using VB.net,

I am using the same logic that you are describing and it is not working.  The DsQuote1.AcceptChanges()  saves the changes to the dataset and the Me.DaQuoteModel.Update() saves the changes to the database.  

Your right about this

Save Parent data ->applychanges to the db
Save Child data ->applychanges to the db
Save 2nd Child data ->applychanges to the db
Save GrandChild data -> applychanges to the db

Thats what my code is doing, however the parent updates don't appear to be seen by the child.  When i debug the parent changes appear to be saving but then i try to save the children and they can't see the new parents that were just saved.  This problem only occurrs with the 2nd and GrandChild.

Big problem i think!
0
Jerry_PangCommented:
ic.. hmm..

Parent(Quote)
child1-(Model)
  grandchild(Accessory)
child2-(Option)

hmmm, do you know which line does the error occurs?
what is tha acceptchanges() doing there? acceptchanges updates 1 rows only is that right?

hmm... anyway, try
Delete first
Update Second
Insert Last

Try moving the Update up before the ADD.
i'l be back tomorrow. i have some questions though,
What are your tables?
what are the relationship?
do you use trigger updates?
what database are you using?
0
Nerdy_Girl88Author Commented:
What are your tables?   Quote,Model,Option,Accessory
what are the relationship? one to many, in the order above.  Quote(1 to Many)Model(1 to many)Option(1 to many)Accessory.
do you use trigger updates? No
what database are you using? Access, VB.net

I think my problem is that the binding manager isn't ending the CurrentEdit!  How Can i Check this.

thanks




0
Jerry_PangCommented:
follow up.

>Quote(1 to Many)Model(1 to many)Option(1 to many)Accessory

are you using autonumber?
im trying to simulate your program on my pc.

0
Nerdy_Girl88Author Commented:
Yes,
0
Jerry_PangCommented:
last try, sorry if i could not help.
recommended reading
creating relationship
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataRelationClassTopic.asp

>> How can i see what data is being update to the database while im debugging?

from your code
                    If Not Me.DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Deleted) Is Nothing Then
                        'if there are rows to be deltele, do it here
                        Dim dtDeletes As DataTable
                        dtDeletes = DsQuote1.Tables("Quote_Accessory").GetChanges(DataRowState.Deleted)
                        'update the database through the dataAdapter
                        Me.DaQuoteAccessory.Update(dtDeletes)
                    End If

Break on dtDeletes,
See the values in debugmode (debug>immediate window)

on command windows type
?me.dtDeletes.Rows.Item(0)

break on all data table, like this
break here and see the value of dttable>  dtTable = DsQuote1.Tables("Quote_Models").GetChanges(DataRowState.Added)

0

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
Jerry_PangCommented:
use
?me.dtDeletes.Rows.Item(0)
?me.dtDeletes.Rows.Item(1)
?me.dtDeletes.Rows.Item(2)
?me.dtDeletes.Rows.Item(3)
?me.dtDeletes.Rows.Item(4)
?me.dtDeletes.Rows.Item(5)

to loop through the values, there's a component written by someone, i could not find it, i think its on www.codeproject.com.
where you can see all the values of the table and you dont have to do the above. i think its c#.
0
Nerdy_Girl88Author Commented:
Thank you Jerry Pang,  I have been a bit busy with documentation.  I will try those sujjestion and get back to you hopefully with a solution to my problem

 
0
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
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.