Evaluate returnvalues of stored procedures used in TableAdapterManager.UpdateAll
-
Hello forum, I'm using Visual Studio 2013 and MSSQL 2012. In my VB.net-application, I'm using a typed dataset to read data from MSSQL and write back additions, changes and deletions. I built Stored Procedures for the 4 commands (Select, Update, Delete and Insert), and assigned them to the 4 commands in the tableadapter. All works fine; all 4 command do their job in my app. BUT: under some (well defined) circumstances the SPs return a returnvalue of 1. This returnvalue signals overlapping of the pending data and the old data in the table. Thus, the SPs do enforce that the time intervals in the table do not overlap. It works, but it works silently: "illegal" inputs are rejected, but without notice. So, I'd like to get access to this returnvalue, in order to show a message like "Your pending data overlap existing intervals and can therefore not be saved". How can i access the returncode of SPs used in TableAdaptermanager?
-
Hello forum, I'm using Visual Studio 2013 and MSSQL 2012. In my VB.net-application, I'm using a typed dataset to read data from MSSQL and write back additions, changes and deletions. I built Stored Procedures for the 4 commands (Select, Update, Delete and Insert), and assigned them to the 4 commands in the tableadapter. All works fine; all 4 command do their job in my app. BUT: under some (well defined) circumstances the SPs return a returnvalue of 1. This returnvalue signals overlapping of the pending data and the old data in the table. Thus, the SPs do enforce that the time intervals in the table do not overlap. It works, but it works silently: "illegal" inputs are rejected, but without notice. So, I'd like to get access to this returnvalue, in order to show a message like "Your pending data overlap existing intervals and can therefore not be saved". How can i access the returncode of SPs used in TableAdaptermanager?
Behold, the power of a cup of coffee :rolleyes: Step 1: Raiserror in the SP with a severity of 11 or higher. 10 and lower ist considered a warning and won't throw an exception in vb.net.
IF @intConflictingRows = 0
BEGIN
INSERT INTO [dbo].[tbl] (Fields) VALUES (@Values);SELECT Fields FROM tbl WHERE (ID = SCOPE\_IDENTITY())END
ELSE
BEGIN
RAISERROR('Error: New interval overlapping existing data!', 11, 1)
--do not return a return code here!
ENDStep 2: Wrap the TableAdapterManager.UpdateAll in a Try-Catch-block, and catch all exception with a Messagebox.
Try
If Me.Validate() Then
MyBindingsource.EndEdit()
TableAdapterManager.UpdateAll(myDataset)
Return True
End If
Return False
Catch ex As Exception
MessageBox.Show(ex.Message, "Error while saving!")
Return False
End Try