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

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

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

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

In front end use this code

on button click
string strconn = "Your Connection string";
SqlConnection = new SqlConnection(strconn);;
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);
if(ds.tables[0].rows[0]["ErrCode"].to String == "999")
Label1.text = "Login Successfull"; // Messgage on successfull login
Label1.text = "User Not Found"; // Error Message

Comments or Responses

Login to post response