Question on SqlDataAdapter Update

[Steven]

Hi,

I have a typed dataset with a SqlDataAdapter which enables
user to Insert, Update and Delete via Stored Procedure
Command to SQL Server. When user click on Update button,
I just call:

SqlDataAdapter1.Update

Everything works fine except that I would like to build
some validation logic on SP which will return some value
to application to indicate there's error and I want the
Update to be stopped. For example, say I have 3 rows to
update and the 2nd update SP return a value, then the 3rd
update should not get execate.

I just can't find a way to intercept
SqlDataAdapter1.Update to stop at 2nd update.
Is there anyway I can tackle the return value from SP via
SqlDataAdapter1.Update command?

Please help, thanks.

 

[William Ryan eMVP]

Steven:

I'm not sure if you need to use Output params or return values, but you can
trap the RowUpdating event which fires each time a row updates. Once it's
updated, you can interrogate its Errors property to check for errors. You
have the option to continueUpdateOnError which is set to false by default.
Anyway, if you write an event handler for this, then check the errors are
each pass, and if you get the errors you are looking for, then don't
continue. You could use a bool, write your own "CompletedSuccessfully
event" which triggers update2 then update3.. you have a lot of flexibility
and the challenge is determining if you want to continue or not.

Since you are using SQL Server, you can even throw your own custom errors in
your proc and since you are using procs (it's so nice to see a non -dynamic
sql related problem ;-)), you can raise whatever you want based on whatever
condition happens.

You can get error info from the RowUpdatingEventArgs
http://msdn.microsoft.com/library/d...mdatacommonrowupdatingeventargsclasstopic.asp .
Also, there's a Status property which you may find useful. Remember I
mentioned the Dataadapter's ContinueUpdateOnError property? Well, there's
an UpdateStatus enum that has four values... Continue, ErrorsOccrured,
SkipAllRemainingRows and SkipCurrentRow
http://msdn.microsoft.com/library/d...ommonrowupdatingeventargsclassstatustopic.asp.
If continue is false, then it won't update the next row or anything else.
So, you can check this to see what's going to happen (it's Read Only so you
can't take it over).

All of this gives you some pretty finely grained control over your update so
you can use this to make the determination if you want to fire udpate2 and
or 3. You'll need to use RaiseError if the query is going to work but you
are going to decide if it 'failed' by virtue of the variables and then send
notification accordingly.

Hopefully I understood the problem and addressed your question but if not,
please let me konw and I'll give it another try.

HTH,

Bill