0

I have the notorious "failed to enable constraints" exception, but none of the answers from this question are applicable because I'm not using a DataSet or TableAdatper:

DataTable tblTypes = connVeekun.ExecuteDataTable("SELECT id, damage_class_id, " +
    "(SELECT name FROM type_names WHERE type_names.type_id = types.id AND local_language_id = 1) AS name_ja, " +
    "(SELECT name FROM type_names WHERE type_names.type_id = types.id AND local_language_id = 9) AS name_en, " +
    "(SELECT name FROM type_names WHERE type_names.type_id = types.id AND local_language_id = 5) AS name_fr, " +
    "(SELECT name FROM type_names WHERE type_names.type_id = types.id AND local_language_id = 8) AS name_it, " +
    "(SELECT name FROM type_names WHERE type_names.type_id = types.id AND local_language_id = 6) AS name_de, " +
    "(SELECT name FROM type_names WHERE type_names.type_id = types.id AND local_language_id = 7) AS name_es, " +
    "(SELECT name FROM type_names WHERE type_names.type_id = types.id AND local_language_id = 3) AS name_ko " +
    "FROM types ORDER BY id");

Helper method:

/// <summary>
/// Runs a command and returns a DataTable containing its results.
/// </summary>
/// <param name="db">Open data connection</param>
/// <param name="sqlstr">SQL string</param>
/// <param name="_params">List of parameters to use with the SQL</param>
public static DataTable ExecuteDataTable(this DbConnection conn, String sqlstr, params IDataParameter[] _params)
{
    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = sqlstr;
    cmd.Parameters.AddRange(_params);
    DbDataReader reader = cmd.ExecuteReader();
    DataTable result = new DataTable();
    result.Load(reader);
    return result;
}

The failed constraint is that there's a null value in the name_ko column. Since I'm not using a DataAdapter, I have no way to change the (inferred) schema or disable constraints. It's important in my application that I preserve the nullness of the name_ko column so modifying the SQL to remove nulls is not an option.

The data is from Eevee's Pokédex.

Community
  • 1
  • 1
mm201
  • 526
  • 4
  • 15
  • so there null values are allowed for type_names.name but you have a not null constraint on name_ko anyway? that would be really strange ... – PrfctByDsgn Dec 02 '14 at 10:14
  • The database itself has no such constraints. The constraints are both generated and fail within `DataTable.Load()`. I'm beginning to speculate the problem could be a bug in either the SQLite connector or ADO.Net itself. For now, I've worked around the issue by not using `DataTable`s at all but would still appreciate an answer since I use this helper a lot and it's never failed on me before now. – mm201 Dec 03 '14 at 15:08

1 Answers1

0

From the docs:

The method also retrieves constraint information from the result set for all added columns. Except for the case of Primary Key constraint, this constraint information is used only if the current DataTable does not contain any columns at the start of the load operation.

Looks like if you set the schema for the table (by creating all the columns) and then load it, you will get around the constraint issue, because he method will not infer the constraints.

dbugger
  • 15,868
  • 9
  • 31
  • 33
  • The way my code actually works is there's a helper method which is passed an SQL string and connection and returns the data table, so knowledge of the columns is out of scope. I'll add this to my question. – mm201 Nov 26 '14 at 20:56
  • Doesn't change your problem. DataTable.Load method does what it does. – dbugger Nov 26 '14 at 21:01