none
The row doesn't belong to the same dataset as this relation. RRS feed

  • Question


  • BusinessLogic bl = new BusinessLogic();
                    DataTable dt_account = bl.GetAccountDataTable();
                    DataTable dt_comm = bl.GetCommDataTable();
                    DataTable dt_meter = bl.GetMeterDataTable();
                    // Create DataSet and add tables
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt_account);
                    ds.Tables.Add(dt_comm);
                    ds.Tables.Add(dt_meter);
    
                    // Create relation
                    DataRelation objRelation = new DataRelation("objRelation",
                        new[] { dt_meter.Columns["me_acct"], dt_meter.Columns["me_suffix"] },
                        new[] { dt_account.Columns["ac_acct"], dt_account.Columns["ac_suffix"] });
    
    
    
                    foreach (DataRow meterRow in dt_meter.Rows)
                    {
                        MessageBox.Show("dt_meter Account = " + meterRow["me_acct"].ToString());
    
                        foreach (DataRow accountRow in meterRow.GetChildRows(objRelation))
                        {
                            MessageBox.Show("dt_account Account = " + accountRow["ac_acct"].ToString());
                        }
    
                    }

    When the attached code is executed the line "foreach (DataRow accountRow in meterRow.GetChildRows(objRelation))" produces the error "The row doesn't belong to the same dataset as this relation.".

    Can someone tell me what I am doing wrong. I am new to this and trying to figure it out.

    Thanks


    DrewT1755



    • Edited by DrewT1755 Friday, June 15, 2018 2:47 PM
    Friday, June 15, 2018 2:46 PM

Answers

  • "I am not sure what you mean by "(if it exists in the database)"."

    Normally in the database you'd set up a foreign key constraint that identifies the column(s) that must exist in the source and target tables. That is what you're setting up in your data relation. 

    "Am I detaching and attaching?"

    That was the discussion around removing the table from the dataset and adding it back to your new dataset. If your business layer just returned the dataset with all 3 queries as part of it you'd get back a single dataset with the 3 tables and the relations already set up. You wouldn't need any additional code.

    The more I think about your error the more I suspect the issue is actually with your data that you're reading. It is trying to apply the relationship but it is finding a row in one of the tables that doesn't exist in other. Hence the constraint is bad and the relationship cannot be enforced. Take a look at your data in both tables and see if there are any cases where the relationship doesn't work. If so then that is why it is failing. My gut instinct is maybe a null value somewhere. This would also lend credibility to the thought that your database isn't actually enforcing this constraint and therefore you probably shouldn't try to in the code either.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by DrewT1755 Friday, June 15, 2018 9:02 PM
    Friday, June 15, 2018 7:47 PM
    Moderator
  • You are correct, there are meter records with no corresponding account records. I will clean up my data and give it another shot.

    One more question please. In DataRelation(String, DataColumn, DataColumn, Boolean) would the boolean set to false correct this problem? And if so what would I use for the parent and child columns?

    Thanks


    DrewT1755

    • Marked as answer by DrewT1755 Friday, June 15, 2018 10:19 PM
    Friday, June 15, 2018 8:52 PM

All replies

  • In order for a relation to be enforced the rows have to be part of the same dataset. The error is indicating that you're trying to take a row from one dataset and add it to another which isn't allowed.

    Without knowing what your GetAccountDataTable and GetMeterDataTable methods do I suspect under the hood that they create a DataSet and then get the table from that (via Fill). As a result the tables are from different datasets and therefore you cannot set up a relation.

    Ideally your business logic should create a single dataset and return all the tables you need as part of the single request. This would solve the problem.

    If that isn't possible then you need to detach the table from the set before it is returned. Alternatively don't use the dataset if you don't need it.

    //Needs to occur inside your business layer's Get... methods
    mydataset.Tables.Remove(mytable)

    Once you do that the tables aren't hooked to the dataset anymore. However you'll now get an error about the rows not being associated with relation. You need to add the relation to the dataset as well.

    var objRelation = new DataRelation(...);
    ds.Relations.Add(objRelation);


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, June 15, 2018 3:03 PM
    Moderator
  • My  GetAccountDataTable and GetMeterDataTable methods are attached and were filling a data adapter, but I have taken that out. Now I am receiving the error " 'column' argument cannot be null. Parameter name: column".

    The error occurs when the line 

    DataRelation objRelation = new DataRelation("objRelation",
                        new[] { dt_meter.Columns["me_acct"], dt_meter.Columns["me_suffix"] },
                        new[] { dt_account.Columns["ac_acct"], dt_account.Columns["ac_suffix"] });

    is executed.

    Any ideas?

    Thanks you.

            public DataTable GetAccountDataTable()
            {
                SqlConnection sqlcon = new SqlConnection();     
                DataTable dt = new DataTable();
                sqlcon.ConnectionString = constring;
    
                if (sqlcon.State == ConnectionState.Closed)
                {
                    sqlcon.Open();
                }
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter("GetAccounts", sqlcon);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    //da.Fill(dt);
                    return dt;
                }
                catch
                {
                    throw;
                }
    
            }
    
    
    
            public DataTable GetMeterDataTable()
            {
                SqlConnection sqlcon = new SqlConnection();
                DataTable dt = new DataTable();
                sqlcon.ConnectionString = constring;
    
                if (sqlcon.State == ConnectionState.Closed)
                {
                    sqlcon.Open();
                }
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter("GetMeters", sqlcon);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    //da.Fill(dt);
                    return dt;
                }
                catch
                {
                    throw;
                }
    
            }



    DrewT1755


    • Edited by DrewT1755 Friday, June 15, 2018 4:05 PM
    Friday, June 15, 2018 4:04 PM
  • If you comment out the Fill method then your datatable will be empty. The DA doesn't populate anything until the Fill call.

    So your methods weren't using a dataset at all before? Did you add in the logic to add the relation to the dataset and then try running your original code again?


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, June 15, 2018 4:08 PM
    Moderator
  • It dawned on me that I would need to put the fill back into my methods and I added the code to add the relation to the dataset, but then get the error "This constraint cannot be enabled as not all values have corresponding parent values", when this line of code

    ds.Relations.Add(objRelation);

    is executed.

                   BusinessLogic bl = new BusinessLogic();
                    DataTable dt_account = bl.GetAccountDataTable();
                    DataTable dt_comm = bl.GetCommDataTable();
                    DataTable dt_meter = bl.GetMeterDataTable();
                    // Create DataSet and add tables
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt_account);
                    ds.Tables.Add(dt_comm);
                    ds.Tables.Add(dt_meter);
    
                    // Create relation
                    DataRelation objRelation = new DataRelation("objRelation",
                        new[] { dt_meter.Columns["me_acct"], dt_meter.Columns["me_suffix"] },
                        new[] { dt_account.Columns["ac_acct"], dt_account.Columns["ac_suffix"] });
    
                    ds.Relations.Add(objRelation);
    
    
    
                    foreach (DataRow meterRow in dt_meter.Rows)
                    {
                        MessageBox.Show("dt_meter Account = " + meterRow["me_acct"].ToString());
    
                        foreach (DataRow accountRow in meterRow.GetChildRows(objRelation))
                        {
                            MessageBox.Show("dt_account Account = " + accountRow["ac_acct"].ToString());
                        }
    
                    }
    
    

    Can you shed some light on this.

    Thank you 


    DrewT1755

    Friday, June 15, 2018 6:50 PM
  • It sounds like maybe you're referencing a column that doesn't exist or something. In my sample code I created a DataTable that had the columns you specified and the relation worked correctly. You might want to verify the columns are correct and the types match.

    Note that earlier I mentioned it might save you some code to simply combine your queries together and then fill the dataset will all 3 results at once. Then the relationship should be created automatically (if it exists in the database) and you wouldn't have to detach/attach again. This would also solve your issue.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, June 15, 2018 6:58 PM
    Moderator
  • I appreciate your responses and as you can see the account.ac_acct is varchar(14), account.ac_suffix is tinyint, meter.me_acct is varchar(14) and meter.me_suffix is tinyint. 

    I am not sure what you mean by "(if it exists in the database)". How does the relationship have to exist in the database?

    Also I am not sure what you mean by "you wouldn't have to detach/attach again". Am I detaching and attaching?

    Thank you for your time.

    By the way I am a Taylor too.

    meteraccount


    DrewT1755

    Friday, June 15, 2018 7:36 PM
  • "I am not sure what you mean by "(if it exists in the database)"."

    Normally in the database you'd set up a foreign key constraint that identifies the column(s) that must exist in the source and target tables. That is what you're setting up in your data relation. 

    "Am I detaching and attaching?"

    That was the discussion around removing the table from the dataset and adding it back to your new dataset. If your business layer just returned the dataset with all 3 queries as part of it you'd get back a single dataset with the 3 tables and the relations already set up. You wouldn't need any additional code.

    The more I think about your error the more I suspect the issue is actually with your data that you're reading. It is trying to apply the relationship but it is finding a row in one of the tables that doesn't exist in other. Hence the constraint is bad and the relationship cannot be enforced. Take a look at your data in both tables and see if there are any cases where the relationship doesn't work. If so then that is why it is failing. My gut instinct is maybe a null value somewhere. This would also lend credibility to the thought that your database isn't actually enforcing this constraint and therefore you probably shouldn't try to in the code either.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by DrewT1755 Friday, June 15, 2018 9:02 PM
    Friday, June 15, 2018 7:47 PM
    Moderator
  • You are correct, there are meter records with no corresponding account records. I will clean up my data and give it another shot.

    One more question please. In DataRelation(String, DataColumn, DataColumn, Boolean) would the boolean set to false correct this problem? And if so what would I use for the parent and child columns?

    Thanks


    DrewT1755

    • Marked as answer by DrewT1755 Friday, June 15, 2018 10:19 PM
    Friday, June 15, 2018 8:52 PM
  • >>This would also lend credibility to the thought that your database isn't actually enforcing this constraint and therefore you probably shouldn't try to in the code either.<<

    And the way to disable the constraints, is when you add create the DataRelation, the last parameter is a bool to indicate whether to create the constraint or not. Set it to false ... at least you can see if the error goes away.

    DataRelation objRelation = new DataRelation("objRelation",
        new[] { dt_meter.Columns["me_acct"], dt_meter.Columns["me_suffix"] },
        new[] { dt_account.Columns["ac_acct"], dt_account.Columns["ac_suffix"] }, false);


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, June 15, 2018 8:55 PM
    Moderator
  • So the false parameter does not have to reside in the ds.Relations.Add(objRelation); statement?


    DrewT1755

    Friday, June 15, 2018 9:03 PM
  • So the false parameter does not have to reside in the ds.Relations.Add(objRelation); statement?


    You could set the false parameter in either place (when creating the new DataRelation, or in the ds.Relations.Add()) ... it doesn't matter.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, June 15, 2018 10:40 PM
    Moderator