Hotel booking system - room availability (search)

Question

0.00/5 (No votes)

See more:

I am creating a hotel booking system. However since I am not an expert, it doesn't do what I want it to be able to do. At the moment, this code does not run and I am not quite sure where it's going wrong. I want it to run like a normal Hotel booking system where for example, if a room was booked 21/02/17 to 23/02/17 then the button would remain red in that date period. However it would be able to still be able to be booked on the 23/02/17 (because of checking out). As I said I'm quite new so there may be something that exists already but just haven't quite understood it.

Also I was thinking should my str1 be changed to something such as

Select * from BookingInformation where theDate between Date In and Date Out and Room Number = 'Something'

Private Sub ReadRecords()
Dim btn As Button = Nothing
MyConn = New OleDbConnection
MyConn.ConnectionString = connString
MyConn.Open()

str1 = ("SELECT * FROM [BookingInformation] WHERE [Date Out] = '" & dtpDateOut.Value.Date & "'") 'When searching this searches for the data that you are looking for'

Dim cmd1 As OleDbCommand = New OleDbCommand(str1, MyConn)
dr = cmd1.ExecuteReader
While dr.Read()
    strRoomBooking = dr("Room Booking").ToString
    strDateIn = dr("Date In").ToString
    strDateOut = dr("Date Out").ToString


    If strDateOut > DateTime.Today Then
        If strRoomBooking = btn.Text Then
            btn.BackColor = Color.Red
        End If
    End If
End While
MyConn.Close()
End Sub

Private Sub Room_Booking_Load(sender As Object, e As EventArgs) Handles MyBase.Load
ReadRecords()

End If

What I have tried:

Ii have tried the code above and also had a look into it and think

<pre>Select * from BookingInformation where theDate between Date In and Date Out and Room Number = 'Something'

may work

Posted 27-May-17 5:07am

Member 13209355

Updated 27-May-17 5:48am

Add a Solution

Comments

Richard MacCutchan 27-May-17 11:39am

It does depend on your criteria. If all room types are the same then you just need to select any where the requested dates do not intersect with the booked dates. So if RequestedStartDate is greater than or equal to DateBookedStart and less than DateBookedEnd, the booking will fail. In other words RequestedEndDtate needs to be before DateBookedStart and RequestedStartDate needs to be after DateBookedEnd for every eligible room. Write a few sample settings on paper to see how it should work.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-05-27T05:48:00

Start by looking at exactly what you are storing as the Date Out - equality checks require exact values, so if you are not setting that as an exact time of day down to the microsecond - and I'd suggest "check out time" which hotels tend to have - then the comparison won't match.

But don't do it like that. I know it's only homework, but it's important stuff: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

What I'd suggest is stopping and thinking about what you are trying to do: when you book a hotel room, when do you rent it for? Effectively, it's booked from "first available time" on start day to "checkout" on end day. And most hotels will have an hour or so gap between the "check out" and "available" time to give the staff a chance to check and clean the room. So make the DB values reflect the actual hotel, and when a booking comes in, check if the available time on the first day to the checkout time on the last day overlaps any booked period. If it doesn't, the user can book it.