Display data updated in a trigger after SqlDataAdapter.Update

[Guest]

I have a DataGrid bound to a DataTable. I issue a SqlDataAdapter.Update
command to apply the user’s changes to the database. Everything works just
fine. However, in the database (SQL Server) I have a trigger which updates
one of the columns. I want the updated column to display the new value after
the SqlDataAdapter.Update finishes.

I can see in the SqlDataAdapter.UpdateCommand.CommandText that there is a
Select statement after the Update statement. What is this for? I had hoped
that its purpose was to re-retrieve the updated row so that my datagrid would
display any columns changed by the database. Apparently I am wrong. Can you
tell me how to accomplish this? Do I have to re-fill the dataset myself?

 

[Guest]

if I understand you correctly ... You could pass the modified value back via
an Output parameter from the stored procedure to the ADO.NET code. Is this
what you are looking for? If so, i can show you some code for this.

// John Papa
// http://codebetter.com/blogs/john.papa

 

[Guest]

no. The SqlCommandText is an Update statement. The data is modified in a
trigger, not a stored procedure. I don't belive that triggers return
parameters.

 

[Guest]

I found my problem. In order to wrap all the updates in a SQL Transaction
and to do Inserts "top-to-bottom" and Deletes "bottom-to-top", I am actually
doing the updates to copies of the tables using GetChanges. The updates are
in the copies, but I need to get them back into the original dataset.

I sure do wish the DataSet would give me an easy way to do this without
writing a lot of code. My DataSet will ultimately have ~20 tables. When I
update a want rollback/commit on all or nothing. That seems so basic. So
does handling Inserts and Deletes in the correct order. I can't just issue
20 SqlDataAdapter.Update statements.

 

[Kevin Yu [MSFT]]

Hi Aurin,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the updated data back
to the DataSet when an Update is issued. If there is any misunderstanding,
please feel free to let me know.

In this case, since you have used GetChanges to get a subset of the
DataSet, you can try to use DataSet.Merge to merge the changed rows back to
the original DataSet. For more information on DataSet.Merge, please check
the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatasetclassmergetopic.asp

HTH.

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