The conversion of a varchar data type to a datetime data type resulted in an out-of-range value in c#

Question

I am getting below message in my event log.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Please help my website is live using so many users and below is my button click code. may be the error is coming by this field TypeOfExpense.

protected void Button1_Click(object sender, EventArgs e)
  {
    if (txt_FromDate.Text != "" && txt_ToDate.Text != "")
    {

        DateTime Fromdate = DateTime.ParseExact(txt_FromDate.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);
        DateTime Todate = DateTime.ParseExact(txt_ToDate.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);
        Todate = Todate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
        ViewState["Fromdate"] = Fromdate;
        ViewState["Todate"] = Todate;

        GridView1.Visible = true;
        SqlConnection cn = new SqlConnection(strConn);
        cn.Open();
        string Query = ("select UserName,ProjectName,ClaimID,Type,DateOfExpense,TypeOfExpense,Amount,Narration,Bill from tbl_DetailedExpenceClaimsInfo  where DateOfExpense between '" + Fromdate + "' and '" + Todate + "' and  ClaimID  in(select ClaimID from tbl_DetailedExpenceClaimsSummaryInfo where status!='Incomplete' ) order by DateOfExpense");

        SqlCommand cmd = new SqlCommand(Query, cn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds.Tables[0].Rows.Count > 0)
        {
            GridView1.DataSource = ds;
            GridView1.DataBind();
            // Button1.Visible = true;
            lbl_ErrorMsg.Visible = false;
            Button2.Visible = true;
        }
        else
        {
            Button2.Visible = false;
            GridView1.Visible = false;
            lbl_ErrorMsg.Visible = true;
            lbl_ErrorMsg.Text = "There are no Claim with given Dates !";

        }
        cn.Close();
    }

    else
    {
        lbl_ErrorMsg.Visible = true;
        lbl_ErrorMsg.Text = " Please select the dates!";
    }
}

Thank you in advance for your support, thank you very much

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Jun 04 '18 at 12:56
Take your select string and run in directly in a query window and see what happens. — Wheels73, Jun 04 '18 at 13:12

score 1 · Accepted Answer · answered Jun 04 '18 at 14:19

You should use parameters to avoid this kind of errors as well as SQL injection attacks:

const string Query = "select UserName,ProjectName,ClaimID,Type,DateOfExpense,TypeOfExpense,Amount,Narration,Bill from tbl_DetailedExpenceClaimsInfo  where DateOfExpense between @FromDate and @ToDate and ClaimID  in(select ClaimID from tbl_DetailedExpenceClaimsSummaryInfo where status!='Incomplete' ) order by DateOfExpense";

...
SqlCommand cmd = new SqlCommand(Query, cn);
cmd.Parameters.AddWithValue("FromDate", Fromdate);
cmd.Parameters.AddWithValue("ToDate", Todate);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
...

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value in c#

1 Answers1