Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
3.50/5 (2 votes)
See more:
i have made a hotel room booking C# desktop application with microsoft localDB v11 and visual studio 2013.

i have a combo box that shows all the rooms that exist in my room table.

and this is my combobox fill function

C#
//fill room combobox with db values
        void fillComboBoxRoom()
        {
            SqlDataReader datareader = null;
            mycon.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\George\Documents\Visual Studio 2013\Projects\HotelManagement\HotelManagement\HotelDB.mdf;Integrated Security=True";
            cmd.Connection = mycon;
            cmd.CommandText = "select * from Room";


            try
            {
                mycon.Open();
                datareader = cmd.ExecuteReader();
                while (datareader.Read())
                {
                    roomtext.Items.Add((int)datareader["Αριθμός δωματίου"]);

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            mycon.Close();

        }


and two datetimepickers (checkindate,checkoutdate)

i need the correct sql query to insert to my combobox function so the rooms combobox show only the rooms that are available between the dates that are picked in the two datetimepickers.


here is my reservations table

SQL
CREATE TABLE [dbo].[Reservation] (
    [Αριθμός Κράτησης]        INT           IDENTITY (1, 1) NOT NULL,
    [Δωμάτιο]                 INT           NULL,  <--this is the room number
    [Α.Φ.Μ. Πελάτη]           NVARCHAR (20) NULL,  --client id
    [Α.Φ.Μ. Πρακτορείου]      NVARCHAR (25) NULL,  --agency id
    [Ημερομηνία άφιξης]       DATE          NULL,  <--this is the checkin date
    [Ημερομηνία αναχώρησης]   DATE          NULL,  <--this is the checkout date
    [Ενήλικες]                INT           NULL,  --grown ups
    [Παιδιά]                  INT           NULL,  --kids
    [Πώς μας βρήκε]           NVARCHAR (50) NULL,  --how did he find us
    [Ποσό]                    MONEY         NULL,  -- amount
    [Προκαταβολή]             MONEY         NULL,  --advance
    [Ημερομηνία προκαταβολής] DATE          NULL,  --advance date
    [Υπόλοιπο]                MONEY         NULL,  --balance
    [Τράπεζα]                 NVARCHAR (30) NULL,  --bank
    [Τρόπος πληρωμής]         NVARCHAR (30) NULL,  --pay way
    PRIMARY KEY CLUSTERED ([Αριθμός Κράτησης] ASC),
    CONSTRAINT [FK_Reservation_ToRooms] FOREIGN KEY ([Δωμάτιο]) REFERENCES [dbo].[Room] ([Αριθμός δωματίου]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_Reservation_ToAgencys] FOREIGN KEY ([Α.Φ.Μ. Πρακτορείου]) REFERENCES [dbo].[Agency] ([Α.Φ.Μ.]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_Reservation_ToCustomers] FOREIGN KEY ([Α.Φ.Μ. Πελάτη]) REFERENCES [dbo].[Customer] ([Α.Φ.Μ]) ON DELETE CASCADE ON UPDATE CASCADE);




and my room table

SQL
CREATE TABLE [dbo].[Room] (
    [Αριθμός δωματίου] INT           NOT NULL,    <----room number
    [Όροφος]           INT           NULL,  --floor
    [Τύπος Δωματίου]   NVARCHAR (30) NULL,  --room type
    [Τηλεόραση]        NVARCHAR (10) NULL,  -- tv
    [Κλιματισμός]      NVARCHAR (10) NULL,  --air condition
    [Mini Bar]         NVARCHAR (10) NULL,  --minibar
    [Θέα]              NVARCHAR (10) NULL,  --view
    CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ([Αριθμός δωματίου] ASC),
    CONSTRAINT [FK_Room_ToRoomType] FOREIGN KEY ([Τύπος Δωματίου]) REFERENCES [dbo].[RoomType] ([Περιγραφή])
);
Posted
Updated 21-Oct-14 12:14pm
v5
Comments
PIEBALDconsult 21-Oct-14 16:51pm    
"Hello Every1," You are unlikely to graduate and get a good job if you write like that.

1 solution

0) Do not use concatenation to form an SQL statement; use a parameterized query.
1) Do not put Data Access Code directly in the Form code; create a layered application with a Data Access Layer.


You shouldn't need to check that -- you should get a list of rooms that are available for the specified period and only present those. Can you imagine a busy 1000-room hotel and the users have to keep trying each room until they find one that's available?


I'm also not a fan of deletes, especially casaded deletes -- have a field to indicate state/status is generally better.
 
Share this answer
 
v3
Comments
PIEBALDconsult 21-Oct-14 17:15pm    
I expect the user has already entered the dates and other attributes (number/type of beds, smoking/non-smoking, etc.) required -- rather than "select * from Room" you need to apply those attributes and also JOIN to the reservation table to get only those rooms that match the criteria.
The query is a little tricky.


Also see this Question from earlier this year:
http://www.codeproject.com/Answers/764006/Selection-of-all-data-between-two-dates#answer1
You will want to get the Rooms that _don't_ have reservations in the date range.
BillWoodruff 21-Oct-14 22:03pm    
+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900