none
DataTable throws InvalidConstraintException on Clear() RRS feed

  • Question

  • Hi,

    I set up a button in my windows form that clears a datatable. It works fine most of the time. However, after running some other processes in the code it trows an InvalidConstraintException when I try to execute the DataTable.Clear() method due to a foreign key constraint with a child table. I've been trying to figure out what child table is and get rid of it, as well as clear out any data relations, but I can't seem to find them. I'm doing the following:

    dt1.ChildRelations.Clear();
    dt1.ParentRelations.Clear();
    dt1.Constraints.Clear();
    dt1.DataSet.Relations.Clear();
    dt1.Clear();


    Still throws the error. In the end I was able to stop getting the error by adding:

    dt1.DataSet.EnforceConstraints = false;

    That works, but I don't really want to turn off enforcing constraints- I just want to get rid of the table entirely so I can replace it with something else later.

     

    Suggestions appreciated.

    Wednesday, October 5, 2011 10:43 PM

Answers

  • Maybe this is the wrong way to go, but I have a UI that allows the user to perform certain kinds of operations, some of which create some child relations. I have a 'clear data' button that I want to dispose of all the data tables, regardless of what's happened so far. I was able to finally get it to work like this:

     

     

    dt.ChildRelations.Clear();
                dt.ParentRelations.Clear();
                dt.Constraints.Clear();
                if (dt.DataSet != null)
                {
                    dt.DataSet.Relations.Clear();
                    dt.DataSet.EnforceConstraints = false;
                    for (int i = 1; i < dt1.DataSet.Tables.Count; i++)
                    {
                        dt1.DataSet.Tables[i].Constraints.Clear();
                        dt1.DataSet.Tables.Remove(dt1.DataSet.Tables[i]);
                    }
                    dt.Columns.Clear();
                    dt.DataSet.Clear();
                }
    dt.clear();


     


    Monday, October 10, 2011 7:56 PM

All replies

  • Just found this: http://msdn.microsoft.com/en-us/library/system.data.invalidconstraintexception.aspx

     

    "If you call Clear, the InvalidConstraintException may be thrown if a ForeignKeyConstraint is enforced on the DataRowCollection. "


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions

    Thursday, October 6, 2011 12:24 PM
  • James,

    How do I find out what the foreign key relationship is- which other table and column it applies to?

    Thanks

    Thursday, October 6, 2011 10:35 PM
  • Hi,

    ForeignKeyConstraint objects are contained in the ConstraintCollection of a DataTable, which is accessed through the Constraints property.

    http://msdn.microsoft.com/en-us/library/system.data.foreignkeyconstraint.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, October 7, 2011 6:35 AM
    Moderator
  • Thanks Alan.  I'm trying this but it can still throw the exception. Is there some other way I should remove the constraint?

     

    dt1.Constraints.Clear();

    Friday, October 7, 2011 9:37 PM
  • @evil_iggy -- it seems to me that you should know which tables are child tables. Especially since you need to clear the data out of both parent and child. Why is that you don't know which tables are related?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, October 8, 2011 5:06 AM
  • Maybe this is the wrong way to go, but I have a UI that allows the user to perform certain kinds of operations, some of which create some child relations. I have a 'clear data' button that I want to dispose of all the data tables, regardless of what's happened so far. I was able to finally get it to work like this:

     

     

    dt.ChildRelations.Clear();
                dt.ParentRelations.Clear();
                dt.Constraints.Clear();
                if (dt.DataSet != null)
                {
                    dt.DataSet.Relations.Clear();
                    dt.DataSet.EnforceConstraints = false;
                    for (int i = 1; i < dt1.DataSet.Tables.Count; i++)
                    {
                        dt1.DataSet.Tables[i].Constraints.Clear();
                        dt1.DataSet.Tables.Remove(dt1.DataSet.Tables[i]);
                    }
                    dt.Columns.Clear();
                    dt.DataSet.Clear();
                }
    dt.clear();


     


    Monday, October 10, 2011 7:56 PM
  • Maybe this is the wrong way to go, but I have a UI that allows the user to perform certain kinds of operations, some of which create some child relations. I have a 'clear data' button that I want to dispose of all the data tables, regardless of what's happened so far. I was able to finally get it to work like this:

     

     

    dt.ChildRelations.Clear();
                dt.ParentRelations.Clear();
                dt.Constraints.Clear();
                if (dt.DataSet != null)
                {
                    dt.DataSet.Relations.Clear();
                    dt.DataSet.EnforceConstraints = false;
                    for (int i = 1; i < dt1.DataSet.Tables.Count; i++)
                    {
                        dt1.DataSet.Tables[i].Constraints.Clear();
                        dt1.DataSet.Tables.Remove(dt1.DataSet.Tables[i]);
                    }
                    dt.Columns.Clear();
                    dt.DataSet.Clear();
                }


     


    Just out of curiosity, was the issue related to the order of tables that were cleared?  That is, it would fail if you attempted to clear the ParentRelates first?

     

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Monday, October 10, 2011 7:58 PM
  • Yes, it is related to the order that the tables are cleared. It won't fail if you clear parentrelations first, as above. It will fail though if you don't clear the constraints for each table before removing the table and clearing the data set.
    Tuesday, October 11, 2011 6:10 PM
  • Yes, it is related to the order that the tables are cleared. It won't fail if you clear parentrelations first, as above. It will fail though if you don't clear the constraints for each table before removing the table and clearing the data set.

     

    Well, that is perfectly logical.  I'd think that you wouldn't/shouldn't be able to insert a new row into a child table before it had a matching row in the parent table.  Guess it'd be the same for removing rows too!


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, October 11, 2011 6:19 PM