Trap TSQL Errors in .NET form

[PhillD]

Hi,

I am triggering a stored procedure from code inside a form and I am wanting to return any TSQL errors back to the form.

I have written code to do it but when I force an error in TSQL for testing, my vb.net code drops to debug mode with "SQL Exception was unhandled" error.

Here is the code I am using

TSQL

ALTER PROCEDURE dbo.GetKeySp
	(
	@KeyID int
	)
AS 

DECLARE @KeyValue int

--Key 1 - Expense Group ID
--Key 2 - Expense User ID
--Key 3 - Expense Type ID

	Select @KeyValue = KeyValue
	FROM Keys
	WHERE KeyID = @KeyID
	
		UPDATE Keys
	SET KeyValue = KeyValue + 1
	WHERE KeyID = @KeyID

	SET NOCOUNT ON

             --Force Error For Testing
	RAISERROR ('Error raised in TRY block.', -- Message text.
    16, -- Severity.
    1 -- State.
    )
	
	IF @@Error <> 0
		RETURN @@Error
	ELSE
		RETURN @KeyValue

Here is the code that calls the Stored procedure.

Private Function GetExpenseTypeKey() As Integer

        'This will be used to generate the Expense Type Key.

        Dim SQLComm As SqlCommand
        SQLComm = New SqlCommand("GetKeySp", PhantomConn)
        SQLComm.CommandType = CommandType.StoredProcedure

        '3 is the ID for the ExpenseType Key.
        SQLComm.Parameters.Add(New SqlParameter("@KeyID", 3))
        SQLComm.Parameters.Add("@Return", SqlDbType.Int)

        SQLComm.Parameters("@Return").Direction = ParameterDirection.ReturnValue

        PhantomConn.Open()
        SQLComm.ExecuteNonQuery()
        PhantomConn.Close()

        If IsNumeric(SQLComm.Parameters("@Return").Value) Then
            'we have the key
            Return SQLComm.Parameters("@Return").Value
        Else
            'TSQL Error
            'Show Error
            MsgBox(SQLComm.Parameters("@Return").Value, MsgBoxStyle.Critical)
            Return 0

        End If
    End Function

All I want is to return the SQL error to a message box in the VB form.

Thanks

 

[jmcilhinney]

No, you haven't written code to do it. There's no exception handling in your VB code at all, which is why you're being told that an exception was unhandled. If you want to handle an exception in VB then you use a Try...Catch block: you try to do perform a task and you catch any exceptions that are thrown. In your case it will be a SqlException, which you can then interrogate to find out exactly what happened.

exception handling vb.net - Google Search

 

[PhillD]

I thought doing this...

IF @@Error <> 0
	RETURN @@Error
ELSE
	RETURN @KeyValue

in my SQL code would return the Error to VB as if the stored procedure ran normally.

Therefore, I didn't think VB would require a try catch. Unless the RAISE ERROR is completley stopping the stored proecdure from completeing its processing.

I could swear I had used this method before without any issue.

Can you offer any input on the SQL side?

 

[cjard]

RAISERROR (Transact-SQL)

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

-> Implies TRY/CATCH block is needed in TSQL in order to handle

RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
)

What TRY block? Neither your TSQL nor VB contains a Try/Catch exception handler block

-> Unhandled exception

Unless the RAISE ERROR is completley stopping the stored proecdure from completeing its processing

Most languages use that model. Upon RAISE of an error/exception, the executing line of code immediately jumps to the first block of code that can handle it*, or kills the app if there is no such block


*"finally" blocks notwithstanding

 

[PhillD]

I copied the RAISERROR code from another website. I was just trying to test my VB code against a TSQL error. Evidently, I do not need to write error handling code in my stored procedure to return the TSQL error to VB. I just need to write a try catch block in VB and it takes care of everything.

Is that correct?

 

[jmcilhinney]

Yes, that is correct. Any particular module generally only handles error if it is able to do something useful. You would only add error handling to your SQL code if you wanted to do something useful in your SQL code. If the stored procedure logically fails if an error occurs then it makes sense to simply let it fail and have the caller handle the cleanup.