M
mszanto
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
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