locked
Passing Uid primary key into different table and column RRS feed

  • Question

  • User-1994446809 posted

    If I have tables like this:

    Table1 (User Table)

    UiD(int) primary Key

    Email

    Pass

    Con_pass

    UserRole

    CreatedBy

    CreateDate

    1

    aaaa@gmail.com

    pass

    pass

    A

    NULL

    6/27/2020

    2

    bbbb@yahoo.com

    admin

    admin

    A

    NULL

    7/01/2020

     

    Table 2 (Wallet)

    Id (int) Primary Key

    Uid

    amount

    1

    1

    0

    1

    2

    0

    And I want to insert data into Table 2 simultaneously with Table 1. When inserting these data into Table 1, the Uid should automatically be passed from Table 1 into Uid column of Table 2 on one button click event.

    Wednesday, July 1, 2020 1:54 PM

Answers

  • User348806598 posted

    You can use scoped identity if the user table primary key is auto-incremented value-

    DECLARE @userId INT
    
    INSERT INTO User (Email, Pass, ....) VALUES ('aaaa@gmail.com', 'pass',.......)
    
    SET @userId = @@IDENTITY
    
    INSERT INTO Wallet (Uid, amount) VALUES (@MYID,0)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 1, 2020 2:33 PM
  • User475983607 posted

    Standard programming patterns.  Implement an Identity column and get the new identity after inserting into the User table.

    IF OBJECT_ID('tempdb..#User') IS NOT NULL
    	DROP TABLE #Signup
    
    IF OBJECT_ID('tempdb..#UserWallet') IS NOT NULL
    	DROP TABLE #UserWallet
    
    CREATE TABLE #User (
    	[UiD] INT	IDENTITY(1,1) PRIMARY KEY,
    	Email VARCHAR(64),
    	Pass VARCHAR(64),
    	Con_pass VARCHAR(64),
    	UserRole VARCHAR(1),
    	CreatedBy INT NULL,
    	CreateDate DATETIME DEFAULT GETDATE()
    )
    
    CREATE TABLE #Wallet(
    	Id INT	IDENTITY(1,1) PRIMARY KEY,
    	UiD INT,
    	Amount DECIMAL(7,2)
    )
    
    DECLARE @uid INT
    
    INSERT INTO #User (Email, Pass, Con_pass, UserRole)
    VALUES('aaaa@gmail.com', 'pass', 'pass', 'A')
    
    SET @uid = SCOPE_IDENTITY()
    
    INSERT INTO #Wallet(Uid, amount)
    VALUES (@uid, 0)
    
    INSERT INTO #User (Email, Pass, Con_pass, UserRole)
    VALUES('bbbb@yahoo.com', 'admin', 'admin', 'A')
    
    SET @uid = SCOPE_IDENTITY()
    
    INSERT INTO #Wallet(Uid, amount)
    VALUES (@uid, 0)
    	  
    SELECT * FROM #User
    SELECT * FROM #Wallet
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 1, 2020 2:43 PM

All replies

  • User348806598 posted

    You can use scoped identity if the user table primary key is auto-incremented value-

    DECLARE @userId INT
    
    INSERT INTO User (Email, Pass, ....) VALUES ('aaaa@gmail.com', 'pass',.......)
    
    SET @userId = @@IDENTITY
    
    INSERT INTO Wallet (Uid, amount) VALUES (@MYID,0)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 1, 2020 2:33 PM
  • User475983607 posted

    Standard programming patterns.  Implement an Identity column and get the new identity after inserting into the User table.

    IF OBJECT_ID('tempdb..#User') IS NOT NULL
    	DROP TABLE #Signup
    
    IF OBJECT_ID('tempdb..#UserWallet') IS NOT NULL
    	DROP TABLE #UserWallet
    
    CREATE TABLE #User (
    	[UiD] INT	IDENTITY(1,1) PRIMARY KEY,
    	Email VARCHAR(64),
    	Pass VARCHAR(64),
    	Con_pass VARCHAR(64),
    	UserRole VARCHAR(1),
    	CreatedBy INT NULL,
    	CreateDate DATETIME DEFAULT GETDATE()
    )
    
    CREATE TABLE #Wallet(
    	Id INT	IDENTITY(1,1) PRIMARY KEY,
    	UiD INT,
    	Amount DECIMAL(7,2)
    )
    
    DECLARE @uid INT
    
    INSERT INTO #User (Email, Pass, Con_pass, UserRole)
    VALUES('aaaa@gmail.com', 'pass', 'pass', 'A')
    
    SET @uid = SCOPE_IDENTITY()
    
    INSERT INTO #Wallet(Uid, amount)
    VALUES (@uid, 0)
    
    INSERT INTO #User (Email, Pass, Con_pass, UserRole)
    VALUES('bbbb@yahoo.com', 'admin', 'admin', 'A')
    
    SET @uid = SCOPE_IDENTITY()
    
    INSERT INTO #Wallet(Uid, amount)
    VALUES (@uid, 0)
    	  
    SELECT * FROM #User
    SELECT * FROM #Wallet
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 1, 2020 2:43 PM
  • User-1994446809 posted

    I tried to used this solution and this is what I got (shown in the image below). It is circled in blue. its an error line. what should I do? or where will the DECLARE statement be put?

    Thursday, July 2, 2020 2:13 PM
  • User475983607 posted

    I tried to used this solution and this is what I got (shown in the image below). It is circled in blue. its an error line. what should I do? or where will the DECLARE statement be put?

    The example is TSQl not C#.  Typically, this type of logic is wrapped in a stored procedure.   You can also do this in two steps.  First insert the user records and return the id.  Then insert the wallet.

    Thursday, July 2, 2020 2:23 PM
  • User-1994446809 posted
    Okay...no other way to get this through? As I don't have stored procedure in my project.
    I will need enough time to learn about stored procedure..
    Thursday, July 2, 2020 2:56 PM
  • User348806598 posted

    You definitely can-

            protected void Page_Load(object sender, EventArgs e)
            {
                int userId = -1;
    
                SqlConnection connection = new SqlConnection("connectionString");
                // define query to be executed
                string query = @"INSERT INTO user (Email, Password, ...)
                                  VALUES (@email, @password,......);
                         SELECT SCOPE_IDENTITY();";
    
                // set up SqlCommand in a using block   
                using (SqlCommand objCMD = new SqlCommand(query, connection))
                {
                    // add parameters using regular ".Add()" method 
                    objCMD.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = "'aaaa@gmail.com'";
                    objCMD.Parameters.Add("@password", SqlDbType.VarChar, 100).Value = "pwd";
                    //...Other parameters
                    //..
                    // open connection, execute query, close connection
                    connection.Open();
                    object returnObj = objCMD.ExecuteScalar();
    
                    if (returnObj != null)
                    {
                        int.TryParse(returnObj.ToString(), out userId);
                    }
                }
    
                if (userId > 0) {
                    query = @"INSERT INTO Wallet (Uid, amount) VALUES (@userId,@amount)";
                    using (SqlCommand objCMD = new SqlCommand(query, connection))
                    {
                        // add parameters using regular ".Add()" method 
                        objCMD.Parameters.Add("@userId", SqlDbType.Int, 50).Value = userId;
                        objCMD.Parameters.Add("@amount", SqlDbType.Float, 100).Value = 0; //Change type here accordingly
                        //...Other parameters
                        //..
                        // open connection, execute query, close connection
                        connection.Open();
                        object returnObj = objCMD.ExecuteScalar();
    
                        if (returnObj != null)
                        {
                            int.TryParse(returnObj.ToString(), out userId);
                        }
    
                    }
                }
                connection.Close();
            }
    Thursday, July 2, 2020 3:18 PM
  • User379720387 posted

    Forget about stored procedures for now. That is just unnecessary complication at this point.

    The answer before me shows how you can get the Id of the just inserted record to put into Wallet.

    Thursday, July 2, 2020 8:11 PM