Passing Uid primary key into different table and column

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	[e-mail]	pass	pass	A	NULL	6/27/2020
2	[e-mail]	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.

Answer 1

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 ('[e-mail]', 'pass',.......)

SET @userId = @@IDENTITY

INSERT INTO Wallet (Uid, amount) VALUES (@MYID,0)

Answer 2

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('[e-mail]', 'pass', 'pass', 'A')

SET @uid = SCOPE_IDENTITY()

INSERT INTO #Wallet(Uid, amount)
VALUES (@uid, 0)

INSERT INTO #User (Email, Pass, Con_pass, UserRole)
VALUES('[e-mail]', 'admin', 'admin', 'A')

SET @uid = SCOPE_IDENTITY()

INSERT INTO #Wallet(Uid, amount)
VALUES (@uid, 0)
	  
SELECT * FROM #User
SELECT * FROM #Wallet

Answer 3

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?

Answer 4

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.

Answer 5

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

Answer 6

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 = "'[e-mail]'";
                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();
        }

Answer 7

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.