• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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

---------------------------------------------------------------------------
0
Nerdy_Girl88
Asked:
Nerdy_Girl88
  • 6
  • 5
1 Solution
 
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
Industry Leaders: 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!

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now