Ravi Patel

Ravi Patel

  • 250
  • 6.8k
  • 1.4m

Sql server 2008r2 And Asp.net 4.0

Nov 18 2014 8:18 AM
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