title: Inserting records in multiple tables using stored procedure in SQL Server database and ASP.NET application
body: <p>I am trying to write a database application using SQL Server and ASP.NET.</p>
  
  <p>I have three tables in my database:</p>
  
  <pre><code>Create Table PatientData
  (
      PatientID int identity(1,1) Primary Key,
      Diagnosis varchar(max),
      Investigations varchar(max),
      Complications varchar(max),
      FollowUp varchar(max)
  )
  
  Create Table Surgeries
  (
      SurgeryID int identity(1,1) Primary Key,
      PatientID int,
      SurgicalPrcedure varchar(max),
      SurgeryDate date,
      HospitalName varchar(max)
  )
  
  Create Table Media
  (
      MediaID int idnetiy(1,1) Primary Key,
      PatientID int,
      MediaLink varchar(max)
  )
  </code></pre>
  
  <p>Relationship between tables is like this: <code>PatientID</code> is FK in <code>Surgeries</code> and <code>Media</code> tables</p>
  
  <p>I am storing media files in a project folder and path into database table.
  I am using stored procedure to insert update and delete records from these tables.</p>
  
  <p>Insert stored procedure is</p>
  
  <pre><code>Create Procedure [dbo].[SPInsertSVf6]
      @PatientID int Output,
      @PatientName varchar(max),
      @Diagnosis varchar(max),
      @Investigations varchar(max),
      @Complications varchar(max),
      @FollowUp varchar(max),
      @SurgeryDate date,
      @SurgicalProcedure varchar(max),
      @HospitalName varchar(max),
      @MediaLink varchar(max)
  As
  Begin
      Set NoCount On
  
      Insert Into PatientData (PatientName, Diagnosis, Investigations, Complications, FollowUp) 
      Values (@PatientName, @Diagnosis, @Investigations, @Complications, @FollowUp)
  
      Set @PatientID = SCOPE_IDENTITY();
  
      Insert Into Surgeries (PatientID, SurgeryDate, SurgicalProcedure, HospitalName)
      Values (@PatientID, @SurgeryDate, @SurgicalProcedure, @HospitalName)
  
      Insert Into Media (PatientID, MediaLink) 
      Values (@PatientID, @MediaLink)
  End 
  </code></pre>
  
  <p>and my C# code is </p>
  
  <pre><code>namespace MultipleImages
  {
      public partial class WebForm6 : System.Web.UI.Page
      {
          SqlCommand cmd = new SqlCommand();
          SqlConnection con = new SqlConnection();
  
          protected void Page_Load(object sender, EventArgs e)
          {
              con = new SqlConnection("Data Source=QUANTUM\\SQLEXPRESS;Initial Catalog=SurgicalVideos;Integrated Security=True");
          }
  
          protected void Button1_Click(object sender, EventArgs e)
          {
              string Img = "";
  
              SqlCommand cmd = new SqlCommand("SPInsertSVf6", con);
              cmd.CommandType = CommandType.StoredProcedure;
              con.Open();
  
              cmd.Parameters.Add("@PatientID", SqlDbType.Int, 0, "PatientID");
              cmd.Parameters["@PatientID"].Direction = ParameterDirection.Output;
              cmd.Parameters.AddWithValue("PatientName", TextBox1.Text);
              cmd.Parameters.AddWithValue("Diagnosis", TextBox2.Text);
              cmd.Parameters.AddWithValue("Investigations", TextBox3.Text);
              cmd.Parameters.AddWithValue("Complications", TextBox4.Text);
              cmd.Parameters.AddWithValue("FollowUp", TextBox5.Text);
              cmd.Parameters.AddWithValue("SurgeryDate", TextBox6.Text);
              cmd.Parameters.AddWithValue("SurgicalProcedure", TextBox7.Text);
              cmd.Parameters.AddWithValue("HospitalName", TextBox8.Text);
              cmd.Parameters.AddWithValue("MediaLink", FileUpload1.FileName);
  
              foreach (HttpPostedFile hpf in FileUpload1.PostedFiles) 
              {
                  if (hpf.ContentLength &gt; 0)
                  {
                      Img = Convert.ToString(hpf);
                      Img = "Images/" + Path.GetFileName(hpf.FileName);
                      hpf.SaveAs(Server.MapPath("~/Images/") + Path.GetFileName(hpf.FileName));
                      Label3.Text += String.Format("{0}&lt;br /&gt;", hpf.FileName);
                  }
              }
  
              cmd.ExecuteNonQuery();
              con.Close();
          }
      }
  }
  </code></pre>
  
  <p>The problem I am facing is this code insert all media in project folder but only first record in database. I will be very grateful if someone please help me write the correct code to insert all records in the <code>Media</code> table. </p>
  
  <p>Thanks</p>