Check Availability

[reemeya]

hi

I'm doing online reservation hotel using vb.net with sql server2000.i have some problem with checking room availability i tried lot of sql query but doesn't get what i want.
the customer choose the arrive date and the enddat from the calendar after that it should disply the available room in that dates

my tables are:
booking table:
booking id
arrivedate
enddate
guestid
totalpaidamount

link_bookingroom:
roomid
bookingid
guestid

room table
roomid
roomtype
description
nightly rate
maximum pepole available

the sql query that i used is
SELECT distinct room.roomtype, room.description, room.[maximum people allowed],room.[nightly rate] FROM room INNER JOIN link_bookingroom ON room.roomid <> link_bookingroom.roomid INNER JOIN booking ON link_bookingroom.bookingid = booking.bookingid WHERE ('" + txtStartDate.Text + "' NOT BETWEEN booking.arrivedate AND booking.enddate) AND ('" + txtEndDate.Text + "' NOT BETWEEN booking.arrivedate AND booking.enddate) or booking.bookingid = null"

please help me with this problem

[binoj_daniel]

Can you post the results of this query?

[QVeen72]

Hi,

Add a Bracket for date condition.. check this:

SELECT distinct room.roomtype, room.description, room.[maximum people allowed],room.[nightly rate] FROM room INNER JOIN link_bookingroom ON room.roomid <> link_bookingroom.roomid INNER JOIN booking ON link_bookingroom.bookingid = booking.bookingid WHERE [B]([/B]('" + txtStartDate.Text + "' NOT BETWEEN booking.arrivedate AND booking.enddate) AND ('" + txtEndDate.Text + "' NOT BETWEEN booking.arrivedate AND booking.enddate)[B])[/B] or booking.bookingid = null"

Regards
Veena

[reemeya]

thanks alot

it's work