title: Need algorithm For room reservation
body: <p>I need a room reservation algorithm that room should not be book before its date once a room is booked should not be book on that similar date. I am using sql server and was using status active or booked but its not working.</p>
  
  <pre><code>public void res(DateTime? arrivalDate,DateTime? departureDate)
      {
  
          SqlConnection myConn = new SqlConnection();
          myConn.ConnectionString = (@"Server=DESKTOP-8JJO9NL\SQLEXPRESS;Database=HMS;Trusted_Connection=True");
          myConn.Open();
          SqlCommand com = new SqlCommand();
          com.Connection = myConn;
          string sql = "";
          sql = "select count(*) from Roomsdata where Room#=@roomnum and Status='Active and arrivaldate &gt;= @arrivalDate and departure &lt;= @departureDate'";
          com.CommandText = sql;
          com.Parameters.Clear();
          com.Parameters.AddWithValue("@roomnum", Roomnum);
          com.Parameters.AddWithValue("@arrivalDate", arrivalDate);
          com.Parameters.AddWithValue("@departureDate", departureDate);
  
          int numRecords = (int)com.ExecuteScalar();
  
          if (numRecords == 1)
          {
  
                  SqlConnection myConn2 = new SqlConnection();
                  myConn2.ConnectionString = (@"Server=DESKTOP-8JJO9NL\SQLEXPRESS;Database=HMS;Trusted_Connection=True");
                  myConn2.Open();
  
                  SqlCommand myCmd = new SqlCommand("update Roomsdata set [customername]=@customername ,[contact] =@contact  ,[arrivaldate]=@arrivaldate,[departure]=@departure  ,[Status]='Booked' ,[stayingdays]=DATEDIFF(day,arrivaldate,departure) ,[Total amount]=DATEDIFF(day,arrivaldate,departure)*1278 FROM [HMS].[dbo].[Roomsdata] where Room#=@roomnum1", myConn2);
                  myCmd.Parameters.AddWithValue("roomnum1", Roomnum);
                  myCmd.Parameters.AddWithValue("@customername", Costumername);
                  myCmd.Parameters.AddWithValue("@contact", Contact);
                  myCmd.Parameters.AddWithValue("@arrivaldate", Arrivaldate);
                  myCmd.Parameters.AddWithValue("@departure", Departuredate);
                  myCmd.ExecuteNonQuery();
                  myConn2.Close();
  
             }
          else
          {
              throw new Exception("Room is Already Booked");
          }
  </code></pre>