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

Question

I have a view called DailyVisitSummary. If I select FromDate and ToDate, it will generate the report called rpt.VisitSummaryReport depending upon the selected dates.

I am using Crystal Reports to generate reports. If I run my application (on my local machine), the report is working fine. But if I check my application after deploying to a local host using IIS Server, the report is not working correctly. It is showing an error. The error which is mentioned in the below image (second one)

My VisitorsViewModel;:

 public DateTime  ? FromDate { get; set; }
 public DateTime  ? ToDate { get; set; }

My view:

 <div class="form-group">
 @Html.LabelFor(model => model.FromDate)
 @Html.TextBoxFor(model => model.FromDate, new { @class = "form-control", type = "text" })
 </div>
 <div class="form-group">
 @Html.LabelFor(model => model.ToDate)
 @Html.TextBoxFor(model => model.ToDate, new { @class = "form-control", type = "text" })
 </div>

My jQuery code:

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js">    </script>
<link rel="stylesheet" href="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/smoothness/jquery-ui.css" />
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js"></script>

<script>
$("#FromDate").datepicker({
    dateFormat: "dd/mm/yy",
    changeMonth: true,
    changeYear: true,
});

$("#ToDate").datepicker({
    dateFormat: "dd/mm/yy",
    changeMonth: true,
    changeYear: true,
});</script>

My controller code:

public ActionResult VisitSummaryReport()
{
    return View();
}

[HttpPost]
public ActionResult GetDates(VisitorsViewModel VisitorsVM)
{
        var fromdt = Convert.ToDateTime(VisitorsVM.FromDate);
        var todt = Convert.ToDateTime(VisitorsVM.ToDate);
        SqlConnection con = new SqlConnection(@"Data Source=192.168.0.73\SQLEXPRESS,14330;Initial Catalog=WafeERP_NEW;User ID=sa;Password=wafewin;");

        DataTable dt = new DataTable();

        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("Select * from View_VisitorsForm where  VisitingDate >='" + fromdt  +"'and VisitingDate <= '" + todt  +"'", con);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
        }
        catch (Exception ex)
        {
            throw;
        }

        ReportDocument rpt = new ReportDocument();  
        rpt.Load(Server.MapPath("~/Areas/Sales/CrystalReports/rpt_VisitSummaryCrystalReport.rpt"));
        rpt.SetDataSource(dt);

        Stream stream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);

        return File(stream, "application/pdf");
}

I tried my level best to explain my issue. Any one understand my issue and help me to resolve this issue.

Thanks in advance.

Answer 1

You have a number of issues.

First, assuming you want the user to select both dates in the view, your properties should be DateTime (not nullable) and include a@Html.ValidationMessageFor() for each property Then in the POST method, start with

if(!ModelState.IsValid())
{
    return View(VisitorsVM);
}

But more importantly, your opening yourself to SQL injection attacks where a malicious user can gain access to your database, or simply delete it, and I recommend you read these answers and other articles on SQL Injection attacks. You need to modify you code to use parameterized queries

SqlConnection con = new SqlConnection(.....);
DataTable dt = new DataTable();
try
{
    SqlCommand command = new SqlCommand();
    command.CommandText = "Select * from View_VisitorsForm where VisitingDate >= @fromDate and VisitingDate <= @toDate";
    command.Parameters.Add("@fromDate", System.Data.SqlDbType.Date).Value = VisitorsVM.FromDate;
    command.Parameters.Add("@toDate", System.Data.SqlDbType.Date).Value = VisitorsVM.ToDate;
    command.Connection = con;
    // con.Open();
    SqlDataAdapter adp = new SqlDataAdapter(command); 
    adp.Fill(dt);
}

Note there is no need to open the connection - the .Fill() method will open and close it, but if you do use it, then you need to call con.Close(); after .Fill()

I would also recommend that this code be in a separate service rather than in a controller method

Side note: There is no need for new { type = "text" }) in you view. The TextBoxFor() method already adds the type="text" attribute so your just overwriting the existing value.

Answer 2

I think there is a problem while passing DateTime to SqlCommand. For this you can check it from here

I hope it would resolved your problem.

Answer 3

You need to convert string value to SQL datetime. For necessary date conversion pattern you can find it in here.

SqlCommand cmd = new SqlCommand("Select * from View_VisitorsForm where  VisitingDate >=CONVERT(datetime,'" + fromdt  +"', 3) and VisitingDate <= CONVERT(datetime,'" + todt  +"', 3)", con);

I choose 3 because you declare your datetime format as 'dd/mm/yyyy' in your client.