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.

Hi,

When you register a SqlCommandBuilder on a SqlDataAdapter, the
SqlCommandBuilder registers itself as a listener for RowUpdating events
that are generated by the SqlDataAdapter specified. So, it will change your
command text again after you change it in the debugger.

In this case, since you have special requirements on a field when updating,
I strongly recommend you not use SqlCommandBuilder, but write the update
command manually.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

OK, thanks!

Show quoteHide quote

You're welcome.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

multithreaded grid-binding problems
How to Insert a whole dataset into database
Connection string information
Memory usage increases with ADO.NET v2.0 RTM
DataSet to Access Database