Update a specific row(sql) via UPDATE using if-statement in C#

Question

If a course is full, the next student will stand in line, 1,2,3 etc.. (0 = not in line, and boolean Accepted is true, meaning the student is in the course). If a student quits the course, there will be a free spot in course table.

However, using C# and OleDbCommand, how can I update every student row once course table has free spot? I want every student get 1 value less for each free spot and if student who was in queue #1 (about to hit queue 0), I want it to update the Accepted boolean to true (meaning the student is in the course).

My problem:

1. I want to update every student queue index to "--1" for each free spots.

2. I don't know how to change the specific student,with queue index 1, to boolean Accepted = true, when the student is about to hit queue index 0.

I don't know how to do this since I don't know how to combine the db and C# properly.

This is how far I can go:

The first code below, I counted amounts free spots in course via SELECT and reader

//-----------------------------   
//SELECT..... WHERE FreeSpots > 0...
int = QueueCount = 0;

while (reader.Read()) {
    QueueCount = ++QueueCount;
};     

//----------------------------
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
conn.Open();

OleDbCommand cmd = new OleDbCommand();

cmd.CommandText = "UPDATE [Course_Student] SET QueueIndex = @queueindex, Accepted = @accepted WHERE QueueIndex > 0 AND CID = " + CourseID + ";";

cmd.Connection = conn;

while (QueueCount > 0) {
    if (/*HELP: how do I check specific student with QueueIndex = 1?*/ == 1)
    {
        var pAccepted = new OleDbParameter("@accepted", SqlDbType.Bit);
        pAccepted.Value = true;
        cmd.Parameters.Add(pAccepted);
    } else {
        var pAccepted = new OleDbParameter("@accepted", SqlDbType.Bit);
        pAccepted.Value = false;
        cmd.Parameters.Add(pAccepted);
    }

    var pQueueIndex = new OleDbParameter("@queueindex", SqlDbType.Int);
    pQueueIndex.Value = /*HELP: for every row, --x QueueIndex for every students in queue*/;
    cmd.Parameters.Add(pQueueIndex);

    FreeSpot = --FreeSpot; //for later, to update freespots in course-table

    cmd.ExecuteNonQuery();
}

In other words, how can I check, in C# using OleDBCommand or whatever, check specific value before updating? Is there something similar Read.Reader method? Where I can check for every single row? To be honest, I'm completely lost here.

Answer 1

change your sql statement:

UPDATE [Course_Student] 
SET QueueIndex = QueueIndex - 1, 
    Accepted = (CASE QueueIndex WHEN 1 THEN 1 ELSE 0 END)
WHERE QueueIndex > 0 AND CID = @CourseID;

Hope this can help;