Passing ID (int) into another column on Signup table

Question

User-1994446809 posted

Hello,

Following series of advice and responses to one of my threads, I have decided to make changes to my initial database design. I have decided to go with having ID (int) as primary key on my tables. Here is the structure of the database:

ID(int) primary Key	Email	Pass	Con_pass	UserRole	CreatedBy	CreateDate
1	[e-mail]	pass	pass	A	NULL	6/27/2020
2	[e-mail]	admin	admin	A	NULL	6/30/2020
3	[e-mail]	user	user	U	1	7/1/2020

Key: A – Admin, U - User

This table will have Admin details and Other Users details. These Admin and Users details will be inserted from different web forms, and NOT on the same web form. On the Admin sign up it will be indicated that the person signing up is an admin.

My major obstacle though, is how to get the Admin ID, which is in Integer and pass it into the column “CreatedBy” when creating new user (as shown in the table, highlighted in yellow).

This is C# code to create new users, showing where Admin ID is needed to be passed into in the yellow highlighted area.

using System;
using System.Collections.Generic;
using System.Data;
using System.Web.Security;
using System.Xml.Linq;
using System.Configuration;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Drawing;

public partial class UserProfile : System.Web.UI.Page
{
    SqlCommand cmd = new SqlCommand();
    SqlCommand cmd2 = new SqlCommand();
    SqlDataAdapter sda = new SqlDataAdapter();
    DataSet ds = new DataSet();
    SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");

    protected void Page_Load(object sender, EventArgs e)
    {
       
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand();
        con.Open();
        cmd.CommandText = "Select * from [Signup] where Email=@email";
        cmd.Parameters.AddWithValue("@email", mailtxtbx.Text);
        cmd.Connection = con;
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            Label4.Visible = true;
            Label4.Text = "Admin already created this Email";
            Label4.ForeColor = System.Drawing.Color.Red;
            mailtxtbx.Text = "";
            Label1.Visible = false;
            Label2.Visible = false;
            Label3.Visible = false;
        }
        else
        {
            con.Close();

            if (mailtxtbx.Text != "" & pass.Text != "" & conpass.Text != "")
            {
                if (pass.Text == conpass.Text)
                {
                    if (check1.Checked)
                    {
                        SqlCommand cmd2 = new SqlCommand("insert into Signup values('" + mailtxtbx.Text + "','" + pass.Text + "','" + conpass.Text + "','U', '" Admin ID "', GETDATE())", con);
                        con.Open();
                        cmd2.ExecuteNonQuery();
                        con.Close();
                        Label5.Visible = true;
                        Label5.Text = "Successfully Signed Up";
                        Label5.ForeColor = System.Drawing.Color.Green;
                        Label1.Visible = false;
                        Label2.Visible = false;
                        Label3.Visible = false;
                        Label4.Visible = false;
                        mailtxtbx.Text = "";
                        pass.Text = "";
                        conpass.Text = "";
                        Response.Redirect("UserProfile.aspx");
                        userlabel.Visible = true;
                        userlabel.ForeColor = System.Drawing.Color.Blue;
                        userlabel.Text = "User Created";
                    }

                    else
                    {
                        Label1.Visible = true;
                        Label1.Text = "please check box";
                        Label2.Visible = false;
                        Label1.ForeColor = System.Drawing.Color.Red;
                        Label3.Visible = false;
                        Label4.Visible = false;
                        Label5.Visible = false;
                    }
                }

                else
                {
                    Label2.Visible = true;
                    Label2.Text = "Passwords don't match";
                    Label2.ForeColor = System.Drawing.Color.Red;
                    conpass.Text = "";
                    Label1.Visible = false;
                    Label3.Visible = false;
                    Label4.Visible = false;
                    Label5.Visible = false;
                }
            }
            else
            {
                Label3.Visible = true;
                Label3.ForeColor = System.Drawing.Color.Red;
                Label3.Text = "*All Fields Are Required*";
                Label1.Visible = false;
                Label2.Visible = false;
                Label4.Visible = false;
                Label5.Visible = false;
            }
        }
    }

Can you please guide me through in making this work? Thank you.

Answer 1

User348806598 posted

Hi,

When admin logs in to the system to create a user, you must be storing logged in user id(in this case admin id) into the session. You can get the id from the session and add it while creating a user. 

Answer 2

User-1994446809 posted

Okay yes, but the issue I am facing is that I want to display email address as my session. Is session associated with ID or is it used to display user information on a web form?

I am asking this because, I was already using email address as my primary, and everything on the web form was linked to user email address; the user email was being displayed when user (Admin) logs in, then I was advised not to use email as primary key on my database tables, which is why i opted for int ID as primary key. But then again, is there any way I could use ID (int) as primary key and then display email address of Admin upon log in? I really am a bit confused here, can you please guide me through? Thank you

Answer 3

User348806598 posted

Do you have session implementation in your project? If you have, then you may be storing some information related to the user in the session. If you only have an email id in the session, you can add user id also in session or you can get user id using session email.

Answer 4

User475983607 posted

georgeakpan233

My major obstacle though, is how to get the Admin ID, which is in Integer and pass it into the column “CreatedBy” when creating new user (as shown in the table, highlighted in yellow).

Can you explain how your login works?  How do you identity the current user in each request?  Are you using Session?  

As mentioned many times in your similar threads, the standard solution is caching the user's identity in a authentication cookie when the user logs in.  The authentication cookie framework makes the user's identity available in every request. 

User.Identity.Name

You can use the username to get the Id.  If you go a step further and add ASP.NET Identity then you get an entire API and data store to manage user accounts which includes fetching the user Id.

Answer 5

User-939850651 posted

Hi georgeakpan233,

Session.Add(string name, object value);

This will mean that you can put any type of data in the session.

Do you define user classes? If you have, you can store all the information of the logged-in user in the session and get it when you need it.

Just like this:

public class User
    {
        public int ID { get; set; }
        public string Email { get; set; }
        public string Pass { get; set; }
        public string Con_pass { get; set; }
        public string UserRole { get; set; }
        public int CreatedBy { get; set; }
        public DateTime CreateDate { get; set; }
    }

In login button event:

DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(...);
String sql = "select * from Signup where Email= @Email";
SqlDataAdapter sda = new SqlDataAdapter(sql,con);
sda.Fill(dt);
if (dt.Rows.Count > 0)
   { 
      User user = new User()
      {
       ID = int.Parse(dt.Rows[0]["ID"]),
       Email = dt.Rows[0]["Email"],
       ......
      };
  Session.Add("User",user);
    }

User it:

Page display:  Lable.Text = (User)Session["User"].Email;   

In Sql statement：insert into Signup values('" + mailtxtbx.Text + "','" + pass.Text + "','" + conpass.Text + "','U', '" + (User)Session["User"].ID + "', GETDATE())

Best regards,

Xudong Peng