Sql server 2008r2 And Asp.net 4.0
Hi All,
my procedure is throwing some exception i want catch it code behind when i am using ExecuteNonQuery i am able to catch it into code behind
but when i am using ExecuteReader or ExecuteScalar no Exception is coming
here is my procedure
create Proc spSelect
@OfficeId int
as
begin
DECLARE @ErrorNumber INT
,@ErrorSeverity INT
,@ErrorState INT
,@ErrorLine INT
,@ErrorProcedure VARCHAR(128)
,@ErrorMsg VARCHAR(2000)
begin try
select 1/0
select * from Emp where officeid=@OfficeId
end try
begin catch
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorMsg = ERROR_MESSAGE();
end catch
RAISERROR (@ErrorMsg, @ErrorSeverity, @ErrorState);
end
and code behind code is
Dim db As Database
Dim dbCommandScalar As DbCommand = Nothing
Dim result As String = ""
Try
db = DatabaseFactory.CreateDatabase
dbCommandScalar = db.GetStoredProcCommand("spSecurityUserNameValidate")
db.AddInParameter(dbCommandScalar, "@OfficeId", DbType.Int32, officeId)
result = db.ExecuteScalar(dbCommandScalar)
dbCommandScalar.Dispose()
Catch sqx As SqlException // control is not hitting catch block if i use ExecuteNonQuery it will hit this catch block
Dim msg as string = sqx.Message
Catch ex As Exception
Dim msg as string = ex.Message
End Try
Return result