CODEWITHSUNDEEP
c#mysql
user2469932
user2469932

Reputation: 167

INSERT, and how to INSERT into multiple tables

So I have this form: http://i.imgur.com/vZYssQy.png

Now the ID, First Name, Last Name, DOB, Address, Phone Number and Post Code text boxes are supposed to INSERT into a table called person (and this works, it inserts)

My code for this is:

public static void insertStudent(int personId, string firstName, string lastName, string DOB, int phoneNumber, string address, int postCode, string majorField, int gradePointAverage)
{
    MySqlConnection conn = connection();
    conn.Open();
    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = conn;
    string myInsertSQL = "INSERT INTO person(personId, firstName, lastName, DOB, phoneNumber, address, postCode) VALUES (@personId, @firstName, @lastName, @DOB, @phoneNumber, @address, @postCode)";
    cmd.Prepare();
    cmd.CommandText = myInsertSQL;
    cmd.Parameters.AddWithValue("@personId", personId);
    cmd.Parameters.AddWithValue("@firstName", firstName);
    cmd.Parameters.AddWithValue("@lastName", lastName);
    cmd.Parameters.AddWithValue("@DOB", DOB);
    cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber);
    cmd.Parameters.AddWithValue("@address", address);
    cmd.Parameters.AddWithValue("@postCode", postCode);
    cmd.ExecuteNonQuery();
}

But the other two text boxes, GPA and Major Field need to go to a separate table called student. And the code I have for this is:

 string myInsertSQLStudent = "INSERT INTO student(majorField, gradePointAverage) VALUES (@majorField, @gradePointAverage)";
            cmd.Prepare();
            cmd.CommandText = myInsertSQLStudent;
            cmd.Parameters.AddWithValue("@majorField", majorField);
            cmd.Parameters.AddWithValue("@gradePointAverage", gradePointAverage);
            prevID(conn, cmd);

which is right underneath the above code. this does not work however, I get a crash and a message telling me Cannot add or update a child row: a foreign key constraint fails then it talks about the primary key.

however, it works KIND OF, if I fill out the text boxes, and click insert the program crashes, but the person table gets filled with the data, but the student table does not.

How would I go about making it work?

Thank you!

EDIT:

This is my person table:

personId | firstName | lastName | DOB | phoneNumber | address | postCode |

blah     |blah       |blah      |blah |blah         |blah     |blah      |blah

this is my student table

person_personId | majorField | gradePointAverage |
balh            |blah        |blah               |

Upvotes: 0

Views: 1967

Answers (2)

jenson-button-event
jenson-button-event

Reputation: 18941

It appears you have a "one-one" relationship between Person and Student, and I'm guessing Student has a PersonId foreign key, so you need to add that.

string myInsertSQLStudent = "INSERT INTO student(personId, majorField, gradePointAverage) VALUES (@majorField, @gradePointAverage)";
            cmd.Prepare();
            cmd.CommandText = myInsertSQLStudent;
            cmd.Parameters.AddWithValue("@personId", personId);
            cmd.Parameters.AddWithValue("@majorField", majorField);
            cmd.Parameters.AddWithValue("@gradePointAverage", gradePointAverage);
            prevID(conn, cmd);

On your first insert you should return the id of inserted record - you can call select @@identity as the last statement and use var id = cmd.ExecuteScalar() to get at it

  string myInsertSQL = "INSERT INTO person(personId, firstName, lastName, DOB, phoneNumber, address, postCode) VALUES (@personId, @firstName, @lastName, @DOB, @phoneNumber, @address, @postCode); 
select @@identity as id";

Upvotes: 0

zmbq
zmbq

Reputation: 39013

The student table probably has a personId field, which references the person table. You must fill that, too (in your second insert).

Also, you'd better place both inserts inside a transaction, so you don't end up with inconsistent, or kinda-working, data. With a transaction, you'll either get both records or none, not one of them like you do now.

Upvotes: 4

Related Questions