Vivek Vijayakumar Vivek Vijayakumar - 2 years ago 89
SQL Question

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

Am using 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);


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


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);


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 Source

You can just use LastInsertedId field :

long modified = cmd.LastInsertedId;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download