Catching a Return from SQL Stored Procedure

Hi All

Here is my SP

ALTER PROCEDUREdbo.InsertPagerDays

@.ReportEndDatedatetime,

@.PagerDaysint,

@.UserIDvarchar(25)

AS

IF EXISTS

(

-- you cannot add a pager days more than once per report date

SELECTReportEndDate, UserIdfromReportPagerDayswhereReportEndDate = @.ReportEndDateandUserId = @.UserID

)

Return1else

SET NOCOUNT OFF;

INSERT INTO[ReportPagerDays] ([ReportEndDate], [PagerDays], [UserID])VALUES(@.ReportEndDate, @.PagerDays, @.UserID)

RETURN

My Question is, this SP will not let you enter in a value more than once (which is what i want) but how do I write my code to inform the user? Here is my VB code becuase the SP does not error out (becuase it works it acts as if the record updates)

How can I catch the Return 1

'set parameters for SP

Dim cmdcommand =New SqlCommand("InsertPagerDays", conn)

cmdcommand.commandtype = CommandType.StoredProcedure

cmdcommand.parameters.add("@.ReportEndDate", rpEndDate)

cmdcommand.parameters.add("@.PagerDays", PagerDays)

cmdcommand.parameters.add("@.UserId", strUserName)

Try

'open connection here

conn.Open()

'Execute stored proc

cmdcommand.ExecuteNonQuery()

Catch exAs Exception

errstr =""

'An exception occured during processing.

'Print message to log file.

errstr ="Exception: " & ex.Message

lblstatus.ForeColor = Drawing.Color.Red

lblstatus.Text ="Exception: " & ex.Message

'MsgBox(errstr, MsgBoxStyle.Information, "Set User Report Dates")

Finally

If errstr =""Then

lblstatus.ForeColor = Drawing.Color.White

lblstatus.Text ="Pager Days Successfully Added!"

EndIf

'close the connection immediately

conn.Close()

EndTry

You need to add a parameter with ParameterDirection.ReturnValue - seehere for more info.