I am working on a web application that will insert data into a row in a table when clicked. When I try to insert the date into the database I get the following error:
System.Data.SqlClient.SqlException was unhandled by user code
HResult=-2146232060
Message=Conversion failed when converting date and/or time from character string.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
The following fields are datetime columns in the Sql Server tabl,e ProfileDate, CheckStartDatetime, LastCheckDate and CheckRecord. However in the model they are string. I doubt this is the issue since when I display information from the sql table the dates are displayed.
Model:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace AlertNotificationWeb.Models
{
public class Task
{
public int ID { get; set; }
public string FunctionName { get; set; }
public string FunctionDesc { get; set; }
public string CheckPeriod { get; set; }
public string Profiledate { get; set; }/*Represented as datetime in sql server table */
public int PeriodDay { get; set; }
public string AlertStatus { get; set; }
public string Comment { get; set; }
public String LastCheckDate { get; set; } /*Represented as datetime in sql server table */
public String CheckStartDatetime { get; set; } /*Represented as datetime in sql server table */
public String CheckRecord { get; set; }/*Represented as datetime in sql server table */
}
}
Controller:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using AlertNotificationWeb.Models;
using System.Data;
using System.Configuration;
namespace AlertNotificationWeb.Controllers
{
public class TaskController : Controller
{
//
// GET: /Task/
string connectionstring = @"data source=MSSQL4MIS; initial catalog= datamart2; User ID= support_user; Password= support_user_pass";
public ActionResult Index()
{
List<Task> tasks = new List<Task>();
string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
string query = "SELECT [ID],[GroupSubsidiary],[FunctionName],[FunctionDesc],[CheckPeriod],[Profiledate],[AlertStatus], [CheckStartDatetime], [LastCheckDate] ,[Comment], [CheckRecord] FROM [datamart2].[dbo].[AS2_AlertsResults]";
using (SqlConnection sqlcon = new SqlConnection(constr))
{
using (SqlCommand sqlcmd = new SqlCommand(query))
{
sqlcon.Open();
sqlcmd.Connection = sqlcon;
using (SqlDataReader sdr = sqlcmd.ExecuteReader())
{
while (sdr.Read())
{
tasks.Add(new Task
{
ID = Convert.ToInt32(sdr["ID"]),
GroupSubsidiary = Convert.ToString(sdr["GroupSubsidiary"]),
FunctionName = Convert.ToString(sdr["FunctionName"]),
FunctionDesc = Convert.ToString(sdr["FunctionDesc"]),
CheckPeriod = Convert.ToString(sdr["CheckPeriod"]),
Profiledate = Convert.ToString(sdr["Profiledate"]),
AlertStatus = Convert.ToString(sdr["AlertStatus"]),
CheckStartDatetime = Convert.ToString(sdr["CheckStartDatetime"]),
LastCheckDate = Convert.ToString(sdr["LastCheckDate"]),
Comment = Convert.ToString(sdr["Comment"]),
CheckRecord = Convert.ToString(sdr["CheckRecord"])
});
}
}
}
sqlcon.Close();
}
return View(tasks);
}
[HttpPost]
public ActionResult UpdateTask(Task task)
{
//string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
string query = "UPDATE AS2_AlertsResults SET AlertStatus= @AlertStatus, Comment= @Comment WHERE ProfileDate= @ProfileDate";
using (SqlConnection sqlcon = new SqlConnection(connectionstring))
{
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand(query, sqlcon);
sqlcmd.Parameters.AddWithValue("@ProfileDate", task.Profiledate);
sqlcmd.Parameters.AddWithValue("@AlertStatus", task.AlertStatus );
sqlcmd.Parameters.AddWithValue("@Comment", task.Comment );
sqlcmd.Connection = sqlcon;
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
}
return new EmptyResult();
}
public ActionResult ArchiveResults(Task task)
{
string query = " INSERT INTO AS2_AlertsResultsArchive(ID ,ProfileDate, GroupSubsidiary, FunctionName, FunctionDesc, CheckPeriod, CheckStartDatetime, LastCheckDate, AlertStatus, Comment, CheckRecord) VALUES (@ID,@ProfileDate, @GroupSubsidiary, @FunctionName, @FunctionDesc,@CheckPeriod, @CheckStartDatetime, @LastCheckDate, @AlertStatus, @Comment, @CheckRecord)";
using (SqlConnection sqlcon = new SqlConnection(connectionstring))
{
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand(query,sqlcon);
sqlcmd.Parameters.AddWithValue("@ID", task.ID);
sqlcmd.Parameters.AddWithValue("@ProfileDate", DateTime.Parse(task.Profiledate));
sqlcmd.Parameters.AddWithValue("@GroupSubsidiary", task.GroupSubsidiary);
sqlcmd.Parameters.AddWithValue("@FunctionName", task.FunctionName);
sqlcmd.Parameters.AddWithValue("@FunctionDesc", task.FunctionDesc);
sqlcmd.Parameters.AddWithValue("@CheckPeriod", task.CheckPeriod);
sqlcmd.Parameters.AddWithValue("@AlertStatus", task.AlertStatus);
sqlcmd.Parameters.AddWithValue("@CheckStartDatetime", DateTime.Parse(task.CheckStartDatetime));
sqlcmd.Parameters.AddWithValue("@LastCheckDate", DateTime.Parse(task.LastCheckDate));
sqlcmd.Parameters.AddWithValue("@Comment", task.Comment);
sqlcmd.Parameters.AddWithValue("@CheckRecord", DateTime.Parse(task.CheckRecord));
sqlcmd.Connection = sqlcon;
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
}
return new EmptyResult();
}
}
}
View and Jquery/Ajax: (I have jquery to update the CheckRecord field to the current time when ever the checkbox is checked)
@using AlertNotificationWeb.Models
@model IEnumerable<Task>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Tasks List</title>
<link href="~/Content/Tablestyles.css" rel="stylesheet" />
<script src="~/Scripts/jquery-ui-1.8.20.min.js"></script>
</head>
<body>
<table id="tblTask" class="table" >
<tr>
<th>ID</th>
<th>GroupSubsidiary</th>
<th>FunctionName</th>
<th>FunctionDesc</th>
<th>CheckPeriod</th>
<th>Profiledate</th>
<th>CheckStartDatetime</th>
<th>LastCheckDate</th>
<th>AlertStatus</th>
<th>Comment</th>
<th>Status Updated</th>
</tr>
@foreach (Task task in Model)
{
<tr>
<td class="taskID">
<span>@task.ID</span>
</td>
<td class="GroupSub">
<span>@task.GroupSubsidiary</span>
</td>
<td class="Fname">
<span>@task.FunctionName</span>
</td>
<td class="Fdesc">
<span>@task.FunctionDesc</span>
</td>
<td class="Checkp">
<span>@task.CheckPeriod</span>
</td>
<td class="Pdate">
<span>@task.Profiledate</span>
</td>
<td class="checkstartdate">
<span>@task.CheckStartDatetime</span>
</td>
<td class="lastcheckdate">
<span>@task.LastCheckDate</span>
</td>
<td class="Status">
@if(task.AlertStatus.IsEmpty()) {
<input type="checkbox" value="NULL" id="checker" />
}else{
<input type="checkbox" value="OK" id="checker2" checked/>
}
</td>
<td class="Comment">
<span>@task.Comment</span>
<input type="text" value="@task.Comment" id="comz" style="display:none" />
</td>
<td class="CheckRecord">
<input type="text" id="crecords" value="@task.CheckRecord" />
</td>
<td>
<a class="Save" href="javascript:;">Save</a>
<a class="Archive" href="javascript:;">Archive</a>
<a class="Cancel" href="javascript:;" style="display:none">Cancel</a>
</td>
</tr>
}
</table>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.cdnj
