CODEWITHSUNDEEP
c#mysqlasp.net
Vivek Vijayakumar
Vivek Vijayakumar

Reputation: 13

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

Upvotes: 1

Views: 731

Answers (2)

Alexander Pacha
Alexander Pacha

Reputation: 9710

Looks like you have executed cmd.ExecuteNonQuery() before calling ExecuteScalar() method. I guess that the second call might fail and therefore not return an id.

Maybe you should check out a tutorial on how to use MySQL with C#

Upvotes: 0

Abdellah OUMGHAR
Abdellah OUMGHAR

Reputation: 3745

You can just use LastInsertedId field :

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

Upvotes: 2

Related Questions