Vivek Vijayakumar Vivek Vijayakumar - 6 months ago 26
SQL Question

get primary key of recently added record and insert into another table as foreign key

Am using asp.net as front end and MySQL as back end.
I have two tables "Registration" and "User".
I have a registration form, in that i have registration details along with login details.
Wen user click register the registration details is saved to "Registration" and login details is saved to "User" with foreign key Reg_Id of table Registration.

this is my code:

DateTime dob = DateTime.Parse(txtDob.Text);
string formattedDate = dob.ToString("yyyy-MM-dd");

string qry = "insert into Registration(First_Name,Last_Name,Dob,Place,Zip,Phone_Number)values(@fname,@lname,@dob,@place,@zip,@phone);SELECT SCOPE_IDENTITY()";


MySqlCommand cmd = new MySqlCommand(qry, Connection.get());

cmd.Parameters.AddWithValue("@fname", txtFname.Text);

cmd.Parameters.AddWithValue("@lname", txtLname.Text);

cmd.Parameters.AddWithValue("@dob", formattedDate);

cmd.Parameters.AddWithValue("@place", txtPlace.Text);

cmd.Parameters.AddWithValue("@zip", txtZip.Text);

cmd.Parameters.AddWithValue("@phone", txtPhone.Text);

cmd.ExecuteNonQuery();

int modified = (int)cmd.ExecuteScalar();

Connection.close();

string qryToUser = "insert into User(User_Name,Password)values(@user,@password)";

MySqlCommand cmd1 = new MySqlCommand(qryToUser, Connection.get());

cmd.Parameters.AddWithValue("@user", txtUsername.Text);

cmd.Parameters.AddWithValue("@password", txtPasswordConfirm.Text);

cmd1.ExecuteNonQuery();


I am not able to get the REG_Id using cmd.ExecuteScalar() method.
Is there any other way or did i do anything wrong???
Please someone help me

Answer

You can just use LastInsertedId field :

cmd.ExecuteNonQuery();
long modified = cmd.LastInsertedId;