DefaultValue of a datetime column in a TableAdapter

Question

I have an Access table with a datetime column. The Default Value is set to NOW(), as I want it to insert the date in which the record is inserted. Null values are not allowed.

In my corresponding Table Adapter in Visual Studio 2010, when I select that column and view Properties, under DefaultValue it has <DBNull>. If I try to change this to GetDate(), it won't let me.

What's the proper way to do this?

I am getting the:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

error when I try to execute a Table Adapter query that simply tries to return all the rows.

Sam Hobbs · Answer

If you still need help please be more specific. I do not understand what select that column and view Properties under DefaultValue it has  means. Where is it that you are doing that? Is it when the data source is created? I also do not understand the relevance of specifying NOW() in Access for the default value. I do not know if TableAdapters use that but I assume not since you are asking how to do it in the TableAdapter.

Vulpes · Answer

I don't know much about how TableAdapters work but it sounds to me like the column which is giving you the problem has been given a 'nullable' DateTime type so it can be given a null value rather than an 'ordinary' DateTime type which cannot be given a null value. So if I'm right about this I'd see if it makes any difference if you change the type to an ordinary DateTime. Even if this works I'm not sure that the TableAdapter will be able to cope with a variable default value such as DateTime.Now or DateTime.Today. You might need to pass this value yourself each time you insert a new record or if it's possible to manually amend the INSERT statement remove this column altogether so that the database will insert a default value for it.

Amit Dhania · Answer

First you need to update all records of datetime column having null value with some specific date. Then you will be able to change default value to getdate().

DefaultValue of a datetime column in a TableAdapter

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.error when I try to execute a Table Adapter query that simply tries to return all the rows.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

error when I try to execute a Table Adapter query that simply tries to return all the rows.