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
Stephen Shakespeare 