How to obtain last insert id?

Question

I want to insert into two tables while taking the last inserted id and then adding it to the second table. I have already tried to use ExecuteNonScalar but it didn't work. Here is my code:

string iquery = "insert into [User] (username, password, role) values (@username,@password,@role)";
                        SqlCommand cmd = new SqlCommand(iquery, conn);
                        cmd.Parameters.AddWithValue("@username", username);
                        cmd.Parameters.AddWithValue("@password", password);
                        cmd.Parameters.AddWithValue("@role", "Customer");
                        cmd.ExecuteNonQuery();
                        //get last insert User ID, insert into borrower 
                        int id = Convert.ToInt32(cmd.ExecuteScalar());
                        string iquery2 = "insert into [borrower] (userid, name, phone, email, type) values (@id, @name, @phone, @email, @type)";
                        SqlCommand cmd1 = new SqlCommand(iquery2, conn);
                        cmd1.Parameters.AddWithValue("@id", id);
                        cmd1.Parameters.AddWithValue("@name", name);
                        cmd1.Parameters.AddWithValue("@phone", phone);
                        cmd1.Parameters.AddWithValue("@email", email);
                        cmd1.Parameters.AddWithValue("@type", type);
                        cmd1.ExecuteNonQuery();

When I compile and run the code the id inserted is 0. Any idea what I am doing wrong?

Answer 1

You should alter a bit your sql statement:

string iquery = "INSERT INTO [User] 
                 (username, password, role) 
                 VALUES (@username,@password,@role)
                 SELECT SCOPE_IDENTITY()";

Basically, SCOPE_INDETITY

Returns the last identity value inserted into an identity column in the same scope.

For further info, please have a look here.

Furthermore, you don't need cmd.ExecuteNonQuery(); before

int id = Convert.ToInt32(cmd.ExecuteScalar());