Bob,
I think that if you look at my post you will see that I indicated the
problem becomes evident when you are attempting to use all of the
columns in a row to insure concurrency i.e. if any of the columns in a
row have been changed since the record was pulled from the server, the
update fails. This approach requires a WHERE clause that incorporates
all of the columns.
Here is the problem when using parameters with nulls.
Let's say you are creating an UPDATE statement for a DataAdapter using
parameters. The UPDATE statement is built the same way everytime.
The construction of the UPDATE statement is not dependant on the
actual data being passed to the server. A simple example follows:
// build update statement for dataadapter
string sqlStmt = "Update mytablename Set";
string sqlWhere = " Where ";
foreach (string colName in tableColumns) // where tableColumns is
an array of columns
{
sqlStmt +=" "+ colName + "= @"+colName+",";
sqlWhere += colName + "= @old"+colName+" AND ";
}
// strip off the last comma
sqlStmt = sqlStmt.Substring(0,sqlStmt.Length-1);
// strip off last AND
sqlWhere = sqlWhere.Substring(0,sqlWhere.Length-4);
// build the where clause
sqlStmt += sqlWhere;
sqlCommand = new SqlCommand(sqlStmt,this.sqlConnect);
// add the parameters
foreach (string colName in tableColumns)
{
sqlParam = new SqlParameter();
sqlParam.ParameterName = "@"+colName;
sqlParam.SourceColumn = colName;
sqlCommand.Parameters.Add(sqlParam);
sqlParam = new SqlParameter();
sqlParam.ParameterName = "@old"+colName;
sqlParam.SourceColumn = colName;
sqlParam.SourceVersion = DataRowVersion.Original;
sqlCommand.Parameters.Add(sqlParam);
}
this.daClassAdapter.UpdateCommand = oCommand;
Assuming a simple update with three columns, the resulting command
sent to the SQL server would look something like this:
exec sp_executesql N'Update mytable Set ColOne= @ColOne, ColTwo=
@ColTwo, ColThres= @ColThree Where ColOne= @oldColOne AND ColTwo=
@oldColTwo AND ColThree= @oldColThree ', N'@ColOne
nvarchar(4000),@oldColOne nvarchar(4000),@ColTwo
nvarchar(4000),@oldColTwo nvarchar(4000),@ColThree
nvarchar(4000),@oldColThree nvarchar(4000), @ColOne = N'New Value',
@oldColOne = N'Old Value', @ColTwo = N'New Value', @oldColTwo = N'Old
Value', @colThree = NULL, @oldcolThree = NULL
In the SQL statement shown above, I am assuming that the user has
changed the values of colOne and colTwo. The value of colThree was
initially NULL and has not changed - still is null. With SET
ANSI_NULLS set to ON, this UPDATE statement will not update the row
because the WHERE clause will always return a FALSE value. This is
because the NULL = NULL comparision on colThree returns a false.
If you construct your WHERE clause only on the primary key as Jon
suggests you must either figure out another way to test to see if the
underlying data has changed since the result set was returned, or you
must live with the fact that you may be overwriting recent changes
with out giving the user the option of canceling the update. This may
be an acceptable solution in some circumstances.
I agree that SET ANSI_NULLS off is a workaround and if someone know a
better way to get around the problem describe above, I would be happy
to adopt it.
Best regards,
Tim
