Error filter the table based on a date field

Question

i use this code to filter table by date field (in table) this my code :

public double Mounthcost()
    {
        DateTime today = DateTime.Now;
        PersianCalendar persianCalendar = new PersianCalendar();
        int yearPersian = persianCalendar.GetYear(today);
        int monthPersian = persianCalendar.GetMonth(today);
        DateTime firstDayOfMonth = persianCalendar.ToDateTime(yearPersian, monthPersian, 1, 0, 0, 0, 0);
        int daysInMonth = persianCalendar.GetDaysInMonth(yearPersian, monthPersian);
        DateTime lastDayOfMonth = firstDayOfMonth.AddDays(daysInMonth - 1);
        //try
        //{
            SqlDataReader DR = ExecuteReader(System.Data.CommandType.Text, "Select Sum(Price) From Tbl_Cost Where Dat Between" + firstDayOfMonth + "AND" + lastDayOfMonth);
            if (DR.Read())
            {
                Mouth_Cost = Double.Parse(DR[0].ToString());
            }
            return Mouth_Cost;
        //}
        //catch
        //{
        //    return 0;
        //}
    }

and this error :

An expression of non-boolean type specified in a context where a condition is expected, near 'between10'.

Try adding single quotes to the dates when building the query: `between '" + firstDayOfMonth + "' and '" + `. You should really be using a parameterized query, or at least explicitly converting the date/times to strings. Note that using parameters removes the problem of the database interpreting a string date in a different manner than .NET, i.e. is "10/5/15" October or May? Then there is the mystery of converting the sum from a numeric data type to a string and then parsing it to a double. — HABO, Oct 31 '15 at 17:29

score 2 · Answer 1 · answered Oct 31 '15 at 15:44

2

You are missing the spaces in your SQL query

SqlDataReader DR = ExecuteReader(System.Data.CommandType.Text, "Select Sum(Price) From Tbl_Cost Where Dat Between " + firstDayOfMonth + " AND " + lastDayOfMonth);

Notice the spaces after BETWEEN and AND.

answered Oct 31 '15 at 15:44

Praveen Paulose

5,741
1
15
19

@ghasemdeh Is the error the same? If not, can you post the new error message. – Praveen Paulose Oct 31 '15 at 15:59

score 1 · Accepted Answer · answered Oct 31 '15 at 18:06

thanks HABO :) use this :

SqlDataReader DR = ExecuteReader(System.Data.CommandType.Text, "Select Sum(Price) From Tbl_Cost Where Dat Between @F AND @L", new SqlParameter[]
            {
                new SqlParameter("@F", firstDayOfMonth),
                new SqlParameter("@L", lastDayOfMonth),
            }

be Right ...

Error filter the table based on a date field

2 Answers2