While adding data to db, date column is set to getdate() but gives an error, date can't be empty

Question

I am making a resgistration form in ASP.NET Core and this is how my table schema looks enter image description here

The idCreatedTime field is a datetime field and has default value set to getdate(). When a user submits the registration form, where I am only asking for his username, emailId and password as the other two fields are auto value fields,I get this error

SqlException: Cannot insert the value NULL into column 'idCreatedTime', table 'PoliticalData.dbo.UserData'; column does not allow nulls. INSERT fails. The statement has been terminated.

Registration form

@page
@model IndianPoliticalData.Pages.UserCredentials.RegisterModel

<div>
    <form method="post">        
        <label>Enter Username</label>
        <input type="text" name="username" asp-for="UserData.Username" placeholder="Enter Username"/>

        <label>Enter EmailId</label>
        <input type="email" name="EmailId" asp-for="UserData.EmailId" placeholder="Enter Email"/>

        <label>Enter Password</label>
        <input type="password" name="password" asp-for="UserData.Password" placeholder="Enter Password" />

        <button type="submit">Register</button>
    </form>
</div>

Code that inserts data to the db

public async Task<IActionResult> OnPost()
{
    if (ModelState.IsValid)
    {
        await _context.UserData.AddAsync(UserData);
        await _context.SaveChangesAsync();
        return RedirectToPage("Index");
    }
    else
    {
        return Page();
    }
}

Just sharing the required columns

[Column("password")]
[StringLength(50)]
[Unicode(false)]
public string Password { get; set; } = null!;
        
[Column("emailId")]
[StringLength(50)]
[Unicode(false)]
public string EmailId { get; set; } = null!;

// Just added DateTime.UtcNow to check if that works.
[Column("idCreatedTime", TypeName = "datetime")]
public DateTime? IdCreatedTime { get; set; } = DateTime.UtcNow;

[InverseProperty("User")]
public virtual ICollection<Bookmark> Bookmarks { get; set; }

What RDBMS are you using? How you set default value? Like this [Default values](https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations#default-values)? — Alexander Petrov, Jun 29 '22 at 14:52

score 0 · Answer 1 · answered Jun 29 '22 at 11:45

0

Have you tried doing an insert from the SQL Management Studio (or any other client) to see if that works?

INSERT INTO PoliticalData.dbo.UserData (username, password, emailId) 
VALUES ('dummy_username', 'dummy_password', 'somemail@domain.com');

If it does work this may be a missconfiguration in the ORM you are using, either it is EntityFramework or any other.

Also, have you tried the different solutions pointed out on this question?

answered Jun 29 '22 at 11:45

WhemanLimon

3
3

It works the way it should, does create a unique id and also adds the current date and time on my computer and yes I am using entity framework – Janki Dave Jun 29 '22 at 11:46

score 0 · Answer 2 · answered Jun 29 '22 at 11:51

The value of the IdCreatedTime property on UserData is probably null so that Entity Framework tries to insert a null value into the database. To avoid this, set the property before inserting the row in the database:

public async Task<IActionResult> OnPost()
{
  if (ModelState.IsValid)
  {
    Userdata.IdCreatedTime = DateTime.UtcNow;
    await _context.UserData.AddAsync(UserData);
    await _context.SaveChangesAsync();
    return RedirectToPage("Index");
  }
  else
  {
    return Page();
  }
}

If you want to avoid having null values in the property, change the definition of the column from a Nullable<DateTime> to a DateTime by removing the question mark:

[Column("idCreatedTime", TypeName = "datetime")]
public DateTime IdCreatedTime { get; set; } = DateTime.UtcNow;

What do I need to add to the ID feild, so that it gets me unique id's everytime — Janki Dave, Jun 29 '22 at 12:01
@JankiDave in order to get the Id of the inserted row, add a `public int Id { get; set; }` property to your class and read the value of the property after `SaveChangesAsync`. — Markus, Jun 29 '22 at 12:34

While adding data to db, date column is set to getdate() but gives an error, date can't be empty

2 Answers2