Stored Procedure for Login and Show Error Message on Page if User Not found or Invalid Login

Raj.Trivedi
Posted by Raj.Trivedi under ASP.NET category on | Points: 40 | Views : 2236
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
}

Comments or Responses

Login to post response