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
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
CREATE TABLE [dbo].[Reservation] (
[Αριθμός Κράτησης] INT IDENTITY (1, 1) NOT NULL,
[Δωμάτιο] INT NULL, <
[Α.Φ.Μ. Πελάτη] NVARCHAR (20) NULL,
[Α.Φ.Μ. Πρακτορείου] NVARCHAR (25) NULL,
[Ημερομηνία άφιξης] DATE NULL, <
[Ημερομηνία αναχώρησης] DATE NULL, <
[Ενήλικες] INT NULL,
[Παιδιά] INT NULL,
[Πώς μας βρήκε] NVARCHAR (50) NULL,
[Ποσό] MONEY NULL,
[Προκαταβολή] MONEY NULL,
[Ημερομηνία προκαταβολής] DATE NULL,
[Υπόλοιπο] MONEY NULL,
[Τράπεζα] NVARCHAR (30) NULL,
[Τρόπος πληρωμής] NVARCHAR (30) NULL,
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
CREATE TABLE [dbo].[Room] (
[Αριθμός δωματίου] INT NOT NULL, <
[Όροφος] INT NULL,
[Τύπος Δωματίου] NVARCHAR (30) NULL,
[Τηλεόραση] NVARCHAR (10) NULL,
[Κλιματισμός] NVARCHAR (10) NULL,
[Mini Bar] NVARCHAR (10) NULL,
[Θέα] NVARCHAR (10) NULL,
CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ([Αριθμός δωματίου] ASC),
CONSTRAINT [FK_Room_ToRoomType] FOREIGN KEY ([Τύπος Δωματίου]) REFERENCES [dbo].[RoomType] ([Περιγραφή])
);