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? — Alexander Petrov
– Alexander Petrov, Commented Jun 29, 2022 at 14:52

WhemanLimon · Accepted Answer · 2022-06-29 11:45:32Z

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', '[email protected]');

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, 2022 at 11:45

WhemanLimon

33 bronze badges

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

1 Comment

Janki Dave Over a year ago

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

Markus · Accepted Answer · 2022-06-29 11:51:06Z

0

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;

answered Jun 29, 2022 at 11:51

Markus

22.7k4 gold badges34 silver badges61 bronze badges

2 Comments

Janki Dave Over a year ago

What do I need to add to the ID feild, so that it gets me unique id's everytime

Markus Over a year ago

@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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related