Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Question Unanswered: VB.NET SQL Problem - Help please!

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    That is definitely not a valid MySQL Syntax (actually the & thing is not valid in any SQL I know)

    This looks like SQL Server, you should post the question there.

    And post the real SQL Statement not your VB code (print the real SQL statement and most probably you'll se the error yourself)

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    I dont understand what you mean? I have not posted any visual basic coding at all.. This is the SQL command that I am trying to create an place in VB.NET

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Take a look at LIMIT to specify that you only want a single row returned. Something like: LIMIT 0,1. Also, I would move "AND Booking.[Date of Departure] < Now()" into the ON clause just for clarity sake.
    Dave

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Keep_Coding View Post
    I dont understand what you mean? I have not posted any visual basic coding at all
    Sure you have: LIKE '" & Room_TypeTextbox.Text & "'" is not valid SQL, it's VB (or some other programming language)

    and AND Booking.[Date of Departure] < Now() is not valid syntax for MySQL, it's the wrong forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •