G
Guest
My data access layer fills a table in a DataSet using an SqlDataAdapter. Then
it makes changes in some of the fields. Then it creates an SqlCommandBuilder
to get the UpdateCommand and calls SqlDataAdapter.Update() to update the
changes to the SQL database.
The SelectCommand is simply "select * from TableXXX".
There is one field in the table, FieldYYY, that I need to handle in a
special way. I need to read it in to the DataSet, but I want to not update
it back to the SQL database. Another process may change the value in the
database during the lifetime of the disconnected dataset, and if I were to
update back I would get a concurrency violation.
I thought I could do this by modifying the UpdateCommand to eliminate the
part that refers to the field. I got the command text that the
SqlCommandBuilder built and modified it to leave out the parts like
FieldYYY = @pnn ,
and
((@pnn = 1 AND FieldYYY IS NULL) OR (FieldYYY = @pnn)) AND
Where nn is any number.
Then I set the CommandText of the UpdateCommand to the modified text. I can
see all this working in the debugger.
However this doesn’t work and I could use help in understanding why, or how
to accomplish what I am trying to do.
When I look in the profiler, I see something like the following, which of
course returns zero rows and causes a concurrency exception:
exec sp_executesql
N'
UPDATE TableXXX
SET FieldYYY = @p1
WHERE
(
(fldREC = @p2) AND
…
((@p25 = 1 AND FieldYYY IS NULL) OR (FieldYYY = @p26)) AND
…
)
',
N'
@p1 int,@p2 int, … ',
@p1 = 3894,
@p2 = 1600,
…
@p26 = 3892,
It’s as if I hadn’t changed anything.
it makes changes in some of the fields. Then it creates an SqlCommandBuilder
to get the UpdateCommand and calls SqlDataAdapter.Update() to update the
changes to the SQL database.
The SelectCommand is simply "select * from TableXXX".
There is one field in the table, FieldYYY, that I need to handle in a
special way. I need to read it in to the DataSet, but I want to not update
it back to the SQL database. Another process may change the value in the
database during the lifetime of the disconnected dataset, and if I were to
update back I would get a concurrency violation.
I thought I could do this by modifying the UpdateCommand to eliminate the
part that refers to the field. I got the command text that the
SqlCommandBuilder built and modified it to leave out the parts like
FieldYYY = @pnn ,
and
((@pnn = 1 AND FieldYYY IS NULL) OR (FieldYYY = @pnn)) AND
Where nn is any number.
Then I set the CommandText of the UpdateCommand to the modified text. I can
see all this working in the debugger.
However this doesn’t work and I could use help in understanding why, or how
to accomplish what I am trying to do.
When I look in the profiler, I see something like the following, which of
course returns zero rows and causes a concurrency exception:
exec sp_executesql
N'
UPDATE TableXXX
SET FieldYYY = @p1
WHERE
(
(fldREC = @p2) AND
…
((@p25 = 1 AND FieldYYY IS NULL) OR (FieldYYY = @p26)) AND
…
)
',
N'
@p1 int,@p2 int, … ',
@p1 = 3894,
@p2 = 1600,
…
@p26 = 3892,
It’s as if I hadn’t changed anything.