Rahul Sharma Rahul Sharma - 7 months ago 24
Vb.net Question

How to prevent overbooking rooms?

I am trying to make restriction for new reservation between two dates. For example during Reservation I selected DateIN is 24/12/2013 and DateOUT is 28/12/2013 and selected Room No. and saved the record in database.

Now I want to restrict any reservation between these two dates like there should be restriction to Reserve room. For example DateIN= 26/12/2013 and DateOut= 30/12/2013 because room is already booked till 28/12/2013 so DateIN must be greater than 28/12/2013.

How can I make this restriction using VB.NET and MS Access? I tried this code but it fails in some conditions.

con = New OleDbConnection(cs)
Dim ct As String = "SELECT RoomNo FROM Temp_Reservation WHERE RoomNo='" _
& cmbRoomNo.Text & "' AND DateOut BETWEEN #" & dtpDateIN.Text _
& "# AND #" & dtpDateOut.Text _
& "# AND Status='Confirmed' AND DateIN < #" & dtpDateOut.Text & "#"
cmd = New OleDbCommand(ct)
cmd.Connection = con
rdr = cmd.ExecuteReader()
If rdr.Read Then
MessageBox.Show("Selected Room is already booked", "Input Error", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
If Not rdr Is Nothing Then
End If
Exit Sub
End If


If the room is already booked from before the new in date until after the new out date then your BETWEEN clause will not detect them. I assume that someone can check in the same day as some else checks out. Test like this

Temp_Reservation.DateIn < new_out AND Temp_Reservation.DateOut > new_in
    ==> booking conflict!


 Reservation A:   26/12/2013 to 28/12/2013  
 Reservation B:   10/01/2014 to 15/01/2014  
 Reservation new: 01/01/2014 to 05/01/2014  

 A_in(26/12/2013) < new_out(05/01/2014) AND A_out(28/12/2013) > new_in(01/01/2014)
     ===> True AND False = False (which means: no conflicting record will be returned)

  B_in(10/01/2014) < new_out(05/01/2014) AND B_out(15/01/2014) > new_in(01/01/2014)
     ===> False AND True = False

How I found the logic: First I formulated the condition bookings have to fulfill in order to be OK.

OK:        A_in >= B_out OR A_out <= B_in

But you want to find overlapping bookings. Therefore I turned the condition around according to De Morgan's law: NOT (x OR y) = NOT(x) AND NOT(y)

CONFLICT:  A_in < B_out AND A_out > B_in