locked
SQL table adapter odd behavior RRS feed

  • Question

  • I'm trying to get data from an SQL database using the dataset designer getdataby method for the table adapter and I am receiving and error telling me that it "Cannot find the parent Relation". When I use the fillby method for the same table adapter the table fills with no errors....

        ShippedEmps.FillByShip(RelatedDataSet.ShippedMariners, 3)   ' this works
    
            Dim x = ShippedEmps.GetDataByShip(3) ' this fails
    Is there something I'm missing?

    Friday, September 16, 2016 11:55 AM

Answers

All replies

  • Do you have an expression column in your TableAdapter? It would probably help to see the SQL query.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, September 16, 2016 12:16 PM
  • just a long shot:

    you have related tables in your dataset, and the shippedmariners table has a parent table, maybe 'ship',

    and one query returns the parent ship's id, while the other query doesn't. 

    If there's a parent id, then the dataset would complain that the corresponding parent record is not present in the dataset.

    You should either fill the parent table first with the corresponding parent(s), or adapt the query so that is doesn't return a parent id.

    regards,

    Nico


    Regards, Nico <br/>


    • Edited by Nico Boey Friday, September 16, 2016 12:29 PM
    Friday, September 16, 2016 12:28 PM
  • Do you have an expression column in your TableAdapter? It would probably help to see the SQL query.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Query...

    SELECT        Contacts.TableKey AS CtableKey, Contacts.Office_ID AS COffice_ID, Contacts.Client_ID AS CClient_ID, Contacts.ContactKey, Contacts.Contact_Type, 
                             Contacts.PRNumber AS CPRNumber, Contacts.Company, Contacts.Last_Name, Contacts.First_Name, Contacts.Middle_Name, Contacts.Sufix, Contacts.Title, 
                             Contacts.Gender, Contacts.DOB, Contacts.Delete_Flag, Shippment_Info.TableKey, Shippment_Info.PRNumber, Shippment_Info.Office_ID, Shippment_Info.Client_ID, 
                             Shippment_Info.Discharge_Ref, Shippment_Info.VesselID, Shippment_Info.PositionID, Shippment_Info.Date_Of_Shipment, Shippment_Info.Time_Of_Shipment, 
                             Shippment_Info.Place_Of_Shipment
    FROM            Contacts INNER JOIN
                             Shippment_Info ON Contacts.PRNumber = Shippment_Info.PRNumber
    WHERE        (Contacts.Contact_Type < 3) AND (Contacts.Company < 3) AND (Shippment_Info.VesselID = @Office_ID)
    This is the same query for foth methods (Fillby and getdataby).

    Friday, September 16, 2016 12:36 PM
  • just a long shot:

    you have related tables in your dataset, and the shippedmariners table has a parent table, maybe 'ship',

    and one query returns the parent ship's id, while the other query doesn't. 

    If there's a parent id, then the dataset would complain that the corresponding parent record is not present in the dataset.

    You should either fill the parent table first with the corresponding parent(s), or adapt the query so that is doesn't return a parent id.

    regards,

    Nico


    Regards, Nico <br/>


    The code block as shown above (fill then Get) fails as well. The parent table is loaded first. and I could adapt the query so it does not return the parent, but that would defeat the purpose of the relation.
    Friday, September 16, 2016 12:40 PM
  • Hi,

    in this query, you return the same number twice:

    Contacts.PRNumber AS CPRNumber

    and Shippment_Info.PRNumber,

    is that also the case in the other query? 

    Nico


    Regards, Nico <br/>

    Friday, September 16, 2016 12:46 PM
  • Yes PRNumber is common to both tables and is is duplicated in all query's. I realize It is superfluous, I just haven't removed it yet.

    Something I just noticed... I added the query posted after I built the relations. Prior to adding the second query I was returning related fields as expected, but after the second query was added none of the parent fields are being returned. Is it possible that the addition of queries breaks the relations?

    Friday, September 16, 2016 12:54 PM
  • Yes PRNumber is common to both tables and is is duplicated in all query's. I realize It is superfluous, I just haven't removed it yet.

    Something I just noticed... I added the query posted after I built the relations. Prior to adding the second query I was returning related fields as expected, but after the second query was added none of the parent fields are being returned. Is it possible that the addition of queries breaks the relations?

    My mistake on this... I was only inspecting 1 row, and it did not have a parent record. The relations still works as expected in the fill method, but not the getdata method.
    Friday, September 16, 2016 1:03 PM
  • A little more information.  I thought I would try to delete and rebuild the relation. Visual studio 2015 returns and error stating the the tablename.designer.vb cannot be modified at this time. If I try to blank out the expression from the parent column I get the same cannot be modified error from studio.
    Friday, September 16, 2016 1:12 PM
  • do both queries return the record without a parent?

    Does it not have a parent, or is the parent not present in the parent table?

    Are you sure in both cases, the parents are loaded first?

    Are you sure in both cases, the parents that are loaded are the same in both cases?

    did you try and set Dataset.EnforceConstraints = False, maybe then you can try and look in the child table and check if all parents are there in the parent table

    am i asking too many anoying questions?


    Regards, Nico <br/>

    Friday, September 16, 2016 1:14 PM
  • do both queries return the record without a parent?

    Does it not have a parent, or is the parent not present in the parent table?

    Are you sure in both cases, the parents are loaded first?

    Are you sure in both cases, the parents that are loaded are the same in both cases?

    did you try and set Dataset.EnforceConstraints = False, maybe then you can try and look in the child table and check if all parents are there in the parent table

    am i asking too many anoying questions?


    Regards, Nico <br/>

    The fill returns the parent, the getdata fails with ''Cannot find parent relation'

    Yes, it has a parent

    Yes, the parent is loaded first in both cases

    Yes, EnforceConstraints = False

    No, You are not asking too many questions

    Friday, September 16, 2016 1:33 PM
  • ok, i think I know what is going on:

    when you do a getdata, the code is generated for you, and getdata creates a table and dataset for you, and it is with enforceconstraints = true (default).

    when you do do a fill, YOU create the dataset AND you set the enforceconstraints to false. 

    The data that gets loaded is not consistent; there are child records that have no parent record.

    If you set EnforceConstraints to false, it ignores this fact, but you have inconsistent data.

    With getdata, EnforceConstraints is true, the data gets loaded, and the dataset protests because you have inconsistent data.


    Regards, Nico <br/>

    Friday, September 16, 2016 2:49 PM
  • ok, i think I know what is going on:

    when you do a getdata, the code is generated for you, and getdata creates a table and dataset for you, and it is with enforceconstraints = true (default).

    when you do do a fill, YOU create the dataset AND you set the enforceconstraints to false. 

    The data that gets loaded is not consistent; there are child records that have no parent record.

    If you set EnforceConstraints to false, it ignores this fact, but you have inconsistent data.

    With getdata, EnforceConstraints is true, the data gets loaded, and the dataset protests because you have inconsistent data.


    Regards, Nico <br/>

    interesting hypothesis. 3 points though

    1) There is only 1 record in the child table, that does have a record in the parent table. This record loads fine with the fill, but not the getdata.

    2) The relations were created as 'Relation Only', No constraints.

    3) the error seems to point at a missing relation, not a problem with the data.

    I'd like to test your theory, but don't know how to set the EnforceConstaraints=false for the getdata method.

     
    Friday, September 16, 2016 3:05 PM
  • you can't set enforceconstraints to false (unless perhaps with reflection), but you can test it by setting enforceconstraints to true in your fill. You should get the same error.

    Regards, Nico <br/>

    Friday, September 16, 2016 3:09 PM
  • Ok another mistake on my part...

    EnforceConstraints was set to true (Not false as I attested earlier), when I set EnforceConstraints = false both methods (Fill and get) return no data (With EnforceConstraits =true Fill returns data as expected, get fails), but the complaint about missing relation does not trigger. 

    Friday, September 16, 2016 3:27 PM
  • maybe it's time to show us the code?

    Regards, Nico <br/>

    Saturday, September 17, 2016 2:34 PM
  • maybe it's time to show us the code?

    Regards, Nico <br/>

    The Datasets, Table, Etc. were created with the dataset designer. The only code I wrote was posted above. If it would help, I can post the Dataset.designer.vb code (23600 lines).
    Saturday, September 17, 2016 7:44 PM
  • The Datasets, Table, Etc. were created with the dataset designer. The only code I wrote was posted above. If it would help, I can post the Dataset.designer.vb code (23600 lines).

    <click>

    This must be Candid Camera!

    Sunday, September 18, 2016 9:43 PM
  • So I found a work around...
      Dim x As New RelatedDataSets.RelatedTables.ShippedMarinersDataTable
            ShippedEmps.FillByShip(x, 3)
    
    I still don't understand why the fill method can find the relation and get method can't (both methods created by the designer), but this gives me a private table to work with that I was looking 
    Sunday, September 18, 2016 10:55 PM
  • So I found a work around...
      Dim x As New RelatedDataSets.RelatedTables.ShippedMarinersDataTable
            ShippedEmps.FillByShip(x, 3)
    I still don't understand why the fill method can find the relation and get method can't (both methods created by the designer), but this gives me a private table to work with that I was looking 

    http://www.entityframeworktutorial.net/what-is-entityframework.aspx

    http://www.vbforums.com/showthread.php?540421-Tutorial-An-Introduction-to-the-ADO-NET-Entity-Framework

    Monday, September 19, 2016 12:18 AM