How do i select UserName from table account and also select department in Department table and pass

Last Reply on Mar 28, 2016 05:54 AM By micah

Posted on Mar 28, 2016 02:05 AM

How do i select UserName from table account and also select department in Department table and pass both to session on login

Is it possible to select UserName from account table and department from Department table while logging and pass it to session this is my login code where i only passed UserName to session

 

 protected void OnAuthenticate(object sender, AuthenticateEventArgs e)
    {

        string constr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        int UserID;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Validate_User5"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    Session["UserName"] = ctlLogin.UserName;
                    Session["Name"] = ctlLogin.UserName;
                  // cmd.Parameters.AddWithValue("@Email", ctlLogin.UserName);
                    cmd.Parameters.AddWithValue("@UserName", ctlLogin.UserName);
                    cmd.Parameters.AddWithValue("@Password", (ctlLogin.Password));
                   
                  //  cmd.Parameters.AddWithValue("@Phone", ctlLogin.UserName);
                    cmd.Connection = con;
                    con.Open();
                    UserID = Convert.ToInt32(cmd.ExecuteScalar());
                    con.Close();
                }
                switch (UserID)
                {
                    case -1:
                        ctlLogin.FailureText = "Username or password not correct.";
                        break;
                    case -2:
                        ctlLogin.FailureText = "Account has not been activated.";
                        break;
                    case -3:
                        ctlLogin.FailureText = "Your Password.";
                        break;
                    default:
                        Response.Redirect("~/LandingPage.aspx");
                        break;
                }
            }
        }
    }
}

But now i want to pass also department from Department table so that i can use department session to fetch record on a particular page TABLE Account

 

--------------------------

====================================================================================================================
        UserName                       | 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         Steve1                        |              
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TABLE Department table --------------------------

 

====================================================================================================================
        UserName                       |  Department
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         Steve1                        |              Sales
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Posted on Mar 28, 2016 02:39 AM Modified on on Mar 28, 2016 02:40 AM

Hi micah,

Refer the below query. Using the query fetch the UserName and DepartmentName of that particular user from account and department table and assign that to the session variable when click the login button.

DECLARE @Account AS TABLE(Id INT,UserName VARCHAR(20),Password VARCHAR(20))
INSERT INTO @Account VALUES(1,'Steve1','steve@123')

DECLARE @Department AS TABLE(Id INT,UserName VARCHAR(20),Department VARCHAR(20))
INSERT INTO @Department VALUES(1,'Steve1','Sales')

SELECT a.UserName,a.Password,d.Department 
FROM @Account a
JOIN @Department d ON d.UserName = a.UserName
WHERE a.UserName = 'Steve1'

OutPut

UserName    Password    Department

Steve1         steve@123    Sales