DefaultValue of a datetime column in a TableAdapter

John Luciani

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs14y ago
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.
Vulpes14y ago
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 Dhania14y ago
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.