Inserting and passing UserId into other columns

Question

User-1994446809 posted

I have had this same encounter before in one of my threads; though I have not been working on it due to ill health. So I am just returning back to it after my recovery.

The issue I am facing is about inserting same data in two columns of the same table. The code behind I was being given seem to be giving me a different result.

I have two tables;

UserTable

UserId	Email	Password	confirm	Role	Name	Createdby	Image	Createdate
1	[e-mail]	Pass	Pass	A	Richard	-1	0xFFD8F	7/28/2020
2	[e-mail]	Abcde	Abcde	A	Ivory	-1	0xFEH7M	7/29/2020

 

DenomTable

Id	UserId	Email	Name	denom
1	1	[e-mail]	Richard	0
2	2	[e-mail]	Ivory	0

 

From the two tables above, I have a situation where I insert data and at the same time fetch the data in the UserId column of UserTable and insert it into Createdby column of the same table.

Also the same data is fetched and passed into UserId column of DenomTable.

This has actually worked in the DenomTable; when I inserted data. But in the UserTable, the value that is returned is “-1”. What could be the issue please? I would appreciate if I get help with this. Thank you

HERE IS THE CODE I USED:

 if (mailtxtbx.Text != "" & pass.Text != "" & conpass.Text != "" & txtname.Text !="")
            {
                if (pass.Text == conpass.Text)
                {
                    if (Filedoc.PostedFile.FileName != "")
                    {
                        if (check1.Checked)
                          {
                            int UserId = -1;

                            byte[] image;
                            Stream s = Filedoc.PostedFile.InputStream;
                            BinaryReader br = new BinaryReader(s);
                            image = br.ReadBytes((Int32)s.Length);
                            // define query to be executed
                            string query = @"INSERT INTO UserTable (Email, Password, confirm, Role, Name, CreatedBy, Image, Createdate) VALUES (@Email, @Password, @confirm, @Role, @Name, @CreatedBy, @Image, @Createdate);
                            SELECT SCOPE_IDENTITY();";
                            // set up SqlCommand in a using block   
                            using (SqlCommand objCMD = new SqlCommand(query, con))
                            {
                                // add parameters using regular ".Add()" method 
                                objCMD.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = mailtxtbx.Text.Trim();
                                objCMD.Parameters.Add("@Password", SqlDbType.VarChar, 100).Value = pass.Text.Trim();
                                objCMD.Parameters.Add("@confirm", SqlDbType.VarChar, 50).Value = conpass.Text.Trim();
                                objCMD.Parameters.Add("@Role", SqlDbType.VarChar, 50).Value = 'A';
                                objCMD.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtname.Text.Trim();
                                objCMD.Parameters.Add("@CreatedBy", SqlDbType.Int, 50).Value = UserId;
                                objCMD.Parameters.Add("@Image", SqlDbType.VarBinary).Value = image;
                                objCMD.Parameters.Add("@Createdate", SqlDbType.DateTime, 100).Value = DateTime.Now;
                                // open connection, execute query, close connection
                                con.Open();
                                object returnObj = objCMD.ExecuteScalar();

                                if (returnObj != null)
                                {
                                    int.TryParse(returnObj.ToString(), out UserId);
                                }
                            }
                            con.Close();

                            if (UserId > 0)
                            {
                                query = @"INSERT INTO DenomTable (UserId, Email, Name, denom) VALUES (@UserId, @Email, @Name, @denom)";
                                using (SqlCommand objCMD = new SqlCommand(query, con))
                                {
                                    // add parameters using regular ".Add()" method 
                                    objCMD.Parameters.Add("@UserId", SqlDbType.Int, 50).Value = UserId;
                                    objCMD.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = mailtxtbx.Text.Trim();
                                    objCMD.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtname.Text.Trim();
                                    objCMD.Parameters.Add("@denom", SqlDbType.Float, 100).Value = 0; //Change type here accordingly
                                    con.Open();
                                    object returnObj = objCMD.ExecuteScalar();

                                    if (returnObj != null)
                                    {
                                        int.TryParse(returnObj.ToString(), out UserId);
                                    }
                                    cmd.ExecuteNonQuery();
                                    Response.Redirect("Default.aspx");
                                }
                            }
                            con.Close();
                        }

Answer 1

User475983607 posted

The code functioning as written.  It sets the userId to -1 and inserts then record.

if (check1.Checked)
  {
    int UserId = -1;

    byte[] image;
    Stream s = Filedoc.PostedFile.InputStream;
    BinaryReader br = new BinaryReader(s);
    image = br.ReadBytes((Int32)s.Length);
    // define query to be executed
    string query = @"INSERT INTO UserTable (Email, Password, confirm, Role, Name, CreatedBy, Image, Createdate) VALUES (@Email, @Password, @confirm, @Role, @Name, @CreatedBy, @Image, @Createdate);
    SELECT SCOPE_IDENTITY();";
    // set up SqlCommand in a using block   
    using (SqlCommand objCMD = new SqlCommand(query, con))
    {
        // add parameters using regular ".Add()" method 
        objCMD.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = mailtxtbx.Text.Trim();
        objCMD.Parameters.Add("@Password", SqlDbType.VarChar, 100).Value = pass.Text.Trim();
        objCMD.Parameters.Add("@confirm", SqlDbType.VarChar, 50).Value = conpass.Text.Trim();
        objCMD.Parameters.Add("@Role", SqlDbType.VarChar, 50).Value = 'A';
        objCMD.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtname.Text.Trim();
        objCMD.Parameters.Add("@CreatedBy", SqlDbType.Int, 50).Value = UserId;
        objCMD.Parameters.Add("@Image", SqlDbType.VarBinary).Value = image;
        objCMD.Parameters.Add("@Createdate", SqlDbType.DateTime, 100).Value = DateTime.Now;
        // open connection, execute query, close connection
        con.Open();
        object returnObj = objCMD.ExecuteScalar();

Answer 2

User-1994446809 posted

Mgebhard,
Should I change the line "int UserId = -1;" to "int UserId > 0;" ?
Is there a way you can help me?
Or what correction can be done on this please?

Answer 3

User-775646050 posted

You need to set UserId to whatever the id of the user who is creating the new user. I am assuming the CreatedBy database type is int. You need to get that id somewhere: hidden field, database etc and use the value instead of setting it to some static value.

Answer 4

User-1994446809 posted

Smtaz,
Sorry I don't understand you. I am trying to get the userId of a user A, who has authority to create other users and insert into another column of same table while this user A is signing up, not when other invited users are signing up. Yes, the Created by column data type is it.
To get userId of the person who is creating new users and insert into the table column when invited users are signing up is working perfectly well. But I want to get userId of the admin user and insert at the same time into another column of the same table, while this admin user signs up.
I'm now starting to ask if it is possible while inserting data into table, you pass Id (which the Identity property is set) into another column of that same table ?

Answer 5

User-1330468790 posted

Hi georgeakpan233,

 

If the user is already logged in, you could store the user id inside a session and fetch the id from the session again when you need this user id. 

Another option is to fetch the user id using below codes if you are using asp.net identity. 

int userId = User.Identity.GetUserId<int>();

  
Then you could get the user id for current logged user and insert the corresponding data to the database.

if (check1.Checked)
  {
    int UserId = Session["userId"];
    // or int UserId = User.Identity.GetUserId<int>();


    byte[] image;
    Stream s = Filedoc.PostedFile.InputStream;
    BinaryReader br = new BinaryReader(s);
    image = br.ReadBytes((Int32)s.Length);
    // define query to be executed
    string query = @"INSERT INTO UserTable (Email, Password, confirm, Role, Name, CreatedBy, Image, Createdate) VALUES (@Email, @Password, @confirm, @Role, @Name, @CreatedBy, @Image, @Createdate);
    SELECT SCOPE_IDENTITY();";
    // set up SqlCommand in a using block   
    using (SqlCommand objCMD = new SqlCommand(query, con))
    {
        // add parameters using regular ".Add()" method 
        ......
        objCMD.Parameters.Add("@CreatedBy", SqlDbType.Int, 50).Value = UserId;
        ......
        object returnObj = objCMD.ExecuteScalar();

 

 To summarize, you should be access the logged user id as long as you have a mechanism to track it.

Best regards,

Sean

Answer 6

User-1994446809 posted
Hello Sean Fang,
What I have is that no account exists yet, so no session. And the admin user is signing up for the first time; so upon signing up his Id should be gotten and stored in another column on button sign up click.
I don't know if u understand me

Answer 7

User475983607 posted

georgeakpan233

Should I change the line "int UserId = -1;" to "int UserId > 0;" ?
Is there a way you can help me?
Or what correction can be done on this please?

You miss the point.  Your code purposely inserts -1 in the column. If you want another value like the Identity then it is up to you to write the the code.   It seems this is a registration process?  You can add to your code to update the UserTable once you have the row Identity.

DECLARE @Id INT = SCOPE_IDENTITY();
UPDATE INTO UserTable
    SET CreatedBy = @Id
WHERE UserId = @Id;

SELECT @Id;

Answer 8

User-1994446809 posted
Looks like your code has to do with stored procedure.
I am not using stored procedure

Answer 9

User475983607 posted

Looks like your code has to do with stored procedure.
I am not using stored procedure

No, the code is basic run-of-the-mill T-SQL.   I formatted the code so it is easy to read and understand.   Add the code to your existing SQL string command using string concatenation.  I can't test the code.  I'll leave the testing up to you but it should work as the UPDATE is pretty simple

string query = @"INSERT INTO UserTable (Email, Password, confirm, Role, Name, CreatedBy, Image, Createdate) " +
                " VALUES (@Email, @Password, @confirm, @Role, @Name, @CreatedBy, @Image, @Createdate); " +
                " DECLARE @Id INT; " +
                " SET @id = SCOPE_IDENTITY(); " +
                "UPDATE INTO UserTable " +
                "    SET CreatedBy = @Id " +
                " WHERE UserId = @Id; " +
                "SELECT @Id; ";