title: SQL server: insert into using select and other parameter
body: <p>I got 3 tables: <strong>projects</strong>, <strong>managers</strong> and <strong>project_manager</strong></p>
  
  <p><strong>managers</strong></p>
  
  <ul>
  <li>manager_id (int, PK)</li>
  <li>manager_name</li>
  </ul>
  
  <p><strong>projects</strong></p>
  
  <ul>
  <li>project_id (int, PK)</li>
  <li>project_name</li>
  </ul>
  
  <p><strong>project_manager</strong></p>
  
  <ul>
  <li>project_id  (int, PK)</li>
  <li>manager_id  (int, PK)</li>
  </ul>
  
  <p>Assume that a manager is currently logged-in (so we know what <strong>manager_id</strong> is) and he want to create a new <strong>Project</strong>, by entering a name into txtName textbox and hit "create" button. I tried to write: </p>
  
  <p>(1) a query to insert a new project (name) into <strong>projects</strong> table and </p>
  
  <p>(2) a single query that insert <strong>project_id</strong> &amp; <strong>manager_id</strong> into <strong>project_manager</strong> table </p>
  
  <p>but got syntax error at the third query string and this is not a good way since inserting into projects table (query 1) may be succeeded but inserting into other table (query 2) may be failed, I dont know much "advanced stuff" of SQL, definitely need help:</p>
  
  <pre><code>string CS = ConfigurationManager.ConnectionStrings["CTECS"].ConnectionString;
  using (SqlConnection con = new SqlConnection(CS))
  {
      string q1 = "SELECT COUNT(*) FROM [projects] WHERE project_name = @Name";
      SqlCommand cmd = new SqlCommand(q1, con);
      cmd.Parameters.AddWithValue("@Name", txtName.Text);
      con.Open();
      int i = (int)cmd.ExecuteScalar();
      if (i &lt; 1)
      {
          string Command = "INSERT INTO [dbo].[projects] (project_name) VALUES (@Name)";
          cmd = new SqlCommand(Command, con);
          cmd.Parameters.AddWithValue("@Name", txtName.Text);
          cmd.ExecuteNonQuery();
  
          Command = "INSERT INTO [project_manager] (project_id, manager_id) VALUES (SELECT project_id FROM [projects] WHERE project_name = @Name, @ID)";
          cmd = new SqlCommand(Command, con);
          // Provide the value for the parameter
          cmd.Parameters.AddWithValue("@Name", txtName.Text);
          cmd.Parameters.AddWithValue("@ID", currentUser.userID.ToString());
          cmd.ExecuteNonQuery();
      }
      else
      {
          Panel1.Visible = true;
          lblMessage.CssClass = "alert alert-error";
          lblMessage.Text = "This project's name has been already used.";
          return;
      }
  }
  </code></pre>