0

I am trying to insert values to two tables at the same time. I have a dbo.Student and dbo.Guardian. The Guardian table has a foreign key studentID. I am trying to insert values to it but it always shows NULL. I am trying to insert both student information and guardian information at the same time in one click of a button. Please help

//Save student information
cn.Open();
cm = new SqlCommand("INSERT INTO Student (studentNo, Lname, Fname, MI, gender, yearLevel, section, studImage, isActive)  VALUES (@studentNo, @Lname, @Fname, @MI, @gender, @yearLevel, @section, @studImage, 'true')", cn);
cm.Parameters.AddWithValue("@studentNo", txtStudentNo.Text);
cm.Parameters.AddWithValue("@Lname", txtLname.Text);
cm.Parameters.AddWithValue("@Fname", txtFname.Text);
cm.Parameters.AddWithValue("@MI", txtMI.Text);
cm.Parameters.AddWithValue("@gender", gender);
cm.Parameters.AddWithValue("@yearLevel", txtYear.Text);
cm.Parameters.AddWithValue("@section", txtSection.Text);
cm.Parameters.AddWithValue("@studImage", img);
cm.ExecuteNonQuery();
cn.Close();


//Save guardian information
cn.Open();
cm = new SqlCommand("INSERT INTO Guardian (studentID, name, address, contactNo) VALUES (@studentID, @name, @address, @contactNo)", cn);
cm.Parameters.AddWithValue("@name", txtGuardianName.Text);
cm.Parameters.AddWithValue("@address", txtAddress.Text);
cm.Parameters.AddWithValue("@contactno", txtContactNo.Text);
cm.ExecuteNonQuery();
cn.Close();
kmandrew
  • 43
  • 6

1 Answers1

0

Option 1

Use Entity Framework or any ORM for Database.

This Option Not Recommended

To Solve This Problem

Step 1 Modified Your SQL Query

Check Execute Insert command and return inserted Id in Sql

Add below line in Student Query

output INSERTED.<Student_Table_Primary_Key_Column_Name>

Step 2 Replace cm.ExecuteNonQuery() with

//Return Inserted Student Table ID
int stdid =(int)cm.ExecuteScalar();

Step 3 Add stdid in Guardian Table

// set Student ID in Guardian Table
cm.Parameters.AddWithValue("@studentID", stdid);
Muhammad Usama Alam
  • 209
  • 4
  • 13
  • While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Dharman Jul 14 '20 at 14:04
  • I don't believe `int stdid =(int)cm.ExecuteScalar();` will return the id unless you modify the sql call. – LarsTech Jul 14 '20 at 14:23
  • Good day, it says invalid column name ID – kmandrew Jul 15 '20 at 03:34
  • I don't know where i did wrong – kmandrew Jul 15 '20 at 03:35
  • output INSERTED. – Muhammad Usama Alam Jul 15 '20 at 09:42