coding for login in sqlserver stored procedure [Resolved]

Posted by reddysankark-13471 under ASP.NET on 9/19/2012 | Points: 10 | Views : 3961 | Status : [Member] | Replies : 7
hi,

i need stored procedure coding for below given code can any body given for this

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);
}
}

sankarreddy


Responses

Posted by: Kotra.Ramakrishna on: 9/20/2012 [Member] Starter | Points: 50

Up
0
Down

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

Posted by: Kotra.Ramakrishna on: 9/19/2012 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: reddysankark-13471 on: 9/19/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: reddysankark-13471 on: 9/20/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Kotra.Ramakrishna on: 9/20/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: reddysankark-13471 on: 9/20/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: reddysankark-13471 on: 9/20/2012 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response