none
"Failed to enable constraints" error??? RRS feed

  • Question


  • In my form load method I have the following generated code (TO DO comments omitted):

      private void frmMain_Load(object sender, EventArgs e)
            {
               
                 this.TransactionTableAdapter.Fill(this.expenditure.Transaction);

                 this.SubCategoryTableAdapter.Fill(this.expenditure.SubCategory);

                 this.CategoryTableAdapter.Fill(this.expenditure.Category);

                 this.PayeeTableAdapter.Fill(this.expenditure.Payee);
            }

    which has been generated from the .xsd diagram.

    However running the application gives an Unhandled Exception "Failed to enable constraints. One or
    more rows contain values violating non-null, unique or foreign key constraints", with the SubCategory
    table identified as the offending item.

    I have a 1:many relationhip between Category and SubCategory where PK (C_Id) in Category (1) has a
    relation with FK S_CatId in SubCategory (many). I have checked the data in both tables. Each table's
    PK entry is unique and, as far as I can ascertain, completely satisfies the relationship requirements,
    and there are no null entries in any of the tables' rows.

    Not sure where to go from here. Advice appreciated.

    Thanks

    Wednesday, October 29, 2008 4:29 AM

Answers

  • OK, I figured it out:

                cmbxPayee.DataSource = dataSet.Payee;
                cmbxPayee.DisplayMember = dataSet.Payee.P_NameColumn.ToString();
                cmbxPayee.ValueMember = dataSet.Payee.P_IdColumn.ToString();
                cmbxCategory.DataSource = dataSet.Category;
                cmbxCategory.DisplayMember = dataSet.Category.C_DescriptionColumn.ToString();
                cmbxCategory.ValueMember = dataSet.Category.C_IdColumn.ToString();

    This does what I want.
    Friday, October 31, 2008 4:19 AM

All replies

  • You can use DataTable.GetErrors() to get the DataRows that are specifically flagged as failing the constraint.

     

    Each failing DataRow.RowError should read something like "ForeignKeyConstraint <constraint name> requires the child key values (<child values from columns in constraint>) to exist in the parent table."

     

    Examining the data of those rows may highlight a specific problem that wasn't previously noticed.

     

    Wednesday, October 29, 2008 3:52 PM
    Moderator
  • Mark: I'm still earening C# and LINQtoDataSets. How would I specify the use of DataTable.GetErrors() given the typed datasets that the VS Designer has generated?

    I found the following in the MSDN documentation:

    private void PrintAllErrs(DataSet dataSet)
            {
                DataRow[] rowsInError;

                foreach (DataTable table in dataSet.Tables)
                {
                    // Test if the table has errors. If not, skip it.
                    if (table.HasErrors)
                    {
                        // Get an array of all rows with errors.
                        rowsInError = table.GetErrors();
                        // Print the error of each column in each row.
                        for (int i = 0; i < rowsInError.Length; i++)
                        {
                            foreach (DataColumn column in table.Columns)
                            {
                                Console.WriteLine(column.ColumnName + " " +
                                    rowsInErrorIdea.GetColumnError(column));
                            }
                            // Clear the row errors
                            // rowsInErrorIdea.ClearErrors();
                            Console.ReadLine();
                        }
                    }
                }

    I'm not sure how to specify the parameter to pass to PrintAllErrs. Or is there a simpler way given the generated code I quoted in my original post?
    Thursday, October 30, 2008 3:07 AM
  • A TypedDataSet is a DataSet, so you should just be able to pass it to your function.

    Code Snippet
    PrintAllErrors(this.expenditure);

     

    For the constraint exception, that particular error message is only at the row level, not column level.

    You could get away with just

    Code Snippet

      foreach(DataRow row in this.expenditure.SubCategory) {

        if (row.HasErrors) {

          Console.WriteLine("RowError={0}", row.RowError);

          foreach(DataColumn column in row.GetColumnsInError()) {

              Console.WriteLine("ColumnError={0}: {1}", column.ColumnName, row.GetColumnError(column));

          }

        }

      }

     

    That ConstraintException can be thrown for more than 3 reasons.

    The purpose of looking at the Row/Column errors to help understand why.

     

    Does your database have similar constraints that are being used in the DataSet?

        If not, do you want the foreign key constraint in the DataSet or just the DataRelation?

                 are you attempting to enforce non-null or uniqeness on another DataColumn?

        If yes, is the database being changed by other user at the same time?

     

     

     

     

    Thursday, October 30, 2008 7:21 PM
    Moderator
  • Mark: I decided to start again - I think there was an inconsistency between my table design and the .xsd diagram. I changed the design of one of the tables after the .xsd diagram was generated, assuming that the diagram and the underlying code would be refactored.

    Any way I now don't get the constraints error and have filled my dataset as shown below.

    I want to fill a combo box with C_Description so that, having selected a C_Description,  I can use C_Id to fill a column in a different table. I tried the query shown below  but got an error: "
    Complex DataBinding accepts as a data source either an IList or an IListSource"


     private void frmMain_Load(object sender, EventArgs e)
            {
                Expenditure dataSet = new Expenditure();
                new ExpenditureTableAdapters.TransactionTableAdapter().Fill(dataSet.Transaction);
                new ExpenditureTableAdapters.PayeeTableAdapter().Fill(dataSet.Payee);
                new ExpenditureTableAdapters.CategoryTableAdapter().Fill(dataSet.Category);
                new ExpenditureTableAdapters.SubCategoryTableAdapter().Fill(dataSet.SubCategory);

                var theCategories = from aCategory in dataSet.Category
                                    select new { aCategory.C_Id, aCategory.C_Description };
              
                cmbxCategory.DataSource = theCategories;
            }

    I changed the DataSource line to cmbxCategory.DataSource = dataSet.Category;
    and this compiled OK. What I am unsure of is how to display C_Description in the combo box and then pick up its related C_Id.

    Your advice would be appreciated again.


    Friday, October 31, 2008 3:44 AM
  • OK, I figured it out:

                cmbxPayee.DataSource = dataSet.Payee;
                cmbxPayee.DisplayMember = dataSet.Payee.P_NameColumn.ToString();
                cmbxPayee.ValueMember = dataSet.Payee.P_IdColumn.ToString();
                cmbxCategory.DataSource = dataSet.Category;
                cmbxCategory.DisplayMember = dataSet.Category.C_DescriptionColumn.ToString();
                cmbxCategory.ValueMember = dataSet.Category.C_IdColumn.ToString();

    This does what I want.
    Friday, October 31, 2008 4:19 AM