[Mono-list] DataAdapter.Update throws null constraint violation

[Chris Derrick]

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.