I have created a hotel booking system programme. However, I am puzzled as to how when the booking button is clicked it can search a database and find the best suited room (RoomType) and make sure that it is available (Date of Departure < system.date).
This data is held in two tables (Access):
Booking: BookingID (Primary Key) CustomerID (Foreign Key, Table:Customer) Date of Arrival (Date) Date of Departure (Date) RoomID (Foreign Key,Table:Room)
Room: RoomID (Primary Key) RoomType (Text) RoomPrice (Currency) Floor (Number)
This has all got to be done within a single button click. Any ideas as to how I can place this into my vb code?
I have a rough query for picking a room that is best suited:
SELECT roomID FROM Room INNER JOIN Booking ON Room.RoomID = Booking.RoomID WHERE Room.[Room Type] LIKE '" & Room_TypeTextbox.Text & "'" AND Booking.[Date of Departure] < Now()
I have been trying for a while to get it to select a single matching record as there is a high chance that this query will return multiple records but im unsure on how to deal with that.
Any help would be hugely appreciated.