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

micah

SOLVED CLOSED ACTIVE ANSWERED
User: micah
Posted: on Mar 28, 2016 02:05 AM
Forum: ASP.Net Basics
Answer: 1
Views: 6411

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

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Mar 28, 2016 02:39 AM
Modified: on Mar 28, 2016 02:40 AM
Report

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

micah

0 2

Reply
User: micah
Replied: on Mar 28, 2016 05:19 AM
Report

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

dharmend...

0 3

Reply
User: dharmendr
Replied: on Mar 28, 2016 05:45 AM
Report

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

micah

0 4

Reply
User: micah
Replied: on Mar 28, 2016 05:54 AM
Modified: on Mar 28, 2016 07:49 AM
Report

Thanks the code worked.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us