DataTable with Column Default Values not working as execpted

[William F. Kinsley]

I was having a problem using a DataTable targeted against a table with
columns that have default values and do not allow nulls. When you added a
new row to the DataTable, and call Update on the adapter. It uses the
CommandBuilder to build the insert against the table. This insert has the
column set to null, which will fail, because the column does not allow
nulls. If I fill in the default value property on the datatable column, it
uses the default value instead of null and everything is happy.

This brings me to question #1) Is there a way to fill datatable schema
information that includes the default value automatically? The
missingschemaaction stuff does not seem to do the job.

So I added a manual schema fill for default values and everything works fine
for columns with static default values, but I still have a problem with
default values that are functions such as (getdate()) on a date time column
for instance. In my current model, the default value comes back from the
schema information as (getdate()), which I then try to set as the default
value of the column. This however causes an exception because of course
(getdate()) can not be translated into a date by the datatable. For legal
reasons the sql server must create this date when the row is added. I can
not create a new date in code and set that as the default value.

This brings me to question #2) Is there a way to use function default
values with a datatable.

 

[Kevin Yu [MSFT]]

Hi William,

1. The default value information will not be filled to the schema from the
database structure automatically. This is by design. So we have to do this
manually in the schema designer.

2. DataColumn default values don't support functions. So I think you have
to leave the fieldfor database to generate.

Since the CommandBuilder does not support this, we have to write our own
update,insert and delete command.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."