Problem in avoiding overlapped bookings in my booking system application

If a booking is present in database on 12/27/2011 at 5:00 pm for 2 hours and i try to make a new booking on 12/27/2011 between 5 to 7 pm then my code generates an error message. Even if i try to book at 4:00 pm for 2 hours it generates an error message as the 2nd hour will be overlapped here wit开发者_Go百科h already made booking from 5:00 to 7:00.

Now here comes the problem part. When day changes it doesn't generate error message i.e. IF a booking is there on 12/27/2011 at 11:00 pm for 3 hours then it should not allow new booking till 12/28/2011 2:00 am but when i try to book 12/28/2011 at 1:00 am it saves it in the database and doesn't generate an error message. I want an error message generated in such a case.

I am using two separate fields in database one for time and one for date. Both of them have DateTime datatype.

newtime refers to time on which i'm trying to make new booking

addednewtime refers to time after adding the duration to time on which i'm trying to make new booking

addeddbtime contains just time value (after adding duration for booking in database) extracted from datetime field stored in database

newdate refers to the next date as the day changes at 12:00 am so if database booking is at 11:00 pm on 12/12/2011 then new date will have 12/13/2011 in it

Problem lies in the last part of the If condition which checks overlapped bookings when a booking spans over two days

Here is my code:

Dim newtime, addednewtime, addeddbtime, changetime, newdate As DateTime 'defines variables
addeddbtime = dbonlytime.AddHours(dbdur)
newtime = TxtBookTime.Text
addednewtime = newtime.AddHours(TxtBookDur.Text)



changetime = "12:00:00 AM"
newdate = dbdate.AddDays(1)

If TxtBookDate.Text = dbdate And TxtPoolNo.Text = dbpoolno And TxtCrtNo.Text = dbcrtno And TxtTblNo.Text = dbtblno And CmboGame.SelectedItem = dbgame And ((newtime > dbonlytime And newtime < addeddbtime) Or (addednewtime > dbonlytime And addednewtime < addeddbtime) Or (newtime < dbonlytime And addeddbtime > changetime And addednewtime < dbonlytime And addednewtime <= addeddbtime And TxtBookDate.Text = newdate)) Then
MessageBox.Show("The date and time you have entered has already been booked " & vbCrLf & " Try Again!", "Bookings Overlapped", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Exit Sub

End If

---

Want you want to do is have a routine which checks for overlappings between the current booking and existing ones. You should have a method for this, with the following input values:

1. Current booking dates
2. List of existing bookings

Let's say you have a class called Booking which has begin and end DateTimes (no need to have separate fields for date and time, a DateTime object contains both).

Public Class Booking

    Public Sub New(ByVal beginDate As DateTime, ByVal endDate As DateTime)
        Me.BeginDate = beginDate
        Me.EndDate = endDate
    End Sub

    Public Property BeginDate As Date

    Public Property EndDate As Date

    'Other booking properties here

End Class

You could have a routine like this which checks if there is an overlapping with an existing booking:

Private Function BookingOverlapping(ByVal booking As Booking, ByVal existingBookings As IEnumerable(Of Booking)) As Boolean
    For Each existingBooking In existingBookings
        If booking.BeginDate < existingBooking.EndDate AndAlso booking.EndDate > existingBooking.BeginDate Then
            Return True
        End If
    Next
    Return False
End Function

Then you would use the method like this:

' Figure out begin and end DateTimes from user input, 
' then create a booking object.
Dim currentBooking As New Booking(beginDate, endDate)

' The GetExistingBookings method would retrieve bookings from the database 
' and add new Booking objects to a list.
Dim existingBookings As List(Of Booking) = GetExistingBookings()

If BookingOverlapping(currentBooking, existingBookings)
    MessageBox.Show("The date and time you have entered has already been booked " & vbCrLf & " Try Again!", "Bookings Overlapped", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End If