Using EF: Need help to insert DateTime value into Date ( Database )

Question

Hi,

This is my table,

Models: SFATender

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Threading.Tasks;  
using System.ComponentModel.DataAnnotations;  
using System.ComponentModel.DataAnnotations.Schema;  
  
namespace SalesSystem.Models  
{  
    public class SFATender  
    {  
        public int Id { get; set; }  
  
        [Required(ErrorMessage = "Please enter Tender Name")]  
        public string TenderName { get; set; }  
  
        [Required(ErrorMessage = "Please enter Deadline")]  
        public DateTime Deadline { get; set; }  
  
        [Required(ErrorMessage = "Please select Sales Person")]  
        public string SalesPersonId { get; set; }  
        public string PersonInCharge { get; set; }  
        public DateTime CrtDte { get; set; }  
        public DateTime UpdDte { get; set; }          
  
  
  
    }  
}  
  

Models: ApplicationDbContext

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Threading.Tasks;  
using Microsoft.EntityFrameworkCore;  
  
namespace SalesSystem.Models  
{  
    public class ApplicationDbContext : DbContext  
    {  
          
        public ApplicationDbContext(DbContextOptions options)  
         : base(options)  
        { }  
  
        protected override void OnModelCreating(ModelBuilder modelBuilder)  
        {            
              
  
            modelBuilder.Entity<SFATender>(entity =>  
            {  
                entity.Property(e => e.TenderName)  
                .HasMaxLength(256)  
                .IsUnicode(false)  
                ;  
  
                entity.Property(e => e.Deadline)  
                ;  
  
                entity.Property(e => e.SalesPersonId)  
               .HasMaxLength(450)  
               .IsUnicode(false)  
               ;  
  
                entity.Property(e => e.PersonInCharge)  
             .HasMaxLength(200)  
             .IsUnicode(false)  
             ;  
  
            //    entity.Property(e => e.CrtDte)  
            //   ;  
  
  
            //    entity.Property(e => e.UpdDte)  
            //;  
  
            });  
  
        }  
          
          
        public virtual DbSet<SFATender> SFATender { get; set; }  
  
    }  
}  
  

Controllers: NoticeController/AddNotice

[HttpPost]  
        public IActionResult AddNotice(SFATender model)  
        {  
  
            List<AspNetUsers> _AspNetUsers = _context.AspNetUsers.ToList();  
            var GetSalesPersonList = _AspNetUsers  
                                     .Select(d => new { SalesPersonId = d.Id, SalesPersonDisplay = d.Name }).ToList();  
  
            ViewBag.dsSalesPerson = GetSalesPersonList;  
  
            if (ModelState.IsValid)  
            {  
                _context.SFATender.Add(model);  
                _context.SaveChanges();  
  
                ViewBag.status = true;  
                string url = string.Format("/Notice/Index?status={0}", true);  
                return Redirect(url);  
  
            }  
            else  
            {  
                return View(model);  
            }  
              
        }  

This is Views: Notice/AddNotice

It give this,

Error,

I suspect, Deadline = 23/3/2022 12:00:00 AM in ASP.NET Core is not valid value in Date in SQL Server

How to fix this ?

Answer 1

Hi @Jerry Lipan

The value of CrtDte and UpdDte are invalid. The value 1/1/0001 12:00:00 AM is the main cause of this error.

Suggetion:
Please use console.log in your html page, and check the value in SFATender model. We first need to confirm that the problem is that the date selected by the front end is not passed to the background?
Or these two fields, the front end is the corresponding option when adding, then in this case, when adding, we recommend to use DateTime.Now for initialization.

I know that these two values are allowed to be NULL in the database, but in the program, for a field of type DataTime without a value, the initialized value may be 1/1/0001 12:00:00 AM, so after putting it into the model , will cause this problem.

Please try it as I suggest and look forward to your reply.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards,
Jason

Answer 2

Hi @JasonPan - MSFT ,

Your are right. Nothing to do with Deadline. This EF does not know how to SQL Server to generate the value using GetDate()

CREATE TABLE [dbo].[SFATender](  
	[Id] [int] IDENTITY(1,1) NOT NULL,  
	[TenderName] [varchar](200) NOT NULL,  
	[Deadline] [date] NOT NULL,  
	[SalesPersonId] [nvarchar](450) NOT NULL,  
	[PersonInCharge] [varchar](200) NULL,  
	[CrtDte] [datetime] NULL,  
	[UpdDte] [datetime] NULL,  
 CONSTRAINT [PK_SFATender] PRIMARY KEY CLUSTERED   
(  
	[Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],  
 CONSTRAINT [SFATender_UQ001] UNIQUE NONCLUSTERED   
(  
	[TenderName] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
GO  
  
ALTER TABLE [dbo].[SFATender] ADD  CONSTRAINT [DF_SFATender_Deadline]  DEFAULT (getdate()) FOR [Deadline]  
GO  
  
ALTER TABLE [dbo].[SFATender] ADD  CONSTRAINT [DF_SFATender_PersonInCharge]  DEFAULT ('N/A') FOR [PersonInCharge]  
GO  
  
ALTER TABLE [dbo].[SFATender] ADD  CONSTRAINT [DF_SFATender_CrtDte]  DEFAULT (getdate()) FOR [CrtDte]  
GO  
  
ALTER TABLE [dbo].[SFATender] ADD  CONSTRAINT [DF_SFATender_UpdDte]  DEFAULT (getdate()) FOR [UpdDte]  
GO  
  
ALTER TABLE [dbo].[SFATender]  WITH CHECK ADD  CONSTRAINT [FK_SFATender_SFATender_001] FOREIGN KEY([SalesPersonId])  
REFERENCES [dbo].[AspNetUsers] ([Id])  
GO  
  
ALTER TABLE [dbo].[SFATender] CHECK CONSTRAINT [FK_SFATender_SFATender_001]  
GO  

I need to initiate the value in SFATender Constructor

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Threading.Tasks;  
using System.ComponentModel.DataAnnotations;  
using System.ComponentModel.DataAnnotations.Schema;  
  
namespace SalesSystem.Models  
{  
    public class SFATender  
    {  
        public int Id { get; set; }  
  
        [Required(ErrorMessage = "Please enter Tender Name")]  
        public string TenderName { get; set; }  
  
        [Required(ErrorMessage = "Please enter Deadline")]          
        public DateTime Deadline { get; set; }  
  
        [Required(ErrorMessage = "Please select Sales Person")]  
        public string SalesPersonId { get; set; }  
        public string PersonInCharge { get; set; }  
        public DateTime CrtDte { get; set; }  
        public DateTime UpdDte { get; set; }  
  
        public SFATender()  
        {  
            this.CrtDte = DateTime.Now;  
            this.UpdDte = DateTime.Now;  
        }  
  
  
    }  
}  

Thanks for help