How do I … identify dataset constraint type that has failed when I get exception: System.Data.ConstraintException: Failed to enable constraints. One or more …
20-Mar-1212 Leave a comment
How do I … identify dataset constraint type that has failed when I get exception:
This is a nice trick
System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Write a test that Fills the requried table. I use a helper class for datasets, but you could change this to put the tableadapter in here.
Then loop through the columns and remove the constraints
Also remove the primary key constraint
Prove that the Fill now works. Then stop turning the constraints off one-by-one until you find the type of constraint.
<TestMethod()> _ Public Sub FillTest() Dim dsValue As New <datasetname> Dim target As New <dataset>Helper(dsValue) For Each col As DataColumn In dsValue.<tablename>.Columns If col.Ordinal < 10 Then 'Use to reduce possible columns down col.MaxLength = -1 'Rem in or out to reduce possible constraints. And next line col.AllowDBNull = True End If Next dsValue.<tablename>.PrimaryKey = Nothing Dim aList = dsValue.<tablename>.Constraints Stop target.Fill End Sub
Optional helper methods
Private Sub SetAllowDBNULLTrue(dt As DataTable, Optional MinOrdinal As Integer = 0, Optional MaxOrdinal As Nullable(Of Integer) = Nothing) If Not MaxOrdinal.HasValue Then MaxOrdinal = dt.Columns.Count For Each col As DataColumn In dt.Columns If col.Ordinal > MinOrdinal AndAlso col.Ordinal < MaxOrdinal Then 'Use to reduce possible columns down If col.AllowDBNull = False Then Debug.Print("Changing column to allow null " + col.ColumnName) col.AllowDBNull = True End If End If Next End Sub Private Sub SetMaxLengthToMinus1(dt As DataTable, Optional MinOrdinal As Integer = 0, Optional MaxOrdinal As Nullable(Of Integer) = Nothing) If Not MaxOrdinal.HasValue Then MaxOrdinal = dt.Columns.Count For Each col As DataColumn In dt.Columns If col.Ordinal > MinOrdinal AndAlso col.Ordinal < MaxOrdinal Then 'Use to reduce possible columns down If col.MaxLength <> -1 Then Debug.Print("Changing column MaxLength to -1 for " + col.ColumnName) col.MaxLength = -1 'Rem in or out to reduce possible constraints. And next line End If End If Next End Sub