Quirky response to Update command using datagrid

[Earl]

Using VS2003 and SQL2k.

I've noticed that if I have an expression column (or relation column) added
to the main table, I cannot do an Update without setting the
UpdatedRowSource to either OutputParameters or None. This works fine ... so
long as we do not execute two Updates in the same row, one after the other.
This causes a concurrency error.

I understand the general idea behind concurrency and the raft of issues that
can cause a "concurrency error" (did I mention that ADO.Net is SUPERB at
spitting out "concurrency errors" if you even look at the IDE wrong, but I
digress). In any event, my stored procedure doing the update returns a fresh
set of data after said update.

Okay, playing around with this a bit, I find that using the UpdatedRowSource
set to FirstReturnedRecord prevents concurrency errors when editing twice in
the same row, but of course, we would now have a concurrency error with the
expression column. So what gives? Is there a reasonable solution?

 

[Earl]

Ahhh, well, I've never read this in anyone's book, but I'm guessing someone
out there knows that you must remove the expression column right before
Update, then add the column back in. Apparently, a more elegant solution
does not exist.

 

[Marina]

This shouldn't be a problem if you don't have any code that is trying to
update the expression column in the database because it think it is a real
database column. I have no idea how you are creating your update statement
since you did not provide that information.

In any case, as long as your code doesn't try to put the expression value in
the database, you shouldn't have a problem. This is, if I am understand
what you are doing correctly. You gave a pretty vague description that was
kind of hard to follow and no relevant source code.

 

[Earl]

Fairly straightforward update for the datatable calling a command function
with all the parameters for the sproc. However the datatable has an
expression column (actually a related column) attached. The data relation
isn't the problem, the attached column is the issue. Detaching then
reattaching the column after update appears to be the solution.

You say that "as long as your code doesn't try to put the expression value
in the database". That's really the issue. It seems clear that with the
expression column attached when submitting the update, then it would always
be trying to update the expression column.

Until I run across something showing me a better way, I'll use the
detach/reattach technique. Thanks Marina.