CODEWITHSUNDEEP
c#sqlsql-server
AlgorithNewbie
AlgorithNewbie

Reputation: 47

It will not insert if the value of the 2 table are the same

My problem is , I want to make an Enrollment System that will not insert the value if the time is conflict for example

I have a two table which is Enlistment and Subject. This is for Enlistment

Enlistment

Table : Subject enter image description here

Now my Problem is if I inserted a value which will conflict the time and days, my program will just Insert it , and not give an error The schedule cannot be inserted cause of conflict and that is my problem.

This is the possible Output that I want:

I will insert a value in Enlistment example: 48490000, 48490000, 103, 8:00 - 9:30 AM, MWF

now this will create an error which is cannot insert conflict schedule and it will now allow it and this is my code

string offerNo = "", capacity = "", offNo = "" , time = "", day = "";
        string a = "", b = "";

        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();
        if (dr.Read())
        {
            offerNo = dr[0].ToString();
        }
        dr.Dispose();

        cmd = new SqlCommand("Select a.time, a.days from Enlistment a, Subject b where a.offerNo = b.offerNo AND a.studID = '" + textEnrollID.Text + "'", conn);
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            time = dr[0].ToString();
            day = dr[1].ToString();
        }
        dr.Dispose();

        cmd = new SqlCommand("Select offerNo,capacity,days,time from Subject  where offerNo ='" + textEnrollOfferNo.Text +  "'", conn);
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            offNo = dr[0].ToString();
            capacity = dr[1].ToString();
            a = dr[2].ToString();
            b = dr[3].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 && Convert.ToInt32(capacity) == 0)
        {
            MessageBox.Show("Subject is closed!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            textEnrollOfferNo.Clear();
            textEnrollOfferNo.Focus();
        }
        else if ((a == day && b == time) || (day == a && time == b))
        {
            MessageBox.Show("The schedule is conflict!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            textEnrollOfferNo.Clear();
            textEnrollOfferNo.Focus();
        }
        else if ((textEnrollOfferNo.Text != offerNo && a != day && b != time) || (textEnrollOfferNo.Text != offerNo && a == day && b != time) || (textEnrollOfferNo.Text != offerNo && a != day && b == time))
        {
            //Button Add Subject for Student
            conn = koneksyon.getConnect();
            conn.Open();
            cmd = new SqlCommand("AddSubject", conn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add("@enlistID", SqlDbType.Int).Value = Convert.ToInt32(textEnrollID.Text);
            cmd.Parameters.Add("@studID", SqlDbType.Int).Value = Convert.ToInt32(textEnrollID.Text);
            cmd.Parameters.Add("@offerNo", 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 = koneksyon.getConnect();
            conn.Open();
            dataGridView1.Rows.Clear();
            cmd = new SqlCommand("Select a.offerNo,b.subj,b.description,b.units,b.room,b.days,b.time from Enlistment a, Subject b where a.offerNo = b.offerNo AND a.studID ='" + textEnrollID.Text + "'", conn);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                dataGridView1.Rows.Add(dr[0], dr[1], dr[2], dr[3], dr[6], dr[4], dr[5]);
            }
            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();

Upvotes: 0

Views: 106

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5235

I assume you have access to SQL Server Management Studio? If so, open a New Query window and paste the following in:

declare @enlistment table
(
EnlistID int,
StudID int,
OfferNo int,
StartTime datetime,
EndTime datetime,
StudDays varchar(3)
)
insert into @enlistment VALUES
(48490000, 48490000, 101, '08:00:00', '09:00:00', 'MWF'),
(48490000, 48490000, 102, '08:00:00', '09:00:00', 'TTh')

declare @subject table
(
OfferNo int,
subj varchar(10),
description varchar(200),
units int,
room varchar(20),
StudDays varchar(3),
StartTime datetime,
EndTime datetime,
Capacity int
)

INSERT INTO @subject VALUES
(101, 'IT 14', 'Software Engineering', 3, 'IT L 2', 'MWF', '08:00:00', '09:00:00', 4),
(102, 'IT 15', 'Multimedia Systems', 3, 'IT L 3', 'TTh', '17:00:00', '18:30:00', 5),
(103, 'Acctg 1', 'Intro to Accounting', 3, 'J03', 'TTh', '17:00:00', '18:30:00', 5),
(104, 'Re ed 1', 'Salvation History', 3, 'CH302', 'MWF', '08:00:00', '09:00:00', 5),
(105, 'Eng 3', 'Speech and Orators', 3, 'Speech Lab', 'MWF', '09:00:00', '10:00:00', 5)

declare @newStudID int = 48490000;
declare @newStart datetime = '08:00:00'
declare @newEnd datetime = '09:00:00'
declare @newDays varchar(3) = 'MWF'
declare @newOfferNo int = 103

INSERT INTO @enlistment 
SELECT @newStudID, @newStudID, @newOfferNo, @newStart, @newEnd, @newDays
WHERE NOT EXISTS (
SELECT 1 FROM @enlistment WHERE StudID = @newStudID 
and ((@newStart >= StartTime AND @newStart <= EndTime) OR  
(@newEnd <= EndTime AND @newEnd >= StartTime) OR 
(StartTime >= @newStart AND StartTime <= @newEND ) OR
(EndTime >= @newStart AND EndTime <= @newEND )) 
AND StudDays = @newDays)

SELECT @@ROWCOUNT as Success

If you run this, it will show Success 0, because the time overlaps with an existing course. Play around with Start and End Times. It should always block any combination that intersects/overlaps with an existing course.

This same logic you can simply transfer to your c# (the SQL you can take verbatim). You basically have a choice: you can either run the whole INSERT and check RowsAffected (0 if it failed to insert), or you can run e.g. SELECT OfferNo FROM Enlistment WHERE .. as a separate pre-check; if the result of this is an empty result set, then you can go ahead and insert; if it returns something you can give a message pointing out that it conflicts with the given course number. Of the two, the second is probably more friendly!

ADDENDUM

BTW In the above example, I am assuming that courses are either MWF or TTh. If you want to cope with other combinations, then it would be better again to change the table design and have Booleans to represent Monday, Tuesday, Wednesday, Thursday and Friday. The check becomes a little harder(!) but it is still feasible. Let me know if this is the case.

Upvotes: 2

Related Questions