Insert multiple data into two tables and need to insert the last inserted id into another table

Question

I have a form which use two tables to insert the data.

Some column in the form would be like:

scholarship name, course, year

Two tables that are involved are:

scholarshipDetail , scholarshipCourse.

• scholarshipDetail table has scholarshipName and year
• scholarshipCourse table has scholarshipID, course

scholarshipDetail:

schid      schName     year
-----------------------------
1          star         2015
2          moon         2016

scholarshipCourse:

schID    course
------------------
1        maths
1        english
2        maths

Assuming that the new user wants to add new scholarship which means the id will 3 and it insert into two tables. How do I that? (MANAGED TO INSERT ALR)

NEW ERROR: EDITED

public DataTable test(string name, string course)
{
    string insertsql = "INSERT INTO Table1(schName) OUTPUT INSERTED.addID  values (@schName)";

    SqlCommand cmd = new SqlCommand(insertsql,conn);

    cmd.Parameters.AddWithValue("@schName", name);

    conn.Open();
    int i = cmd.ExecuteNonQuery();

    var table1Id = (int)cmd.ExecuteScalar();

    string insertsql1 = "INSERT INTO Table2(ScholarshipID, DiplomaCourse) VALUES (@id, @course)";

    SqlCommand cmd2 = new SqlCommand(insertsql1, conn);
    cmd2.Parameters.AddWithValue("@id", table1Id);
    cmd2.Parameters.AddWithValue("@course", course);

    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;

    da.SelectCommand = cmd2;
    DataTable dt = new DataTable();
    da.Fill(dt);

    return dt;
}

The output in my table is

Table1

schID     schname
-------------------
1            jj
2            jj

Table2

TableID     schID       Course
------------------------------
1            2          Maths

the data is being inserted twice in Table1. why is that so? (SOLVED)

Edited:

Now the problem is, there will be checkboxes which allow the user to choose which course is applicable for the scholarship.

When the user click all checkbox, only the last checkbox will insert into database.

In my codebehind(cs):

protected void Button1_Click(object sender, EventArgs e)
{
      //  addScholarship[] test = new addScholarship[1];

        string course = "";
        string Name = schName.Text;

        if (DIT.Checked )
        {
            course = "DIT";
        }

        if (DFI.Checked)
        {
            course = "DFI";
        }

        addScholarship[] insertName = new addScholarship[1];
        addScholarship insertedName = new addScholarship(Name,course);

        scholarshipBLL obj = new scholarshipBLL();
        DataTable dt = obj.test(Name, course);
}

Answer 1

BEGIN;

  INSERT INTO scholarshipDetail(schid,schName,year)  VALUES(3,'sun',2017);

  INSERT INTO scholarshipCourse(schID,course) VALUES(LAST_INSERT_ID(),'science');

COMMIT;

Answer 2

You are executing the command twice.

int i = cmd.ExecuteNonQuery();

var table1Id = (int)cmd.ExecuteScalar();

You need to execute only one. I think removing cmd.ExecuteNoteQuery would solve your issue.

Answer 3

For the latest problem you posted. You are calling obj.test method only once after all the if blocks.

So the "course" variable will have value from the latest if block where the condition is true.

You need to call DataTable dt = obj.test(Name, course); method in every if block. That means if checkbox is checked you call insert row. If not checked then you don't insert the row. Following is the code you should put in your button_click.

string course = "";
string Name = schName.Text;
scholarshipBLL obj = new scholarshipBLL();

List<addScholarship> addScholarshipList= new List<addScholarship>();

addScholarship scholarship;
if (DIT.Checked )
{
    scholarship = new addScholarship(Name,course);
    addScholarshipList.Add(insertedName);
    course = "DIT";
    DataTable dt = obj.test(Name, course);

}

if (DFI.Checked)
{
    scholarship = new addScholarship(Name,course);
    addScholarshipList.Add(insertedName);
    course = "DFI";
    DataTable dt = obj.test(Name, course);

}