title: Same ID with different value inserted into database
body: <p>I would like to insert same id with different value into database. Basically it is a form which allow the user to add new scholarship with some criteria. Not all scholarships will be applicable to all courses. Hence there will be two tables, <code>scholarship</code> which will take the new scholarships, and <code>scholarshipCourse</code> which will handle the eligibility of courses. </p>
  
  <p>I want to insert both data at the same time. where the user press submit, the scholarship id in <code>scholarship</code> table will be auto increment whereas the <code>scholarshipcourse</code> table will take the new scholarship id and insert</p>
  
  <p>For example:</p>
  
  <p>Table <code>scholashipCourse</code>:</p>
  
  <pre><code>scholarship_id    course
  -------------------------
      1             math
      1             english
      2             english
      2             science
  </code></pre>
  
  <p>Table <code>scholarship</code>:</p>
  
  <pre><code>scholarship_id     scholarshipName 
  --------------------------------------
        1            moon scholarship
        2            star scholarship
        3            light scholarship
  </code></pre>
  
  <p>Assume scholarship_id 3 is the new added scholarship, how do i get that id and insert into the scholarshipcourse table simultaneously.</p>
  
  <p>I can use this select statement:</p>
  
  <pre><code>INSERT INTO user (name) 
  VALUES ('John Smith');
  
  INSERT INTO user_details (id, weight, height)
  VALUES (SELECT(id FROM user WHERE name = 'John Smith'), 83, 185); 
  </code></pre>
  
  <p>but how do I get the latest id that has just be added?</p>
  
  <p>i will need to insert to my DAL like:</p>
  
  <p>public DataTable test(string name, string course)
          {</p>
  
  <pre><code>        SqlCommand cmd = new SqlCommand();
          SqlConnection myConnection = new SqlConnection(strConnectionString);
  
          cmd.Connection = conn;
          cmd.CommandText = "INSERT INTO Table1(schName)  values (@schName)";
          cmd.Parameters.AddWithValue("@schName", name);
          conn.Open();
          int i = cmd.ExecuteNonQuery();
          int id = (int)cmd.ExecuteScalar();
  
          cmd.CommandText = "Select SCOPE_IDENTITY();INSERT INTO Table2(ScholarshipID,DiplomaCourse) values (@id,@course)";
          cmd.Parameters.AddWithValue("@id", id);
          cmd.Parameters.AddWithValue("@course", course);
  
  
          SqlDataAdapter da = new SqlDataAdapter();
          da.SelectCommand = cmd;
          DataTable dt = new DataTable();
          da.Fill(dt);
          return dt;
  
      }
  </code></pre>
  
  <p>it is successfully added to two tables but it does not retrieve the last id and insert to the table</p>