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!

Catch a trigger from the Grid Events

Hi,

I have one problem when I try to update one record in the data base.

Such error arises when I update a table and a trigger raises an error, I cant catch it within the Row_Updating event command and I dont know how to get it within the Row_Updated event, because the exception happes before that command.

Also, I tried in the sqldatasource updating event but I got no results.

Any idea?

thanks in advance.

Modify your procedure to catch any errors and return a message:http://technet.microsoft.com/en-us/library/ms175976.aspx