I have a stored procedure like this.
---------------------------------
Procedure check_user(p_user_name in varchar2, userid in out integer)
   select userid, active_bln into v_user_id, v_active_bln from table where username = p_user_name ;
userid := v_user_id

if v_active_bln <> "Y" Then
raise_application_error(-20001,"user disabled")
end if


raise_application_error(-20004,"invaild username")
end Procedure -------------------------------------
My .NET ode
--------------------------------------------------
Public Function CheckUser(username as integer)
Try
Conn.Open
cmd = conn.createcommad
cmd.commandtext = check_user
cmd.parameters.add("p_user_name", username)
cmd.parameters.add("p_user_id", oracledbtype.int32, parameterdirection.output)
cmd.excutenonquery()
return cmd.parameters("p_user_id").value
Catch ex as exception
Throw
End Try
-------------------------------------
How can I catch and display an appropriate error "Invalid username" to the user if the user name doesn't exist in the table.Also If the user is disabled the error message should be "User is disabled".

Thanks..

It depends which connection object you are using (OleDb or Sql) but if it's OleDbConnection you can catch the OleDbException and check it's Errors collection. Each item in the Errors collection has a NativeError property this should store you're -20004 and -20001 error codes.

You can check that way.

Kep.

I'm assuming you're using OleDb because you use an oracledbtype.int32 data type, so before you catch your general exceptions (Catch ex as exception) you should catch your OleDb exceptions:

Catch ex as System.Data.OleDb.OleDbException

Then you can test on ex.ErrorCode for the specific error (you have to do the leg work to find out the error number that is returned with a null recordset) and send your own message to the client:

If ex.ErrorCode = -20004 Then throw new ApplicationException("Username not found")

or the Oledb exception:

If ex.ErrorCode = -20004 Then throw new ApplicationException(ex.Message)

Now I generally write in C#, so my VB might be a little rusty, but I know that if you let your general exception handler catch database exceptions, you will lose the error code and therefore be unable to send the correct message to the user.

Hope this helps!

Robin

Thank you for your help.It's working fine.