coding for login in sqlserver stored procedure [Resolved]

Hi,

Try below code. I modified Code , Plz find that code

ALTER proc [dbo].[spLoginChecking]
(@UserName varchar(50),@Password varchar(50),@Role varchar(50)output)

AS
BEGIN

Declare @EmpId int

if exists (select * from tbl_Login where LoginName=@UserName And Password=@Password)
begin
select @EmpId=EmpId from tbl_Login where LoginName=@UserName And Password=@Password
select @Role=Role from tbl_Login where EmpId=@EmpId
Select @Role AS Role
end
else
begin
Select 'NoUser' AS Role
end
end

exec spLoginChecking 'sankarreddy@gmail.com','ffjhgfg'

If it is useful , Please mark as Answered

Thanks & Regards
Ramakrishna.k

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
Please try below Proc. if you have any clarification let me know.



create proc sp_GetEmpCargoStatus
@UserName varchar(50),
@Password varchar(50),
@Role varchar(50) output

as


Declare @UserId int
--Authenticating User
if exists (select UserId from tblUsers where UserName=@UserName And Paswords=@Password)
begin
select @UserId=UserId from tblUsers where UserName=@UserName And Paswords=@Password
select @Role=Roles from tblUserRoles where UserId=@UserId
end
else
begin
Set @Role =-99
end

Thanks & Regards
Ramakrishna.k

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

hi i will try that code its asking @role parameter

ALTER proc [dbo].[spLoginChecking]
(@UserName varchar(50),@Password varchar(50),@Role varchar(50)output)

AS
BEGIN

Declare @EmpId int

if exists (select * from tbl_Login where LoginName=@UserName And Password=@Password)
begin
select @EmpId=EmpId from tbl_Login where LoginName=@UserName And Password=@Password
select @Role=Role from tbl_Login where EmpId=@EmpId
end
else
begin
Set @Role ='NoUser'
end
end

exec spLoginChecking 'sankarreddy@gmail.com','ffjhgfg'

sankarreddy

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

hi i will try this code it will execute successfully in sql server.But in code section its not authenticate what will be the reason please clarify it i am new in this field

sankarreddy

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Can you plz post what error you r getting..

Thanks & Regards
Ramakrishna.k

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

hi


i will execute the login page it will process .but it is direct to same page postback.it will not execute that adminpage its doesn't shows any error plz help me

this is my coding


login.asp.cs


public partial class Login : System.Web.UI.Page
{
clsLogin objLogin = new clsLogin();
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string str1 = null;
string[] UserName = null;
try
{
if (txtUserName.Text.Contains("@"))
{
string str = txtUserName.Text;
UserName = str.Split('@');
clsLogin.UserName = UserName[0].ToString();
str1 = UserName[0].ToString();
}
else
{
clsLogin.UserName = txtUserName.Text.Trim();
str1 = txtUserName.Text.Trim();
}
clsLogin.Password = txtPassword.Text.Trim();
string Role = objLogin.GetUserLogin();

if (Role == "NoUser")
lblMsg.Text = "User Name and password mismatch. Try again.";
else
if (Role == "Admin")
{
Session["UserName"] = str1;
Response.Redirect("~/Admin/AdminHome.aspx");
}
else if (Role == "Manager")
{
Session["UserName"] = str1;
Response.Redirect("~/HRManager/ManagerHome.aspx");
}
else if (Role == "Employee")
{
Session["UserName"] = str1;
Response.Redirect("~/Employee/EmployeeHome.aspx");

}

}

catch (Exception ex)
{
lblMsg.Text = ex.Message;

}
}
}

clsLogin.cs


public class clsLogin:Connection
{
public clsLogin()
{
//
// TODO: Add constructor logic here
//
}
public static string UserName { get; set; }
public static string Password { get; set; }
public static string Role { get; set; }
DataSet ds = null;

public string GetUserLogin()
{
try
{
SqlParameter[] p = new SqlParameter[3];

p[0] = new SqlParameter("@UserName", UserName);
p[1] = new SqlParameter("@Password", Password );
p[2] = new SqlParameter("@Role", SqlDbType.VarChar, 50);
p[2].Direction = ParameterDirection.Output;


SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "spLoginChecking", p);
Role = Convert.ToString(p[2].Value);
return Role;
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public DataSet GetEmpCargoStatus(int EmpId)
{
try
{
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@EmpId", EmpId);
return SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "sp_GetEmpCargoStatus", p);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}


}

storedprocedure

ALTER proc [dbo].[spLoginChecking]
(@UserName varchar(50),@Password varchar(50),@Role varchar(50)output)

AS
BEGIN

Declare @EmpId int

if exists (select * from tbl_Login where LoginName=@UserName And Password=@Password)
begin
select @EmpId=EmpId from tbl_Login where LoginName=@UserName And Password=@Password
select @Role=Role from tbl_Login where EmpId=@EmpId
Select @Role AS Role
end
else
begin
Select 'NoUser' AS Role
end
end

sankarreddy

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

hi i can clear this coding.But i want one thing in this i will give unknown username in this txtUserName.Text; .it will not executing user not found code and password code.how to clear this plz help me

sankarreddy

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator