ASP.Net SQL Query Input Controls

Question

Schema

    CREATE TABLE "Rooms"(

    RoomNo int NOT NULL,
    RoomType nvarchar(20) NULL,
    PricePerNight money NULL,
    MaximumOccupancy int NULL,
    NoOfBeds int NULL,
    NoOfBathrooms int NULL,
    Entertainment bit NULL,
    RoomService bit NULL,
    Gym bit NULL,

    CONSTRAINT PK_Rooms PRIMARY KEY(RoomNo)
)

CREATE TABLE "Reservation"(

    ReservationID int IDENTITY (1,1) NOT NULL,
    CustomerID int NOT NULL,
    RoomNo int NOT NULL,
    CheckInDate date NOT NULL,
    CheckOutDate date NOT NULL,
    NoOfDays int NOT NULL,

    CONSTRAINT PK_Reservation PRIMARY KEY(ReservationID),

    CONSTRAINT FK_Reservation_Customers_CustID FOREIGN KEY(CustomerID)  
        REFERENCES dbo.Customers(CustomerID),

    CONSTRAINT FK_Reservation_Rooms_RoomNo FOREIGN KEY(RoomNo)
        REFERENCES dbo.Rooms(RoomNo)
)

I had in mind to create a query for Sql Server that when the user would enter in 2 dates it would query the Room and Reservation table to bring back all the rooms which are currently available ( available: meaning there is no reservation entry which has/or is in between the dates the user inputs.

Sql-Server-Code

 SELECT dbo.Rooms.RoomNo
 FROM dbo.Rooms JOIN dbo.Reservation
 ON (dbo.Rooms.RoomNo = dbo.Reservation.RoomNo)
 WHERE '2012-02-01' NOT BETWEEN dbo.Reservation.CheckInDate AND dbo.Reservation.CheckOutDate
 AND '2012-02-05' NOT BETWEEN dbo.Reservation.CheckInDate AND dbo.Reservation.CheckOutDate;

^ i tested this and in the test i carried it, it yielded the results i wanted. So i need to now test this in my ASP.Net website. On my page i have a search box where the users enters in 2 dates, checkin and checkout. This again will return all the rooms which are available so i thought.

ASP.Net Code Search Box

<div id="searchContainer">
    <form id="searchForm">
    <ul id="searchBox">
        <li>Search</li>
        <hr />
        <li>Check In Date</li>
        <li class="CiMenu">
            <asp:TextBox ID="txtBoxCheckIn" runat="server" />
            <ajaxtoolkit:CalendarExtender ID="CalendarExtender2" runat="server" TargetControlID="txtBoxCheckIn"
                Format="yyyy-MM-dd" />
        </li>
        <li>Check Out Date</li>
        <li class="CoMenu">
            <asp:TextBox ID="txtBoxCheckOut" runat="server" />
            <ajaxtoolkit:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="txtBoxCheckOut"
                Format="yyyy-MM-dd" />
        </li>
        <li>
            <asp:Button ID="searchButton" runat="server" Text="Search" 
                PostBackUrl="~/Rooms.aspx" />
        </li>
    </ul>
    </form>
</div>

ASP.Net Code For my query.

    <asp:SqlDataSource ID="RoomDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:HotelProjectConnectionString %>"
        SelectCommand="SELECT * FROM Rooms JOIN Reservation
        ON (Rooms.RoomNo = Reservation.RoomNo)
        WHERE (@CheckIn NOT BETWEEN Reservation.CheckInDate AND Reservation.CheckOutDate)
        AND ( @CheckOut NOT BETWEEN Reservation.CheckInDate AND Reservation.CheckOutDate)">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtBoxCheckIn" Name="CheckIn" PropertyName="Text"
                Type="DateTime" />
            <asp:ControlParameter ControlID="txtBoxCheckOut" Name="CheckOut" PropertyName="text"
                Type="DateTime" />
        </SelectParameters>
    </asp:SqlDataSource>

I created 9 different rooms room 101-109 and 1 reservation entry.

INSERT Reservation(CustomerID,RoomNo,CheckInDate,CheckOutDate,NoOfDays)
    VALUES(1,101,'2012-01-01','2012-01-30', DATEDIFF(day,'2012-01-01','2012-01-30'))

So now if i do a search on my website where the "checkindate" is '2012-09-01' and "checkoutdate" is '2012-09-05'. It returns room101 row which it should do as theres no reservation entry with those dates for that room, however it doesn't return all the other 8 rooms.
If i do a search where the checkindate is '2012-01-01' and checkoutdate is '2012-01-30' < there is already a reservation for this for room101. Meaning it should bring back all the room rows except 101. However it doesn't return anything.

Could someone please help me with this, can you see where i am going wrong.

UPDATE:

This new Query is what i'm looking for cept it has a minor problem

SELECT *
FROM dbo.Rooms
WHERE NOT EXISTS
  (select *
    from dbo.Reservation
    where dbo.rooms.RoomNo = dbo.Reservation.RoomNo
    AND '2012-01-01' BETWEEN dbo.Reservation.CheckInDate AND dbo.Reservation.CheckOutDate
    AND '2012-01-30' BETWEEN dbo.Reservation.CheckInDate AND dbo.Reservation.CheckOutDate
    );

This will show results between room102-109 which is correct however if we change the 2012-01-30 to 2012-01-32 it adds room101 to the result which it shouldn't. Any solution?

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

Your date criteria is wrong. To test for overlapping ranges you should use

where '2012-01-01' <= dbo.Reservation.CheckOutDate
  and '2012-01-30' >= dbo.Reservation.CheckInDate

It says that if beginning of range1 is before end of range two, and end of range1 is after beginning of range2, there is overlap.

Left join is not working for you because conditions put on right side table must be specified in join itself:

SELECT dbo.Rooms.RoomNo
  FROM dbo.Rooms 
  LEFT JOIN dbo.Reservation
    ON dbo.Rooms.RoomNo = dbo.Reservation.RoomNo
   AND '2012-01-01' <= dbo.Reservation.CheckOutDate
   AND '2012-01-30' >= dbo.Reservation.CheckInDate;

Otherwise it is treated as inner join by following execution order - join is evaluated and includes null records from right side table only to eliminate those records in where clause.

EDIT: The final filter is missing. The query lists all rooms and for those reserved lists a reservation. If you need to select rooms without reservations add

 WHERE Reservation.RoomNo is null

And if you want all rooms reserved in a period use inner join instead of left outer join.

edited May 23 '17 at 11:56

Community

1
1

answered Sep 03 '12 at 21:45

Nikola Markovinović

18,963
5
46
51

Beat me to it. It's classic this one. – Tony Hopkinson Sep 03 '12 at 21:50
Yep looks like Nikola is right :) just you need the opposite condition because you need rooms that dont overlap.. – Greg Oks Sep 04 '12 at 07:11
This is not working, the above dates will return room101, though it shouldint as theres a reservation for room101 with those dates. – user1638362 Sep 04 '12 at 07:18
1

SELECT * FROM dbo.Rooms WHERE NOT EXISTS (select * from dbo.Reservation where dbo.rooms.RoomNo = dbo.Reservation.RoomNo AND '2012-01-01' <= dbo.Reservation.CheckOutDate AND '2012-01-30' >= dbo.Reservation.CheckInDate ); this looks like it works. – user1638362 Sep 04 '12 at 07:20
1

@user1638362 Could you please post some sample data? I suggest using [Sql Fiddle](http://sqlfiddle.com/), but a list of inserts would be great too. – Nikola Markovinović Sep 04 '12 at 07:27

score 0 · Answer 2 · answered Sep 03 '12 at 16:42

0

I think your problem is that you are using join instead of left outer join. There is only one item in th Reservations table with room 101 so if you do join on room and reservation it will only return 1 row with room 101 (try it..) instead you want all the rooms from the rooms table to join the rooms from reservations table, so your query needs to look like this:

Sql-Server-Code

SELECT dbo.Rooms.RoomNo FROM dbo.Rooms LEFT OUTER JOIN dbo.Reservation ON (dbo.Rooms.RoomNo = dbo.Reservation.RoomNo) WHERE '2012-02-01' NOT BETWEEN dbo.Reservation.CheckInDate AND dbo.Reservation.CheckOutDate AND '2012-02-05' NOT BETWEEN dbo.Reservation.CheckInDate AND dbo.Reservation.CheckOutDate;

answered Sep 03 '12 at 16:42

Greg Oks

2,700
4
35
41

I tried doing left outer join in asp.net and im still getting the same problem. I just want to clarify that the sql-server code i'm using works perfectly in sql server when i'm testing it out there. but on converting it/getting it to work with asp.net its having trouble. Oh NO im wrong its not working in sql server either!! – user1638362 Sep 03 '12 at 17:49
changed my sql code to include left outer join and it still doesent work. i think its safe to say its deffi something to do with my sql query now. Left outer join should work, but its still only returning room 101 – user1638362 Sep 03 '12 at 17:53
Check my original post i added something. – user1638362 Sep 03 '12 at 20:14

ASP.Net SQL Query Input Controls

2 Answers2