Error Handle in Strore Procedure

Posted by Murugavelmsc under ASP.NET on 2/25/2013 | Points: 10 | Views : 868 | Status : [Member] | Replies : 1
Error handle in Store procedure and how to display error msg in page

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Raj.Trivedi on: 2/26/2013 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hello Murugavelmsc

You can do this in the following way.

Over here i have registration table

This is a stored procedure where it accepts 2 parameters UserEmail and Password.

If the user enters the emai and password from front end and if the value matches in the table then he will able to to login

If you see that i have declared 3 more parameters for checking if the match has been successfull

create proc [dbo].[Login]
@UserEmail varchar(25),
@Password varchar(25)
AS
declare @@Rcount int -- to check if the supplied parameters returns any rows
declare @ErrCode varchar(4) --To hold the error
declare @UserInfo varchar(100) -- To hold User Info
BEGIN

set @@Rcount = (select COUNT(Email) from Registration where Email = @UserEmail and Password = @Password); -- we are setting the row count over here
IF(@@Rcount = 0)
Begin
set @ErrCode = '101';--User Not Found -- if the row count is 0 then error code = 101 .this means there is no user
set @UserInfo='';
End
Else
Begin
set @ErrCode = '999'; -- successful login
set @UserInfo = (select Email from Registration where Email = @UserEmail and Password = @Password)
End

select @ErrCode ErrCode,@UserInfo UserInfo -- this will be stored in dataset.
END


In front end use this code

on button click
string strconn = "Your Connection string";
SqlConnection = new SqlConnection(strconn);
con.open();
SqlCommand cmd = new SqlCommand("Login",con)
cmd.CommandType = CommandType.StoredProcedure;
cmd.addparameters.with value("@UserEmail",SqlDbTYpe.varchar).value = txtEMail.text;
cmd.addparameters.with value("@Password",SqlDbTYpe.varchar).value = txtPassword.text;
dataset dslogin = new dataset();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.fill(ds);
if(ds.tables[0].rows[0]["ErrCode"].to String == "999")
{
Label1.text = "Login Successfull"; // Messgage on successfull login
}
else
{
Label1.text = "User Not Found"; // Error Message
}



Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response