Results 1 to 18 of 18

Thread: error at save in to 2 tables they have relationship one-to-many

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Question error at save in to 2 tables they have relationship one-to-many

    hi all

    I have 2 tables Customers and Orders it have relationship one-to-many and i set the cascade between tables. i add both tables to one form and i set EndEdit event to the CustomerBindingSource owing to Saves the new record in Customers before save new record to the Orders but i receives error :
    A foreign key value cannot be inserted because a corresponding primary key value does not exist.
    I did no know what is wrong !




    ErrorSave.zip
    Last edited by ebrahim; Dec 13th, 2012 at 10:31 PM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: error at save between 2 tables they have relationship one-to-many

    That means that your child inserts are being done before your parent inserts. Please the relevant code and ONLY the relevant directly in the thread. Don't make us jump through hoops unnecessarily. If the code is in your post then we can see it without navigating to nay external sites or downloading anything. Take the easy option first and only go the harder route if necessary.

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    the code in save button :

    Code:
      Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.CustomerBindingSource.EndEdit()
            Me.OrdersBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
    
        End Sub
    in the load form:

    Code:
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Me.CustomerTableAdapter.Fill(Me.Database1DataSet.Customer)
            Me.OrdersTableAdapter.Fill(Me.Database1DataSet.Orders)
        End Sub
    and one line at add new row in :

    Code:
    Private Sub OrdersBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles OrdersBindingSource.AddingNew
            Me.CustomerBindingSource.EndEdit()
        End Sub
    and the last thing i set the cascade between Datatables in DataSet.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: error at save between 2 tables they have relationship one-to-many

    In theory, UpdateAll should handle related data. It appears that it's not in your case. Try calling Update on the table adapters explicitly in the appropriate order and see if the issue goes away.

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Quote Originally Posted by jmcilhinney View Post
    Try calling Update on the table adapters explicitly in the appropriate order
    Sorry, but I am weak in the English language, I hope to explain to me what you mean
    thank you
    Last edited by ebrahim; Dec 11th, 2012 at 05:56 AM.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: error at save between 2 tables they have relationship one-to-many

    Call Update on your parent table adapter first and then call Update on your child table adapter.

  7. #7

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Yes Mr.jmcilhinney I do it :
    Code:
      Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.CustomerBindingSource.EndEdit()
            Me.CustmerTableAdapter.Update(Me.Database1DataSet.Customer)
            Me.OrdersBindingSource.EndEdit()
            Me.OrdersTableAdapter.Update(me.Database1DataSet.Orders)
    
        End Sub
    But The-error still appear ! Is there any mistake in the code?
    Last edited by ebrahim; Dec 11th, 2012 at 07:58 AM.

  8. #8

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Is there any suggestion

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: error at save between 2 tables they have relationship one-to-many

    you need to create a DataRelationship between your two tables... right now, as far as VB is concerned, they are just two tables in the dataset... it doesn't know that the ID of one is the FKey of the other... there's no parent/child relationship in the dataset.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Mr.techgnome did that in the photo is not DataRelationship ?




    plz explained to me what you mean to do it.

    thanks

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: error at save between 2 tables they have relationship one-to-many

    What is IDCustomer? Datatype and definition... is it an Identity (or AutoNumber?) How does it get set? What is its source?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: error at save between 2 tables they have relationship one-to-many

    OK, I think I see the issue now. In the DataSet designer (your first screen shot) try editing the properties of the DataRelation (the line joining the two DataTables) and set the action on Update to Cascade. I think what's happening is that your app is generating temporary IDs for records in both DataTables but they are not the same as the final IDs generated by the database. Your child records contain the temporary IDs from the parent table so, when you try to save them, no matching parent ID is found. When you save the parent records, the parent DataTable should be refreshed with the final IDs generated by the database. By cascading updates, those values will be pushed to the child records also, so the IDs match when the child records are saved.

  13. #13

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Mr.techgnome

    IDCustomer
    Data type: int
    Identity: true
    allow nulls: no
    Unique: yes
    Primary key: yes

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: error at save between 2 tables they have relationship one-to-many

    I notice that you say in your first post:
    i set the cascade between tables
    Did you do that in the database or the application, because post #10 indicates not in the database. If Updates are already cascading across the DataRelation then maybe the parent table is not being updated. Try saving just the parent records and see if the IDs in your DataTable match those in the database.

  15. #15

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Mr.jmcilhinney I Chang the Update Rule to cascade in the Database and test the app put still error appear.

    see :



    Quote Originally Posted by jmcilhinney View Post
    Try saving just the parent records and see if the IDs in your DataTable match those in the database.
    I try to save the parent only and it is save then close the form and reopen it and try save child and it is saved ok put that is not what i need any way i make search then I find solve but it is complex code must write it in the TableAdapter class http://blogs.msdn.com/b/bethmassi/ar...-database.aspx
    is there any Idea easier ?

  16. #16
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: error at save between 2 tables they have relationship one-to-many

    Quote Originally Posted by ebrahim View Post
    Mr.jmcilhinney I Chang the Update Rule to cascade in the Database and test the app put still error appear.
    It's no use just changing it in the database because that's not where it's needed. It's needed in your DataSet so did you regenerate the Data Source after making that change in the database?
    Quote Originally Posted by ebrahim View Post
    I try to save the parent only and it is save then close the form and reopen it and try save child and it is saved ok put that is not what i need any way
    Maybe if you actually did what I said then you might get a useful result.

  17. #17

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Quote Originally Posted by jmcilhinney View Post
    It's no use just changing it in the database because that's not where it's needed. It's needed in your DataSet so did you regenerate the Data Source after making that change in the database?Maybe if you actually did what I said then you might get a useful result.
    Yes i regenerate the data source but still the error , but may i ask u To look in this link
    http://blogs.msdn.com/b/bethmassi/ar...-database.aspx
    I think that the problem needs Trick
    Last edited by ebrahim; Dec 12th, 2012 at 10:07 AM.

  18. #18

    Thread Starter
    Member
    Join Date
    Jun 2011
    Location
    saudi arabia
    Posts
    37

    Re: error at save between 2 tables they have relationship one-to-many

    Sorry error add
    Last edited by ebrahim; Dec 12th, 2012 at 10:09 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width