DataTable load() constraint error

Question

I've written a method below using reflection to load several strongly typed datatables in a .NET application. If i were to run as is below, everything works - including no thrown exceptions. But if I use the commented portion instead (holding everything else the same), then i get Failed to enable constraints error described here:enter link description here.

If I look at what is inside the errors array, it always the following:

"Column 'AEDelegateName' does not allow DBNull.Value."

and the ItemArray for an error will look something like:

[0] = {}
[1] = "Some Value"

That surprises me, since I would only expect 1 column in a script that selects 1 column and not 2 like what it indicated above. I also imagine this is close to the problem since one of them appears to be null.

My script does not return null, and I can quickly and visually confirm it, as well as say things like NOT NULL in the query i use.

private void GetData(string query, Component tableAdapter)
{
    OracleCommand command = new OracleCommand();
    command.Connection = conn;
    command.CommandText = query;
    command.CommandType = CommandType.Text;
    command.CommandTimeout = 3000;
    OracleDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
    MethodInfo[] methods = tableAdapter.GetType().GetMethods();
    MethodInfo getDataMethod = tableAdapter.GetType().GetMethod("GetData");
    DataTable table = (DataTable)getDataMethod.Invoke(tableAdapter, null);
    Type[] paramTypes = new Type[] { table.GetType() };
    MethodInfo updateMethod = tableAdapter.GetType().GetMethod("Update", paramTypes);
    foreach (DataRow row in table.Rows)
    {
        row.Delete();
    }
    //try
    //{
    //    if (reader.HasRows)
    //    {
    //        table.Load(reader, LoadOption.OverwriteChanges, FillErrorHandler);
    //    }
    //}
    //catch (Exception e)
    //{
    //    DataRow[] errors = table.GetErrors();
    //}
    while (reader.Read())
    {
        try
        {
            List<object> newRow = new List<object>();
            for (int i = 0; i < reader.FieldCount; ++i)
            {
                object currentValue = reader.GetValue(i);
                Debug.WriteLine("Value: "+currentValue);
                newRow.Add(currentValue);
            }
            table.Rows.Add(newRow.ToArray());
        }
        catch (ConstraintException e)
        {
            DataRow[] errors = table.GetErrors();
        }
    }            
    updateMethod.Invoke(tableAdapter, new object[]{table});
    reader.Close();
}

Could you describe the relation between the `DataTable table` and the query being run through `OracleDataReader`? I have the feeling that the schema for both are different and that `DataTable.Load()` overwrites the definition thus triggering the constraint. — Caramiriel, Mar 13 '13 at 21:45
.... @Caramiriel might be on the right track, but I'm going to take an educated WAG and suggest adding `AcceptChanges()` after the row delete loop. — radarbob, Mar 13 '13 at 22:10
_This means that every table column created with the constraint of "not null" or "foreign key" must also be present in the result of your SQL statement or stored procedure._ [from this SO Thread](http://stackoverflow.com/questions/7026566/failed-to-enable-constraints-one-or-more-rows-contain-values-violating-non-null) — radarbob, Mar 13 '13 at 22:18

score 1 · Accepted Answer · answered Mar 13 '13 at 23:03

Per the documentation for DataTable.Load Method (IDataReader, LoadOption), I suspect that you may be encountering the behavior described in the excerpt below. Have you checked the number of columns returned from your query versus the number of columns on your DataTable? Does the name of the column returned from the query match the desired column name in your DataTable?

Condition: The schemas are compatible, but the loaded result set schema contains fewer columns than does the DataTable.

Behavior: If a missing column has a default value defined or the column's data type is nullable, the Load method allows the rows to be added, substituting the default or null value for the missing column. If no default value or null can be used, then the Load method throws an exception. If no specific default value has been supplied, the Load method uses the null value as the implied default value.

Your code in the while loop is probably working because it makes no attempt to match schema. It just fills values positionally and will succeed as long as the types are compatible, no constraints are violated, and the array doesn't contain more items than the row has columns.

DataTable load() constraint error

1 Answers1