A Visual Basic Dilemma..

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). It must also assign the value of the cost that is placed in the combobox as the room.roomprice value.

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 *
FROM Room.Booking
WHERE Room.[Room Type] LIKE "Room_TypeTextbox.text"
AND Room.RoomID = Booking.RoomID

---

And this for checking the availability:

Code:

---

SELECT *
FROM Booking
WHERE [Date of Departure] < GetDate()

---

So what's the difficulty? You've already got a start on the SQL (sort of, as it isn't quite legit). Since it is likely that you will get more than one room, you would want a datareader (or possibly a datatable, especially if you intend to update information, or want to readily display the data in a grid, or elsewhere). You don't want to put that textbox.text in there unless you have already cleaned it, or unless the program is one where there is no chance of a SQL injection attack.

Now that I look at it, the SQL isn't all that great. A better solution would be to do a join on the two tables:

SELECT <list your fields here, rather than using *>
FROM Room INNER JOIN Booking ON Room.RoomID = Booking.RoomID
WHERE Room.[Room Type] LIKE '" & Room_TypeTextbox.Text & "'"
AND Booking.[Date of Departure] = ?

I don't like that date of departure thing, so I just put in a question mark. Surely you would want to put in some date rather than using the system date, and perhaps that's what you meant with GetDate(), but I wasn't clear. Also, the SQL for a date is different depending on the SQL flavor (which DB are you targeting, since Access requires dates wrapped in ##, while SQL Server doesn't).

Lastly, I can't even figure out what you mean by this sentence:

Quote:

---

It must also assign the value of the cost that is placed in the combobox as the room.roomprice value.

---

so that's clearly an issue.

Quote:

---

Originally Posted by Shaggy Hiker

So what's the difficulty? You've already got a start on the SQL (sort of, as it isn't quite legit). Since it is likely that you will get more than one room, you would want a datareader (or possibly a datatable, especially if you intend to update information, or want to readily display the data in a grid, or elsewhere). You don't want to put that textbox.text in there unless you have already cleaned it, or unless the program is one where there is no chance of a SQL injection attack.

Now that I look at it, the SQL isn't all that great. A better solution would be to do a join on the two tables:

SELECT <list your fields here, rather than using *>
FROM Room INNER JOIN Booking ON Room.RoomID = Booking.RoomID
WHERE Room.[Room Type] LIKE '" & Room_TypeTextbox.Text & "'"
AND Booking.[Date of Departure] = ?

I don't like that date of departure thing, so I just put in a question mark. Surely you would want to put in some date rather than using the system date, and perhaps that's what you meant with GetDate(), but I wasn't clear. Also, the SQL for a date is different depending on the SQL flavor (which DB are you targeting, since Access requires dates wrapped in ##, while SQL Server doesn't).

Lastly, I can't even figure out what you mean by this sentence:


so that's clearly an issue.

---

Yeah, the chances are likely that the results will return more than one room. I would want it to pick one of the rooms if the result returns multiple rooms. There is no risk of an SQL Injection.

I want some way of it checking that the room is not being used or has been booked and the only way I could think of that was by comparing the date of departure to being < system.date.

With the bit you are unsure about.
Nevermind about that, ive changed it so now the price is fixed and held in the table: Room.

This form of data manipulation is all new to me. I was also unsure of what sort of code I would be placing on my button click event.

Also another little question, How do I quote the content of that field?
I.E. messagebox.show("Your booking is going to cost: " + Room.RoomPrice + " Per Night.")

..Any form of help would be hugely appreciated!