How to save DateTime format in ASP.NET MVC .NET CORE

Hi guys,, I'm try to save data in sql server with DateTime format, I'm get error like this

SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.

in the Controller I have been try to + Convert.ToDateTime(pasienModel.Tanggal_Lahir) + but not working. Any help could be appreciated.

this is the PasienModel.cs

[DataType(DataType.DateTime)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true, NullDisplayText = "NULL")]
public Nullable Tanggal_Lahir { get; set; }

this is the PasienController.cs

[HttpGet]
public IActionResult TambahData()
{
return View();
}
public IActionResult TambahData(PasienModel pasienModel)
{
if (ModelState.IsValid)
{
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("db_klinik")))
{
using (SqlCommand cmd = new SqlCommand("Insert Into Pasien (Nama_Pasien,Alamat,Jenis_Kelamin,Tanggal_Lahir,Status) Values ('" + pasienModel.Nama_Pasien + "','" + pasienModel.Alamat + "','" + pasienModel.Jenis_Kelamin + "','" + Convert.ToDateTime(pasienModel.Tanggal_Lahir) + "','" + pasienModel.Status + "')", con))
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
return RedirectToAction("Index", "Dokter");
}
}
}
else
{
return View(pasienModel);
}
}

this is the View TambahData.cshtml

class="form-group row">
for="Tanggal_Lahir" class="col-sm-2 col-form-label">Tanggal Lahir
class="col-sm-10">
@Html.TextBoxFor(model => model.Tanggal_Lahir, new { @class = "form-control datepicker", placeholder = "Enter Date", id = "Date", autocomplete = "off" })
for="Tanggal_Lahir" class="text-danger">

From Line 10 to 21 can be re-written as

using (SqlCommand cmd = new SqlCommand("Insert Into Pasien (Nama_Pasien,Alamat, Jenis_Kelamin,Tanggal_Lahir, Status) values
(@Nama_Pasien, @Alamat, @Jenis_Kelamin, @Tanggal_Lahir, @Status) ));
{
cmd.Parameters.AddWithValue("@Nama_Pasien", pasienModel.Nama_Pasien);
cmd.Parameters.AddWithValue("@Alamat", pasienModel.Alamat);
cmd.Parameters.AddWithValue("@Jenis_Kelamin", pasienModel.Jenis_Kelamin);
cmd.Parameters.AddWithValue("@Tanggal_Lahir", Convert.ToDateTime(pasienModel.Tanggal_Lahir));
cmd.Parameters.AddWithValue("@Status", pasienModel.Status);
con.Open();
cmd.ExecuteNonQuery();
return RedirectToAction("Index", "Dokter");
}

Can you share the value of pasienModel.Tanggal_Lahir ?

@Amit Gupta

thanks sir for the advice..

Set to only date instead of DateTime [DataType(DataType.Date)] I suggest you to post your new issue to a new thread. Anyway, you can try different approaches mentioned here httpss://www.koskila.net/help-my-asp-net-mvc-textbox-date-format-is-wrong/

@Amit Gupta

Great answer sir,, thanks so much for the help,, I was try it and then solved my problem.

this the result sir.. for insert and update has been working properly, my next steps is I will try to disappearing the 00:00:00 bihind the Date

@Amit Gupta thanks for the answer sir, maybe you can give me example for corrected based my code above.

In addition to what @Nitin sir said, always write cleaner and maintainable code with parameterized query instead of concatinated string.

@Nitin Sontakke l have try entry valid date like 1993/09/02 that come from View use jquery calendar and the data not null and then I'm submit the data but I'm get error like above.

I think the value of Tanggal_Lahir is not correct.

It is unlikely that it is null, because otherwise it could have given error at the call convert.ToDateTime.

Which leaves the possibility that it is not a date after 01-01-1753.

Please be generally aware that 01-01-0001 is a valid date on .net side but not on SQL Server side.

Hope it helps!

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
Accepted answer
From Line 10 to 21 can be re-written as
using (SqlCommand cmd = new SqlCommand("Insert Into Pasien (Nama_Pasien,Alamat, Jenis_Kelamin,Tanggal_Lahir, Status) values
(@Nama_Pasien, @Alamat, @Jenis_Kelamin, @Tanggal_Lahir, @Status) ));
{
cmd.Parameters.AddWithValue("@Nama_Pasien", pasienModel.Nama_Pasien);
cmd.Parameters.AddWithValue("@Alamat", pasienModel.Alamat);
cmd.Parameters.AddWithValue("@Jenis_Kelamin", pasienModel.Jenis_Kelamin);
cmd.Parameters.AddWithValue("@Tanggal_Lahir", Convert.ToDateTime(pasienModel.Tanggal_Lahir));
cmd.Parameters.AddWithValue("@Status", pasienModel.Status);

con.Open();
cmd.ExecuteNonQuery();
return RedirectToAction("Index", "Dokter");
}
Can you share the value of  pasienModel.Tanggal_Lahir ?
+3
Tri Setia5y ago
@Amit Gupta
thanks sir for the advice..
+2
Amit Gupta5y ago
Set to only date instead of DateTime [DataType(DataType.Date)] I suggest you to post your new issue to a new thread. Anyway, you can try different approaches mentioned here httpss://www.koskila.net/help-my-asp-net-mvc-textbox-date-format-is-wrong/
+3
Tri Setia5y ago
@Amit Gupta
Great answer sir,, thanks so much for the help,, I was try it and then solved my problem.
this the result sir.. for insert and update has been working properly, my next steps is I will try to disappearing the 00:00:00 bihind the Date

+2
Tri Setia5y ago
@Amit Gupta thanks for the answer sir, maybe you can give me example for corrected based my code above.
+2
Amit Gupta5y ago
In addition to what @Nitin sir said, always write cleaner and maintainable code with parameterized query instead of concatinated string.
+2
Tri Setia5y ago
@Nitin Sontakke l have try entry valid date like 1993/09/02 that come from View use jquery calendar and the data not null and then I'm submit the data but I'm get error like above.
+2
Nitin Sontakke5y ago
I think the value of Tanggal_Lahir is not correct.

It is unlikely that it is null, because otherwise it could have given error at the call convert.ToDateTime.

Which leaves the possibility that it is not a date after 01-01-1753.

Please be generally aware that 01-01-0001 is a valid date on .net side but not on SQL Server side.

Hope it helps!
+2