What triggers ConstraintException when loading DataSet?

Question

How can I find out which column and value is violating the constraint? The exception message isn't helpful at all:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

I'd give you more up-votes if I could. This is a thorn in my side as well. — Austin Salonen, Sep 26 '08 at 15:19

score 23 · Answer 1 · answered Sep 26 '08 at 16:59

Like many people, I have my own standard data access components, which include methods to return a DataSet. Of course, if a ConstraintException is thrown, the DataSet isn't returned to the caller, so the caller can't check for row errors.

What I've done is catch and rethrow ConstraintException in such methods, logging row error details, as in the following example (which uses Log4Net for logging):

...
try
{
    adapter.Fill(dataTable); // or dataSet
}
catch (ConstraintException)
{
    LogErrors(dataTable);
    throw;
}
...

private static void LogErrors(DataSet dataSet)
{
    foreach (DataTable dataTable in dataSet.Tables)
    {
        LogErrors(dataTable);
    }
}

private static void LogErrors(DataTable dataTable)
{
    if (!dataTable.HasErrors) return;
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat(
        CultureInfo.CurrentCulture,
        "ConstraintException while  filling {0}",
        dataTable.TableName);
    DataRow[] errorRows = dataTable.GetErrors();
    for (int i = 0; (i < MAX_ERRORS_TO_LOG) && (i < errorRows.Length); i++)
    {
        sb.AppendLine();
        sb.Append(errorRows[i].RowError);
    }
    _logger.Error(sb.ToString());
}

Your LogErrors method is great! Literally saved me today. – Dave Apr 14 '10 at 22:36 — Dave, Apr 14 '10 at 22:36

score 12 · Accepted Answer · answered Sep 26 '08 at 15:27

There is a property called RowError you can check.

See http://dotnetdebug.net/2006/07/16/constraintexception-a-helpful-tip/

Edited to add this link showing iteration of rows to see which had errors.

http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic58812.aspx

score 3 · Answer 3 · answered Oct 29 '08 at 13:00

3

When you use a strong typed dataset and used the visual designer (xsd): to access tbl.Rows[0].RowError information, you need to create the Fill method.

You can't use the Get method, since the DataTable is instanced within generated code.

answered Oct 29 '08 at 13:00

doekman

18,750
20
65
86

like you said, you need a fill. Very strange; why would I need a fill statement when i'm only getting some data (basic select). Somewhere inside thought this Fill method is used by .net it seems. After i generated a Fill method (no change on the get) it worked. – Obelix Dec 30 '13 at 14:29

score 3 · Answer 4 · answered May 11 '16 at 16:07

For googlers who want a snippet to get more details on the ConstraintException:

try
{
    ds.EnforceConstraints = true;
}
catch (ConstraintException ex)
{
    string details = string.Join("",
        ds.Tables.Cast<DataTable>()
            .Where(t => t.HasErrors)
            .SelectMany(t => t.GetErrors())
            .Take(50)
            .Select(r => "\n - " + r.Table.TableName + "[" + string.Join(", ", r.Table.PrimaryKey.Select(c => r[c])) + "]: " + r.RowError));
    throw new ConstraintException(ex.Message + details);
}

score 1 · Answer 5 · edited May 23 '17 at 11:53

1

I added some code that I've found to be useful in debugging ConstraintException occurrences here

Hope this helps.

edited May 23 '17 at 11:53

Community

1
1

answered Oct 10 '08 at 13:05

Jonathan Webb

1,573
1
17
26

What triggers ConstraintException when loading DataSet?

5 Answers5

Linked