Tuesday, February 14, 2012

Catch raiserror from ExecuteReader

Hi. I am executing a stored procedure. The stored procedure raises an error and all I need is to catch this error. Pretty simple, but it only works with an ExecuteNonQuery and not with an Executereader statement. Can anybody explain to me why this happens?

Here's the sp:


CREATE PROCEDURE dbo.rel_test
AS
select 1
raiserror ('My error.', 11, 2)
return
GO

Here's the ASP.Net page:

<% @.Page Language="VB" debug="True" %>
<% @.Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public Function RunSP(ByVal strSP As String) As SqlDataReader
Dim o_conn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
AddHandler o_conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

o_conn.Open

Dim cmd As New SqlCommand(strSP, o_conn)
cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim rdr as SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rdr.Close()
cmd.Dispose()

Response.Write(o_conn.State)

End Function

Private Sub OnInfoMessage(sender as Object, args as SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
Response.Write(String.Format("The {0} has received a severity {1}, state {2} error number {3}\n" & _
"on line {4} of procedure {5} on server {6}:\n{7}", _
err.Source, err.Class, err.State, err.Number, err.LineNumber, _
err.Procedure, err.Server, err.Message))
Next
End Sub

Sub Page_Load(sender as Object, e as EventArgs)
RunSP("rel_test")
End Sub
</script>

I thought InfoMessage captured messages with a severity level of 10 or less? If you change your sproc to raise a severity level 9 error will your code work?

Terri|||I was trying to get the severity level 11 til 18. I did receive the messages when they had severity level 1 til 10. That's what I found so weird about it.

I got it solved though. It seems like when using NextResult it will actually raise the error.

I really need to change my mind from ASP to ASP.Net :-/

Thnx!

No comments:

Post a Comment