System.Data.SqlClient.SqlException: Cannot insert the value NULL into column in AspnetCore Web Api

Question

I am creating web api in AspNetCore. I want to set default DateTime value in the sql Server. The below is way to Create the Web Api.

Controller :

[HttpPost]
        [Route("Insert/PartyAddress")]
        public async Task<IActionResult> PostPartyAddressAsync([FromBody] PartyAddress partyAddress)
        {
            if (!ModelState.IsValid)
                return BadRequest(ModelState);
            var result = await _repo.PostPartyAddressAsync(partyAddress);
            return Ok(result);
        }

Repository logic :

public async Task<bool> PostPartyAddressAsync(PartyAddress partyAddress)
        {
            try
            {
                TblPartyAddress tblPartyAddress = new TblPartyAddress();
                tblPartyAddress.PartyId = partyAddress.PartyId;
                tblPartyAddress.AddressLine1 = partyAddress.AddressLine1;
                tblPartyAddress.AddressLine2 = partyAddress.AddressLine2;
                tblPartyAddress.Pincode = partyAddress.Pincode;
                tblPartyAddress.City = partyAddress.City;
                tblPartyAddress.State = partyAddress.State;
                tblPartyAddress.Country = partyAddress.Country;
                tblPartyAddress.PersonName = partyAddress.PersonName;
                tblPartyAddress.Email = partyAddress.Email;
                tblPartyAddress.ContactNo = partyAddress.ContactNo;
                tblPartyAddress.IsDefault = partyAddress.IsDefault;
                tblPartyAddress.IsActive = partyAddress.IsActive;

                await ctx.Context.tblPartyAddresses.AddAsync(tblPartyAddress);
                ctx.Context.Entry(tblPartyAddress).State = EntityState.Added;
                await ctx.Context.SaveChangesAsync();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

Mapping Class

[Table("tblPartyAddress")]
    public class TblPartyAddress
    {
        [Key]
        [Column("Id")]
        [Required]
        public int Id { get; set; }
        [Column("PartyId")]
        [Required]
        public int PartyId { get; set; }
        [Column("AddressLine1")]
        [Required]
        public string AddressLine1 { get; set; }
        [Column("AddressLine2")]
        public string AddressLine2 { get; set; }
        [Column("Pincode")]
        [MaxLength(50)]
        [Required]
        public string Pincode { get; set; }
        [Column("City")]
        [MaxLength(50)]
        public string City { get; set; }
        [Column("State")]
        [MaxLength(50)]
        public string State { get; set; }
        [Column("Country")]
        [MaxLength(50)]
        public string Country { get; set; }
        [Column("PersonName")]
        [MaxLength(50)]
        [Required]
        public string PersonName { get; set; }
        [Column("Email")]
        [MaxLength(50)]
        [Required]
        public string Email { get; set; }
        [Column("ContactNo")]
        [MaxLength(20)]
        [Required]
        public string ContactNo { get; set; }
        [Column("IsDefault")]
        [Required]
        public bool IsDefault { get; set; }
        [Column("IsActive")]
        [Required]
        public bool IsActive { get; set; }
        [Column("CreatedDate")]
        public DateTime? CreatedDate { get; set; } 
        [Column("ModifiedDate")]
        public DateTime? ModifiedDate { get; set; }
    }

But i get the following exception in Insert Data to the Table.

{Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'CreatedDate', table 'AasthaSales.dbo.tblPartyAddress'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
       at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__108_0(Task`1 result)
       at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()
       --- End of inner exception stack trace ---
       at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__10.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__61.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__59.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__48.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

If Anyone have doubt please feel free to ask.

Any Help will be Appreciated.

Chris Pratt · Accepted Answer · 2018-03-28 13:49:15Z

1

A nullable DateTime will obviously map to a column that allows nulls, so the the only possible explanation is that at some point it was either not a DateTime? and you made it one or you had the Required attribute on it and removed it. In either case, you need to do a new migration to propagate those changes back to the database.

However, for this particular scenario, something like your CreatedDate property should be non-nullable and simply have a default set:

public DateTime CreatedDate { get; set; } = DateTime.UtcNow;

answered Mar 28, 2018 at 13:49

Chris Pratt

240k37 gold badges414 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column in AspnetCore Web Api

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related