How do I … identify dataset constraint type that has failed when I get exception: System.Data.ConstraintException: Failed to enable constraints. One or more …

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: