Search code examples
c#asp.net-mvc-5crystal-reports

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

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)

enter image description here

enter image description here

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.

Solution

  • 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.