How to get multiple column values from DB in session variabl

Question

I want to get UID username city email from registration table (sql server) from DB on successful login of user. I want to store this values in session variables. plz help me with the code and solution below is my code   protected void ValidateUser(object sender EventArgs e) { int userId = 0; string constr = ConfigurationManager.ConnectionStrings[&quot;constr&quot;].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(&quot;Validate_User&quot;)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(&quot;@Username&quot; txtUname.Text.Trim()); cmd.Parameters.AddWithValue(&quot;@Password&quot; txtPassword.Text.Trim()); cmd.Connection = con; con.Open(); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); userId = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); if (dt.Rows.Count &gt; 0) { Session[&quot;uid&quot;] = dt.Columns[0].ToString(); Session[&quot;Username&quot;] = dt.Columns[1].ToString(); Session[&quot;city&quot;] = dt.Columns[2].ToString(); Response.Redirect(&quot;Redirectform.aspx&quot;); Session.RemoveAll(); } else { lblmsg.Text = &quot;You're username and word is incorrect&quot;; lblmsg.ForeColor = System.Drawing.Color.Red; } } switch (userId) { case -1: lblmsg.Text = &quot;Username and/or password is incorrect.&quot;; break; case -2: lblmsg.Text = &quot;Account has not been activated.&quot;; break; default: //FormsAuthentication.RedirectFromLoginPage(Login1.UserName Login1.RememberMeSet); break; } } } This is my stored Procedure..validated user ALTER PROCEDURE [dbo].[Validate_User] @Username NVARCHAR(20) @Password NVARCHAR(20) AS BEGIN SET NOCOUNT ON; DECLARE @UserId INT @LastLoginDate DATETIME SELECT @UserId = UserId @LastLoginDate = LastLoginDate FROM Users WHERE Username = @Username AND [Password] = @Password IF @UserId IS NOT NULL BEGIN IF NOT EXISTS(SELECT UserId FROM UserActivation WHERE UserId = @UserId) BEGIN UPDATE Users SET LastLoginDate = GETDATE() WHERE UserId = @UserId SELECT @UserId [UserId] -- User Valid END ELSE BEGIN SELECT -2 -- User not activated. END END ELSE BEGIN SELECT -1 -- User invalid. END END

Bikesh Srivastava · Answer

Hi safal you are doing good but according your code you are removing all session in last line. Session.RemoveAll(); why??? if you used this line definatly all session will be lost.

safal agrawal · Answer

Hello sir Madhanmohan Devarajan its by mistake protected void ValidateUser(object sender EventArgs e) { int userId = 0; string constr = ConfigurationManager.ConnectionStrings[constr].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(Validate_User)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(@Username txtUname.Text.Trim()); cmd.Parameters.AddWithValue(@Password txtPassword.Text.Trim()); cmd.Connection = con; con.Open(); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); DataTable dt = new DataTable(); sda.Fill(dt); userId = Convert.ToInt32(cmd.ExecuteScalar()); if (dt.Rows.Count > 0) { lblmsg.Text = Login Successful; Session[uid] = dt.Rows[0][UserId].ToString(); //stores user name Session[Username] = dt.Rows[0][Password].ToString(); Session[city] = dt.Rows[0][City].ToString(); Response.Redirect(~/Home.aspx); } else { lblmsg.Text = Login failed; } con.Close(); con.Dispose(); } } }

Madhanmohan Devarajan · Answer

Safal What kind of error you are currently encountering? Because there are some doubts on seeing yours code. DECLARE @UserId INT @LastLoginDate DATETIME // You need UID username city email ? I am not sure what you are selecting from the below query SELECT @UserId = UserId  @LastLoginDate = LastLoginDate FROM Users WHERE Username = @Username AND [Password] = @Password // You are getting 3 column values from dt but in the Stored procedure you are just selecting UID. SELECT @UserId [UserId] Session["uid"] = dt.Columns[0].ToString(); Session["Username"] = dt.Columns[1].ToString(); Session["city"] = dt.Columns[2].ToString(); Response.Redirect("Redirectform.aspx"); Session.RemoveAll(); //Why you are removing all session content after creating session variables?