news:1143588787.717219.309660@v46g2000cwv.googlegroups.com...
> This is completely baffling me and I'm hoping someone can help me
> understand why this doesn't work.
>
> I'm updating existing rows in a MS SQL table by reading them into a
> datatable, modifying the values then updating the database using the
> update method of SqlDataAdapter.
> If I set a value of a field to the same value as the default value for
> that column the value resorts back to its previous value when I update
> the table.
>
> For example:
> The definition for Field1 is a varchar where Allow Nulls is false and
> the default value is ''.
> I have filled a datatable with the existing rows from my sql table.
>
> When I change Field1 to any value other than '' the data is saved
> correctly when I run the update method on the SqlDataAdapter.
> If I set the value of Field1 to '' the value is ignored and the
> previously saved value is saved.
>
> This is my select command:
> select Col_ID, Field1 from MyTable
>
> This my update command/parameter:
>
> sqlDA.UpdateCommand = new SqlCommand("update MyTable set Field1=@Field1
> where Col_ID=@Col_ID", sqlCnn);
> sqlDA.UpdateCommand.Parameters.Add("@Field1", SqlDbType.VarChar, 50,
> "Field1");
> sqlDA.UpdateCommand.Parameters.Add("@Col_ID", SqlDbType.Int, 4,
> "Col_ID");
>
> To ensure that I wasn't completely insane, I watched the events in SQL
> Profiler and sure enough, my values were getting replaced with the
> original values. Following is the actual statement displayed in SQL
> Profiler. Notice the value for Field1 is being saved as SALEPR which is
> the original value:
>
> exec sp_executesql
> N'update MyTable set Field1=@Field1 where Col_ID=@Col_ID',
> N'@Field1 varchar(50),@Col_ID int', @Field1 = 'SALEPR', @Col_ID = 241
>
> Since I was manually creating my update command I decided to try the
> same thing using the SqlCommandBuilder. Profiler reflected a far more
> complicated update command generated by CommandBuilder but the net
> result was the same; The sql statement was using the previously saved
> value, not my newly entered value.
>
> I should also point out that the RowState for the row was set to
> modified and I had not run the AcceptChanges method. I'm pretty sure
> that Profiler would not have displayed my update if either of these two
> conditions were different.
>
> I decided to check one more thing.  I made sure that the last row in
> the datatable was modified to set Field1 to '' then ran the
> sqlDA.Update() and set a breakpoint on the line following the update
> then checked the value of the Field1 parameter and it was set to to
> 'SALEPR'. I also checked the value in the table row and it was still
> set to a blank string.
>
> One final thing to try... I added one more line of code immediately
> following sqlDA.Update() to update the last row only and it saved the
> Field1 value correctly as follows:
>
> sqlDA.UpdateCommand.Parameters["@Field1"].Value = "";
> // no need to set Col_ID because it is already set to the Col_ID of the
> last row
> sqlDA.UpdateCommand.ExecuteNonQuery();
>
>
> This concludes that my logic is sound, but why is the Update method
> screwing with my data? I'm starting to think there is a bug in the
> SqlDataAdapter in .NET Framework 1.1.
>
>
> mike
>