I wrote this post months ago (apparently before I was registered as a subscriber) and didn't realize that it had never been accepted by the mailing list. So I'm posting the exact message again here:
At first I thought that this was a problem with NpgSQL, but I have just confirmed it also happens when I use MySql.Data.MySqlClient. The issue seems to be that the method DataAdapter.Update is not successfully acquiring values for the parameters the programmer specifies. For this reason it uses default null values for each parameter which results in a null constraint violation for parameters that cannot be null (like the key for example). This code is assumes that a table was created with "create table tableb(field_int2 int2, field_timestamp timestamp, field_numeric numeric);" and is taken straight from the user manual for NpgSQL: public static void UserManualAdapterExample() { string connectionString = "Server=localhost;" + "Database=testDB;" + "User ID=user1;" + "Password=password;"; NpgsqlConnection conn = new NpgsqlConnection(connectionString); conn.Open(); DataSet ds = new DataSet(); NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb", conn); da.InsertCommand = new NpgsqlCommand("insert into tableb(field_int2, field_timestamp, field_numeric) values (:a, :b, :c)", conn); da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", NpgsqlTypes.NpgsqlDbType.Smallint)); da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", NpgsqlTypes.NpgsqlDbType.Timestamp)); da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", NpgsqlTypes.NpgsqlDbType.Numeric)); da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input; da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input; da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input; da.InsertCommand.Parameters[0].SourceColumn = "field_int2"; da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp"; da.InsertCommand.Parameters[2].SourceColumn = "field_numeric"; da.Fill(ds); DataTable dt = ds.Tables[0]; DataRow dr = dt.NewRow(); dr["field_int2"] = 4; dr["field_timestamp"] = new DateTime(2003, 03, 03, 14, 0, 0); dr["field_numeric"] = 7.3M; dt.Rows.Add(dr); DataSet ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); } When I reach "ds.Merge(ds2)" I get this error: System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. at System.Data.Constraint.ThrowConstraintException () [0x00000] in <filename unknown>:0 at System.Data.DataSet.InternalEnforceConstraints (Boolean value, Boolean resetIndexes) [0x00000] in <filename unknown>:0 at System.Data.DataSet.set_EnforceConstraints (Boolean value) [0x00000] in <filename unknown>:0 at System.Data.MergeManager.Merge (System.Data.DataSet targetSet, System.Data.DataSet sourceSet, Boolean preserveChanges, MissingSchemaAction missingSchemaAction) [0x00000] in <filename unknown>:0 at System.Data.DataSet.Merge (System.Data.DataSet dataSet, Boolean preserveChanges, MissingSchemaAction missingSchemaAction) [0x00000] in <filename unknown>:0 at System.Data.DataSet.Merge (System.Data.DataSet dataSet) [0x00000] in <filename unknown>:0 If I remove "DataSet ds2 = ds.GetChanges();" and just call "da.Update(ds);" then a new row is added to the table that consists entirely of null values. As I mentioned above I used to think this was an issue with NpgSQL, but when I reimplemented my code to use MySQL I continued to see the same behavior. Here is my MySQL code which assumes there to be a table called "mytable" containing two varchars, id and name where id is the primary key: public static void DataAdapterTest() { MySqlConnection conn = new MySqlConnection(connectionString); MySqlDataAdapter da = CreateSqlDataAdapter(); DataSet ds = new DataSet(); da.Fill(ds, "mytable"); DataRow dr = ds.Tables["mytable"].NewRow(); dr["id"] = "007"; dr["name"] = "Bond"; ds.Tables["mytable"].Rows.Add(dr); da.Update(ds, "mytable"); } private static MySqlDataAdapter CreateSqlDataAdapter() { MySqlConnection conn = new MySqlConnection(connectionString); MySqlDataAdapter da = new MySqlDataAdapter(); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.SelectCommand = new MySqlCommand("SELECT id, name FROM mytable", conn); da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " + "VALUES (?id, ?name)", conn); da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " + "WHERE id=?oldId", conn); da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn); da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id"); da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id"); da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; return da; } When I run this code I get an error message saying that "Column 'id' cannot be null". Just like NpgSQL the update method is unable to populate the Parameters properly. I have created a work around that seems to work, but it makes me nervous so I would like see if I can't get the update method to function properly. Here is the work around: foreach (System.Data.DataTable dataTable in ds.Tables) { foreach (System.Data.DataRow row in dataTable.Rows) { //Update if the row was changed if(row.RowState.Equals(System.Data.DataRowState.Modified)) { foreach(NpgsqlParameter param in da.UpdateCommand.Parameters) { param.Value = row[param.SourceColumn]; } if(!da.UpdateCommand.Connection.State.Equals(System.Data.ConnectionState.Open)) { da.UpdateCommand.Connection.Open(); } da.UpdateCommand.ExecuteNonQuery(); } //Then I repeat this for the Insert and the Delete Commands } } As you can see the work around is kinda ugly and makes me nervous. Any help getting DataAdapter.Update to work would be greatly appreciated! Thanks, Chris PS I'm running MonoDevelop version 2.4 on Ubuntu 10.04.3 LTS, my version of Npgsql is 2.0.11.91 and MySQL is 6.4.3.0. -- View this message in context: http://mono.1490590.n4.nabble.com/DataAdapter-Update-throws-null-constraint-violation-tp3727327p4310715.html Sent from the Mono - General mailing list archive at Nabble.com. _______________________________________________ Mono-list maillist - Mono-list@lists.ximian.com http://lists.ximian.com/mailman/listinfo/mono-list