CODEWITHSUNDEEP
c#sql
CanP
CanP

Reputation: 11

Get price between 2 dates

I am trying to get the price between 2 dates based on the hotel tariffs.

enter image description here

var dates = new List<DateTime>();
for (var dt = checkIn; dt <= checkOut; dt = dt.AddDays(1))
{
    dates.Add(dt);
}

try
{
    cnn = new MySqlConnection(connString);
    cnn.ConnectionString = connString;
    cnn.Open();

    for (int i = 0; i < dates.Count - 1; i++)
    {
        lblResult.Text = "Pass";
        string da = dates[i].Year + "-" + dates[i].Month + "-" + dates[i].Day;
        DayOfWeek da1 = dates[i].DayOfWeek;
        if (dates[i].Day == 1 && dates[i].Month == 1 || dates[i].Day == 9 && dates[i].Month == 1 || dates[i].Day == 11 && dates[i].Month == 2 || dates[i].Day == 20 && dates[i].Month == 3 || dates[i].Day == 29 && dates[i].Month == 4 || dates[i].Day == 3 && dates[i].Month == 5 || dates[i].Day == 4 && dates[i].Month == 5 || dates[i].Day == 5 && dates[i].Month == 5 || dates[i].Day == 17 && dates[i].Month == 7 || dates[i].Day == 11 && dates[i].Month == 8 || dates[i].Day == 18 && dates[i].Month == 9 || dates[i].Day == 22 && dates[i].Month == 9 || dates[i].Day == 9 && dates[i].Month == 10 || dates[i].Day == 3 && dates[i].Month == 11 || dates[i].Day == 23 && dates[i].Month == 11 || dates[i].Day == 23 && dates[i].Month == 12)
        {
            sql = "SELECT GrossPrice FROM HotelPriceFIT WHERE HotelName='" + hotelName + "' AND RmType='" + rmType + "' AND Remarks LIKE '%Eve of Japan Holiday%' AND DATE(StartDate) >='" + da + "' AND DATE(EndDate) <='" + da + "'";
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Pass 2');", true);
        }
        else if (da1.ToString() == "Saturday")
        {
            sql = "SELECT GrossPrice FROM HotelPriceFIT WHERE HotelName='" + hotelName + "' AND RmType='" + rmType + "' AND Remarks LIKE '%Sat%' AND DATE(StartDate) >='" + da + "' AND DATE(EndDate) <='" + da + "'";
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Pass 3');", true);
        }
        else
        {
            **sql = "SELECT GrossPrice FROM HotelPriceFIT WHERE HotelName='" + hotelName + "' AND RmType='" + rmType + "' AND DATE(StartDate) >='" + da + "' AND DATE(EndDate) <='" + da + "'";**
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Pass 4');", true);
            lblPassHotel.Text = sql;
        }
        cmd = new MySqlCommand(sql, cnn);
        reader = cmd.ExecuteReader();
        if (reader.Read() == true)
        {
            amount = amount + reader.GetInt16(0);
        }
        reader.Close();
    }
    cnn.Close();
}

If i select

2018-01-18 as start date and 2018-01-19 as end date, it should return me 9000.

Appreciate any form of support.

Empty set

Upvotes: 0

Views: 97

Answers (1)

Sunil
Sunil

Reputation: 3424

Multiple problems here:

  1. You need to group this line using () brackets. Since you have not grouped you are confusing yourself. Either group all && or group all || so final condition is && only (or || only)

    if (dates[i].Day == 1 && dates[i].Month == 1 || dates[i].Day == 9 && dates[i].Month == 1 || dates[i].Day == 11 && dates[i].Month == 2 || dates[i].Day == 20 && dates[i].Month == 3 || dates[i].Day == 29 && dates[i].Month == 4 || dates[i].Day == 3 && dates[i].Month == 5 || dates[i].Day == 4 && dates[i].Month == 5 || dates[i].Day == 5 && dates[i].Month == 5 || dates[i].Day == 17 && dates[i].Month == 7 || dates[i].Day == 11 && dates[i].Month == 8 || dates[i].Day == 18 && dates[i].Month == 9 || dates[i].Day == 22 && dates[i].Month == 9 || dates[i].Day == 9 && dates[i].Month == 10 || dates[i].Day == 3 && dates[i].Month == 11 || dates[i].Day == 23 && dates[i].Month == 11 || dates[i].Day == 23 && dates[i].Month == 12)

This is the reason, that always last Else condition gets executed.

  1. This code is prone to SQL Injection attack. Parameterize your query.

Upvotes: 1

Related Questions