Please help me.. I have a problem.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help me.. I have a problem.

Author

Topic

Keep_Coding
Starting Member

2 Posts

Posted - 2010-06-08 : 14:08:33

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):

[CODE]Booking:
BookingID (Primary Key)
CustomerID (Foreign Key, Table:Customer)
Date of Arrival (Date)
Date of Departure (Date)
RoomID (Foreign Key,Table:Room)[/CODE]

[CODE]
Room:
RoomID (Primary Key)
RoomType (Text)
RoomPrice (Currency)
Floor (Number)[/CODE]

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:
[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()
[/CODE]
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.

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 14:25:48

"select a single matching record"


SELECT TOP 1 Col1, Col2, ...
FROM ...
WHERE ...
ORDER BY SortCol1, SortCol2

You don't have to use ORDER BY but if you don't you'll get a random room, which might be annoying if someone presses refresh and everytime gets something different. I always put whatever Order By columns I want and make sure the PK fields are included, if not add them to the end, so that there is always a tie-break that is repeatable.

Keep_Coding
Starting Member

2 Posts

Posted - 2010-06-08 : 14:37:04

Yeah I've tried that but it doesnt seem to work :( It doesnt like the & part. Any ideas?

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 17:53:06

Use "+" to concatenate strings in SQL, not "&"

Use GetDate() for current Date & Time in SQL, not Now()

Use single quoted to delimit strings in SQL, not double quotes. (Your quotes are not balanced, and you don't appear to be concatenating anything. If you are using LIKE then perhaps you are trying to pre/append wildcards?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-09 : 01:47:59

quote:
Originally posted by Keep_Coding

Code:
[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()
[/CODE]

Suggestion: Google SQL Injection. You have a major vulnerability here.

Are your tables in Access or in SQL Server? & is the Access string concatenation operator, + is the SQL Server one. I noticed that you mentioned Access in the post, and that "Room_TypeTextbox.Text" is classic MS Access front-end coding, as is the use of Now() to get datetime.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-06-09 : 02:45:33

Ah ... didn't think of that ... could just be that the Quotes are not balanced then.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources