Mini-registration Check if Room is booked or open

[WickedFooker]

I have been creating a smaller scale MUCH SIMPLIFIED version of a registration system that assumes classes are taught once a week. This system already checks if a teacher has taught 3 classes to not select him for further classes. But I need to make sure it also checks that the room is not already chosen for a class previous during the time it wants. What I have so far selects rooms and shows them in a drop down list, but I am able to make it show all room less everything chosen so far but it does not take into consideration the time and day of the week and so it is eliminating anything chosen, not just for the day of the week and the start_Hour and end_Hour times.

// Populate Room and add Please Select
// This shows ALL Rooms ---> string commandText1 = "SELECT * FROM tblRoom";\
// Below shows ALL rooms minus any chosen before but does not consider day of the week (week_Day) or (start_Time and end_Time) of the class.
string commandText1 = "SELECT * FROM tblRoom WHERE NOT EXISTS (SELECT * FROM tblClass WHERE tblRoom.room_Num = tblClass.room_Num)";

        var ds1 = new DataSet();

        using (var connection = new OleDbConnection(connectionString))
        using (var command = new OleDbCommand(commandText1, connection))
        {
            // OleDbCommand uses positional, rather than named, parameters.
            // The parameter name doesn't matter; only the position.
            command.Parameters.AddWithValue("@p0", ddlRoom.SelectedValue);

            var adapter = new OleDbDataAdapter(command);
            adapter.Fill(ds1);
        }

        ddlRoom.DataSource = ds1;
        ddlRoom.DataTextField = "room\_Num";
        ddlRoom.DataValueField = "room\_Num";
        ddlRoom.DataBind();
        ddlRoom.Items.Insert(0, new ListItem("Please Select", "0"));
        ddlRoom.Items.Insert(1, new ListItem("TBD", "TBD"));
    }

}

tblRoom consists of tblRoom.room_Num (Key) TextString with Room# tblClass consists of tblClass.room_Num (FK) TextString, CourseID (FK) from tblCourse, week_Day which is the Day Of the Week (Monday, Tueday, Wednesday, Thursday, Friday or Saturday). start_Hour and end_Hour for when the class begins and ends. What I am looking for is to improve the select command above so it can use week_Day = ? (ddlweekDay.SelectedValue) start_Hour (ddlstarthour.selectedvalue), end_Hour (ddlendHour.SelectedValue) OR to "r

[Bernard Grosperrin]

Seems difficult to give you a precise answer without knowing more about the database. From my point of view, but I might be biased as I come more from a SQL background than many, I would create a SQL View, taking parameters (stored procedure) returning all ClassRooms available between 2 datetime (start and end), and I would do my select against this view. Also, as I guess classes starts and end at a fixed hour, instead of using a whole datetime twice, I would build a fixed table with ID values, for example, from 8 to 8:45 AM = #1, from 9 to 9:45 = #2, and so on... Just some ideas....

Bernard Grosperrin BGSoftFactory