error handling from SQL server problem

[bbosley]

I am developing with VB 6 SP4, MDAC 2.5, SQL server 7.

I am currently having difficulty with raising errors back to VB from a SQL 7 sproc. Within the sproc I call &quot;sub&quot; sprocs to lookup different values (factors) and return 0 if success and 1 if the factor is not found. The main sproc works fine with the sub sproc. The problem lies when a 1 is returned from the sub sproc (no record found) I check the return value and IF @intRetVal <> 0 I raise an error with the following code:

EXEC @intRetVal = sproc @intVar1, @intVar2
IF @intRetVal <> 0
BEGIN
SET @chvErrorMessage = 'Error in sproc'
RAISERROR (@chvErrorMessage,11,1)
GOTO ErrorHandler
END

ErrorHandler:
RETURN (@intRetVal)

The following code is how I call the sproc from VB:

Set cmd3 = New ADODB.Command

With cmd3

.ActiveConnection = conRating
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter(&quot;QuoteID&quot;, adInteger, adParamInput, 4)
.Parameters.Append .CreateParameter(&quot;PlanID&quot;, adInteger, adParamInput, 4)

.Parameters.Item(0).Value = frmRate.txtQuote
.Parameters.Item(1).Value = frmRate.txtPlan

.CommandTimeout = 0 'Override default of 60 seconds

.Parameters.Append .CreateParameter(&quot;RequestUserID&quot;, adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter(&quot;RequestDateTime&quot;, adDate, adParamInput, 8)

.Parameters.Item(2).Value = fMDIMain.pstrLogin
.Parameters.Item(3).Value = dteRatingStart

.CommandText = &quot;dbo.spr_BasicLifeRating&quot;
.Execute , , adAsyncExecute

End With

GoTo Cleanup
errHandler:

'error routine

Cleanup:

End Sub
--------------------------

The conRating connection object is declared WITHEVENTS and that seems to be working fine.

I have tried the RAISERROR with severity 11, 16, 18 and state 127 but nothing will trigger the VB error handler. When I return from a sproc that has an error I have even tried checking the COUNT of the errors collection and it is 0. I have tried returning the value from the .EXECUTE to a recordset and checking that rs.fields(0).value which returns one of the SELECT '*** information for debugging ***' values instead of the 0 or 1 that is in the RETURN(0) at the end of the sproc.

Any ideas would be greatly appreciated.