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

 


Posted on Mar 28, 2016 05:19 AM

i tried your example on my storedprocedure login to fetch the account from user table and fetch the ad name from company account

 

ALTER PROCEDURE [dbo].[Validate_User5]
      @UserName NVARCHAR(200),
      @Password NVARCHAR(200)
       
       
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @UserId INT, @LastLoginDate DATETIME
       DECLARE @User3 AS TABLE(Id INT,UserName VARCHAR(20),LastLogin DATETIME,Password VARCHAR(20))
	   DECLARE @CompanyInfo AS TABLE(Id INT,UserName VARCHAR(20),Name VARCHAR(20))
	   SELECT a.Id,a.UserName,a.Password,a.LastLogin,d.Name
FROM @User3 a
JOIN @CompanyInfo d ON d.UserName = a.UserName
WHERE a.UserName = @UserName

   
      IF @UserId IS NOT NULL
      BEGIN
            SELECT 1 -- User available.
      END
      ELSE
      BEGIN
            SELECT -1 -- User not available.
      END
END

Now i have the login code which uses session

 

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

now on page i have the code that help me to navigae to the very ad page of the user by clicking the company name and its not working only username is working

 

 public string getUserHREF(object sURL)
    {
        DataRowView dRView = (DataRowView)sURL;
        string username = dRView["Name"].ToString();
        return ResolveUrl("~/ADPage.aspx?Id=" + username);
    }

 


Posted on Mar 28, 2016 05:45 AM

First you are asking Account with Department Table now you are changing your requirement. I would suggest you create new procedure for getting the UserName and the DepartmentName using my given query, after user successfully loged in then using the procedure fetch the record from database and assign the value to session variable like below.

Session["UserName"] = dr["UserName"].ToString();
Session["Department"] = dr["Department"].ToString();

 


Posted on Mar 28, 2016 05:54 AM Modified on on Mar 28, 2016 07:49 AM

Thanks the code worked.