title: C# Enrollment System schedule conflict
body: <p>I have a problem in my system, I want to put an error after I insert a value into the database. This is my sample for better explanation. </p>
  
  <p>I have two table which is Enlistment and Subject, and this are the sample value .</p>
  
  <p><img src="https://i.stack.imgur.com/bHd5Q.png" alt="My Table with sample Value"></p>
  
  <p>Now my problem is I want to put and error my the time will be the same or conflict for example :
  I will ad a value on the Enlistment table which is this .</p>
  
  <p><img src="https://i.stack.imgur.com/BDqhy.jpg" alt="I insert new value to Enlistment Table"></p>
  
  <p>Now this will create an Error because as you can see on the Subject table ,
  offerNo = 102 Have the same value of StartTime, EndTime and Days of inserted value in the Enlistment table. Then if it is not the same value it will just insert into the table without getting an error. Can you give me some codes , on how to solve this, Thank you for the help</p>
  
  <p>This is my code for precedure</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[addSubject]
  (
  @newStudID int,
  @newofferNo int
  )
  AS
  BEGIN
  BEGIN TRANSACTION
  
  declare @newStart datetime
  set @newStart = (select StartTime from Subject where offerNo = @newofferNo)
  declare @newEnd datetime
  set @newEnd = (select EndTime from Subject where offerNo = @newOfferNo)
  declare @newDays varchar(50)
  set @newDays = (select StudDays from Subject where offerNo = @newOfferNo)
  
  insert into Enlistment values(@newStudID, @newStudID, @newOfferNo, @newStart, @newEnd, @newDays)
  
  UPDATE Subject
  SET Capacity = Capacity - 1
  WHERE offerNo = @newofferNo
  
  COMMIT TRANSACTION 
  END
  </code></pre>
  
  <p>Now this is my code in the Front End</p>
  
  <pre><code>        conn = koneksyon.getConnect();
          conn.Open();
          cmd = new SqlCommand("Select a.offerNo  from Enlistment a, Subject b where a.offerNo = b.offerNo AND a.offerNo ='" + textEnrollOfferNo.Text + "' AND a.studID = '" + textEnrollID.Text + "'", conn);
          dr = cmd.ExecuteReader();
          while (dr.Read())
          {
              offerNo = dr[0].ToString();
          }
          dr.Dispose();
  
          cmd = new SqlCommand("Select offerNo,Capacity,StartTime, EndTime,StudDays from Subject  where offerNo ='" + textEnrollOfferNo.Text +  "'", conn);
          dr = cmd.ExecuteReader();
          while (dr.Read())
          {
              offNo = dr[0].ToString();
              capacity = dr[1].ToString();
              start = dr[2].ToString();
              end = dr[3].ToString();
              day = dr[4].ToString();
  
          }
          dr.Dispose();
  
          cmd = new SqlCommand("Select StartTime, StudDays from Enlistment where StudDays =  '" + day + "'", conn);
          dr = cmd.ExecuteReader();
          while (dr.Read())
          {
              START = dr[0].ToString();
              DAY = dr[1].ToString();
          }
          dr.Dispose();
  
  
          cmd.Dispose();
          conn.Close();
          if (textEnrollOfferNo.Text == "")
          {
              MessageBox.Show("Input Offer No.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
              textEnrollOfferNo.Clear();
              textEnrollOfferNo.Focus();
          }
          else if (textEnrollOfferNo.Text == offerNo)
          {
              MessageBox.Show("Cannot insert duplicate schedules", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
              textEnrollOfferNo.Clear();
              textEnrollOfferNo.Focus();
          }
          else if (offNo != textEnrollOfferNo.Text)
          {
              MessageBox.Show("Offer No doesn't exist!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
  
              textEnrollOfferNo.Clear();
              textEnrollOfferNo.Focus();
          }
          else if (textEnrollOfferNo.Text == offNo &amp;&amp; Convert.ToInt32(capacity) == 0)
          {
              MessageBox.Show("Subject is closed!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
              textEnrollOfferNo.Clear();
              textEnrollOfferNo.Focus();
          }
          else if( (textEnrollOfferNo.Text != offerNo  &amp;&amp; START == start &amp;&amp; DAY == day) || (offerNo != textEnrollOfferNo.Text &amp;&amp; start == START &amp;&amp; day == DAY) || (textEnrollOfferNo.Text != offerNo &amp;&amp; START == start &amp;&amp; day == DAY) || (textEnrollOfferNo.Text != offerNo &amp;&amp; start == START &amp;&amp; DAY == day))
          {
              MessageBox.Show("Subject is conflict!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
              textEnrollOfferNo.Clear();
              textEnrollOfferNo.Focus();
          }
          else
          {
              //Button Add Subject for Student
              conn = koneksyon.getConnect();
              conn.Open();
              cmd = new SqlCommand("AddSubject", conn);
              cmd.CommandType = CommandType.StoredProcedure;
  
  
              cmd.Parameters.Add("@newStudID", SqlDbType.Int).Value = Convert.ToInt32(textEnrollID.Text);
              cmd.Parameters.Add("@newofferNo", SqlDbType.Int).Value = Convert.ToInt32(textEnrollOfferNo.Text);
  
              cmd.ExecuteNonQuery();
              conn.Close();
              cmd.Dispose();
  
              MessageBox.Show("Added Successfully! ", "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information);
              conn = koneksyon.getConnect();
              conn.Open();
              dataGridView1.Rows.Clear();
              cmd = new SqlCommand("Select a.offerNo,b.subj,b.description,b.units,b.StartTime, b.EndTime,b.room,b.StudDays from Enlistment a, Subject b where a.offerNo = b.offerNo AND a.studID ='" + textEnrollID.Text + "' ORDER BY a.StudDays, a.StartTime", conn);
              dr = cmd.ExecuteReader();
              while (dr.Read())
              {
                  string startTime = dr[4].ToString();
                  string EndTIme = dr[5].ToString();
                  DateTime start1 = DateTime.Parse(startTime);
                  DateTime end1 = DateTime.Parse(EndTIme);
  
                  string Time = start1.ToString("h:mm") + " - " + end1.ToString("h:mm tt");
  
                  dataGridView1.Rows.Add(dr[0], dr[1], dr[2], dr[3], Time, dr[6], dr[7]);
              }
              dr.Dispose();
              cmd.Dispose();
              conn.Close();
  
              conn = koneksyon.getConnect();
              conn.Open();
  
              cmd = new SqlCommand("Select sum(b.units) from Enlistment a, Subject b where a.offerNo = b.offerNo AND a.studID ='" + textEnrollID.Text + "'", conn);
              dr = cmd.ExecuteReader();
              while (dr.Read())
              {
                  textTotalUnits.Text = dr[0].ToString();
              }
              dr.Dispose();
              conn.Close();
              cmd.Dispose();
          }
          dr.Dispose();
          cmd.Dispose();
          conn.Close();
  </code></pre>