none
Enabling constraints on a DataSet issue RRS feed

  • Question

  • Hi,

     

    After filling a DataSet i get the error below...

     

    "Failed to enable constraints. One or more rows  contain values violating non-null, unique, or foreign-key  constraints."

    I though I understood completely what the problem meant and how to deal with it, or so I thought!

     

    This is what I did...

     

    After filling the dataset I checked for errors using DataSet.HasErrors property and then, if true, ran a Foreach though all DataTables checking for errors.  If any tables had errors, i am (for now) deleting each row in the DataRow array returned by DataTable.GetErrors() method by calling DataRow.Delete().  After this, I call DataSet.AcceptChanges() which actually removes any offending rows.  After that I can happily call DataSet.EnforceConstraints = true.  This has worked with a DataSet that did indeed have some child rows not having parent rows through a DataRelation.

     

    However, I have a situation where my DataSet has no errors i.e. DataSet.HasErrors returns false and yet I get the "Failed to enable constraints..." error when I set .EnforceConstraints to true.  Even though the dataset reports to have no errors I also tried and made a double check on all the containing DataTables for errors.  They do not have any!!

     

    Does anyone now what else could cause this problem...

     

    //////

     

    DataSet ds = new DataSet();

    if (ds.HasErrors)

    {

    // No errors at this point! so this code is not executed

    foreach (DataTable table in ds.Tables)

    {

    if (table.HasErrors)

    {

    DataRow[] errorRows = table.GetErrors();

    foreach (DataRow errorRow in errorRows)

    {

    errorRow.Delete();

    }

    }

    }

    }

    ds.AcceptChanges();

    ds.EnforceConstraints = true; // this fails although the dataset report no errors!!

     

    Any help appreciated
    Tuesday, April 8, 2008 7:43 PM

Answers

  • To answer your question, HasErrors supports Update (which updates the database) but not AcceptChanges. AcceptChanges updates the Dataset, but this has no impact on the database.

     

    If you want to ensure the integrity of the data, with respect to the defined constraints of your Dataset, you can catch the exception that is raised when a constraint is violated. Sounds like you're enforcing constraints but you're just not catching the exception so that you can do whatever it is you need to do when this happens.

     

     

     

    Thursday, April 10, 2008 4:51 PM
  • I'm not aware of a method for identifying the rows which violate constraints at the DataTable/DataSet level when accepting batch changes (unless it can be done with a custom-defined exception). I think the assumption is that they will be identified at update, which unfortunately you aren't doing in this instance. In addition, I think the goal of the EnforceContraints option was to be able to disable constraints in order to speed up updates on the DataTable/DataSet.

    I think that if you want to identify a row that violates a constraint then you will have to set this option to True so it's flagged at the time the row is accepted to the DataTable/DataSet. If the updates are accepted to the DataTable/DataSet as a batch then you won't be able to indentify them individually unless the data is pushed to the database (Update).

    Monday, April 14, 2008 1:08 PM

All replies

  • The HasErrors property will not be set until the Update method of the DataAdapter is called. It's typically used when the ContinueUpdateOnError property is set to True, so that you can better control the behavior of your application when batch updates fail.

     

     

     

    Wednesday, April 9, 2008 1:00 PM
  • Thankyou for your reply.

     

    Can I give a little more detail...

     

    In my scenario, a DataAdapter is used initially to fill the tables, each of which are then added to the DataSet (which is actually a typed Dataset that contains DataRelations etc).  The set is then sent to a PDA device for use.  I wanted to ensure the integrity of the dataset before it is loaded on the device.  Before it is sent, I attempt to enforce constraints on the set by setting the property to true and thus confirming integrity.

     

    There is obviously something I am missing here.  As I understand, by calling AcceptChanges(), the rowstate for all rows would be set to unchanged.  Is this not what the end result of calling Update on a DataAdapter does but without pushing the data changes back to the database?  All rows marked as deleted are actually removed, modified and newly added rows are accepted.  Why can't the HasErrors property be checked at this point?

     

    How can I get the dataset to allow the enforcing of constraints as I am not using the DataAdapter to push the changes back?

     

    I am sorry if this situation has not been described very well.

     

    Thanks again 

     

     

    Wednesday, April 9, 2008 9:25 PM
  • To answer your question, HasErrors supports Update (which updates the database) but not AcceptChanges. AcceptChanges updates the Dataset, but this has no impact on the database.

     

    If you want to ensure the integrity of the data, with respect to the defined constraints of your Dataset, you can catch the exception that is raised when a constraint is violated. Sounds like you're enforcing constraints but you're just not catching the exception so that you can do whatever it is you need to do when this happens.

     

     

     

    Thursday, April 10, 2008 4:51 PM
  • Thanks again for your reponse...

     

    Your answer confirms my understanding regarding the effects of the Update and AcceptChanges methods. 

     

    Yes I could 'catch the exception' which I am happy to do.  How do I then pin point the offending rows, rectify them by what ever is necessary to enable me to finally set EnforceConstraints to true without an exception?  Can you get that level of detail from the thrown ConstraintException object?

     

    Thanks

     

    Friday, April 11, 2008 7:01 PM
  • I'm not aware of a method for identifying the rows which violate constraints at the DataTable/DataSet level when accepting batch changes (unless it can be done with a custom-defined exception). I think the assumption is that they will be identified at update, which unfortunately you aren't doing in this instance. In addition, I think the goal of the EnforceContraints option was to be able to disable constraints in order to speed up updates on the DataTable/DataSet.

    I think that if you want to identify a row that violates a constraint then you will have to set this option to True so it's flagged at the time the row is accepted to the DataTable/DataSet. If the updates are accepted to the DataTable/DataSet as a batch then you won't be able to indentify them individually unless the data is pushed to the database (Update).

    Monday, April 14, 2008 1:08 PM
  • Thanks for your answer and time

     

     

    Monday, April 14, 2008 3:05 PM