Overview: this is a code where a student can sign up for courses he want to read. He should only be accepted into one course, not more(this is where I got problem with). Although the student can search up to 3 courses at same time by priority selection 1-3.
If a courses is full then students get a queue-index, starting from 1 and up. Once there's a free spot in the course(that's where this syntax below is being used, looping for each free spot in a course), the student with queue-index 1 hit 0 and he's accepted into the course.
cmd.CommandText = "UPDATE [Register] SET QueueIndex = QueueIndex - 1, Accepted = IIF(QueueIndex = 1, 1, 0) WHERE QueueIndex > 0 AND CID = @cid;";
However I don't know how to improve this syntax and implement a checker if that student is already accepted. If he already is, then he should be NOT be accepted(and set his queue-index to 0 so he's no longer in queue, and accepted is still false) and check for the next student instead. I don't know how to improve this syntax.
In the db-table, I'm using CID(course ID), SID(student ID), Accepted(bool), Selection(for 1-3 course choices, not important here), QueueIndex(int)
I would like to implement some kind of.. If that current student with QueueIndex = 1, AND if any of his/SID choices/selections is Accepted = true THEN QueueIndex = 0 and Accepted = false, so that current choice is no longer relevant since he's already accepted.