I have a problem, can you fix it?

[Keep]

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:

Code:

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.

[Keep]

Can anyone help me out? Could really do with any form of guidance or help on this.

[finito]

For that i need to know how you define a "room that is best suited"

with those values.

Use best method available to do this as this what your logic will depend on.

[Keep]

For that i need to know how you define a "room that is best suited"

with those values.

Use best method available to do this as this what your logic will depend on.

Well, there is a combo box with values:

Single
Double
Family
Luxury
Honeymoon

All different room types. The total number of rooms are all stored in table room.

[Keep]

BUMP! Can anyone help me?

[finito]

Before I go any further a question,

So just want to get the first returned result if so just do Execute Scaler() instead of Execute Reader()
-----------------------------------------
Well ok So you choose from

Single
Double
Family
Luxury
Honeymoon

Then you get all the available rooms that fits that criteria which should be multiple records. Now to narrow the results You could also give it preferred floor.

What I usually see with hotel reservation systems is They have a Diagram of the building and all the Relative rooms light up making the decision easier.

Cause this is where human mind comes to play. Computer can still not take over such things.

[Keep]

The flooring is based on room type with single rooms being first floor, doubles being second etc.. SO that concept cannot be applied in this case.

The basic idea of what I want the query to do is:
Pick a single record that matches the room type in the combo box and check that it is available.

[ndeniche]

SELECT TOP 1 Columns /*The columns you want to select*/
FROM Table /*The table you're looking in*/
WHERE args = TRUE /*The arguments you intend to evaluate*/

[ndeniche]

Using TOP 1 in a query selects just the first record of the query. Changing the number following TOP will show more records

[Keep]

Alright cool, thanks. Anymore help on how I can implement the date part?

[ndeniche]

You can use the operator MAX() to obtain the maximum value in a column:

SELECT Record_id, MAX(Date)
FROM COLUMN
HAVING Date < Now()
GROUP BY Record_id

[Keep]

Aright ok cool, However.. For some reason the SQL is not working. Is there anything wrong with my SQL command above? It seems to not be finding any results at all when my room table is populated

[finito]

What did you type?

Exact word for word?

You were supposed to change From Column to what your table is called

[Keep]

SELECT TOP 1 room.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())

In short, all I want this to do is pick the best suited room and place the ID of that room in the booking table.

[finito]

Try running this statement in your SQL browser.

[Keep]

It works fine as in the scripting is fine. Although there is something wrong the with join as when I use that whole script there are no results shown.

[ndeniche]

Remove the parentheses, and leave it

SELECT TOP 1 room.RoomID
FROM room 
INNER JOIN booking 
ON 
room.RoomID = booking.RoomID AND room.[Room Type] LIKE '"& Room_Typetextbox.text &"' AND booking.[Date of Departure] < NOW()

Try the verification statement in the INNER JOIN condition, so it resolves for it on joining the tables, thus showing the results you need