none
Cannot clear table because ForeignKeyConstraint enforces constraints RRS feed

  • Question

  • I am doin an asp.net page, when i try to clear my datatable i am getting the error

    i tried a few things but they dont seem to work here is all my code

    if (dt.ParentRelations.Count > 0)
                dt.ParentRelations.Clear();
            if (dt.ChildRelations.Count > 0)
            {
                dt.ChildRelations.Clear();
                dt.Constraints.Clear();
            }


            dt.Clear();
            DataTable dtErrs = new DataTable();
            dtErrs.Columns.Add(new DataColumn("tempRowId")); 
            dtErrs.Columns.Add(new DataColumn("ErrorMessage", typeof(System.String)));
            dtErrs.Columns[1].MaxLength = 250;

            foreach (UWBordereauDataContract contract in UWDataContracts)
            {
                DataRow dr = dt.NewRow();

                dr["Policyholder Name"] = contract.PolicyHolderName;
                dr["Policy Number"] = Regex.Replace(contract.PolicyNo, @"[\W]", "");
                

                if (contract.UploadErrors != null)
                {
                    dr["hasError"] = true;
                    string errs=string.Empty;
                    foreach( Dc.UploadErrorDataContract err in contract.UploadErrors)
                    {
                        errs += err.ErrorMessage ;

                        DataRow row = dtErrs.NewRow();
                        row["tempRowId"] = contract.Batch_Upld_Row_Id;
                        row["ErrorMessage"] = err.ErrorMessage;
                        dtErrs.Rows.Add(row);
                    }
                    dr["ErrorMessage"] = errs;
                }

                dt.Rows.Add(dr);
            }
            ds = new DataSet();
            ds.Tables.Add(dt);
            ds.Tables.Add(dtErrs);
           
            DataRelation rel = new DataRelation("relErrors",ds.Tables[0].Columns["tempRowId"],ds.Tables[1].Columns["tempRowId"]);
            ds.Relations.Add(rel);
            Session[uploadType] = dt;
            Session[uploadType+ "ds"] = ds;
            return dt;
    Monday, January 11, 2010 4:12 PM

Answers

  • While that will work, you then have to set up all your DataRelations again when you're done clearing the DataTables! Way more work than you want to do. Turning the EnforceConstraints off and then on again is much simpler and cleaner.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by dbdrtso1 Friday, May 7, 2010 8:01 PM
    Saturday, February 6, 2010 4:27 PM

All replies

  • I didn't read all of the code you posted,  but probably the easiest thing to do is turn off the constraints prior to doing whatever you're doing that violates constraints. That is done on the DataSet, not the DataTable. So, something like this:

    ds.EnforceConstraints = false;
    ds.Tables[0].Clear();
    ds.Tables[1]].Clear();
    ds.EnforceConstraints = true;

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, January 13, 2010 12:08 AM
  • I did the Following which seemed to work.  It clears all child relations for the table.  

     Sub Clear(ByVal table As DataTable)
            For Each r As DataRelation In table.ChildRelations           
                Clear(r.ChildTable)
            Next
            table.Clear()
        End Sub
    Thursday, February 4, 2010 12:49 PM
  • While that will work, you then have to set up all your DataRelations again when you're done clearing the DataTables! Way more work than you want to do. Turning the EnforceConstraints off and then on again is much simpler and cleaner.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by dbdrtso1 Friday, May 7, 2010 8:01 PM
    Saturday, February 6, 2010 4:27 PM