CODEWITHSUNDEEP
c#.netdataset
Dala
Dala

Reputation: 1889

What triggers ConstraintException when loading DataSet?

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.

Upvotes: 36

Views: 15199

Answers (5)

Olivier de Rivoyre
Olivier de Rivoyre

Reputation: 1597

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);
}

Upvotes: 3

doekman
doekman

Reputation: 19268

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.

Upvotes: 3

Jonathan Webb
Jonathan Webb

Reputation: 1573

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

Hope this helps.

Upvotes: 1

to StackOverflow
to StackOverflow

Reputation: 124696

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());
}

Upvotes: 23

Nikki9696
Nikki9696

Reputation: 6348

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

Upvotes: 12

Related Questions