Default Values properties (table level) not working.

I am using SQL Server Management Studio Express (SSMSE) with SQL Server Express as my database tools/database to assign the ‘Default Value’ for a column at the table level.

Going over the basics… using the database tools (SSMSE) and when inserting a new row; all rows by default have a 'Null' value. Ok.
If a default value is assigned to a column (table level) using the database tools, the default value is inserted correctly if that column has a null value upon the creation of a new row. Ok.

This works fine when I am working with SSMSE on tables (inserting, deleting editing rows etc.) within my database…

But this doesn’t apply or work for adding new rows with datasets (example: using the default insert, update, delete statements provided by the wizard and using a DataGridView). My table level default values are not inserted into the new row, instead my column that had a default value assigned; now has a 'Null' value in the new row that was created by the dataset.

Isn’t a Null value is still a Null value for a new row?

Shouldn’t the database engine supply that ‘default value’ for a field that had a ‘Null’ value upon row creation?

I have always thought of a table level column ‘Default Value property’ as a trigger that tests for nulls and inserts the default value if that column has a null value when the new row is created. So I am expecting the database engine to insert the default value for that column, not the dataset when the value inserted into that column is null for a new row. I really don't need a (table level) column default property that only works with database tools for inserting new rows, that doesn't help me... totally baffled here...
Thanks

Hey Rick.

Default values will be applied to a column when NO explicit value is specified for the column in the corresponding insert (this includes a <NULL> explicit value)...so, for example, assume I have a table with 2 columns, colA and colB, and on colB I have a default value of 'colBDefault' specified...the following statement will end up with a record that includes a row with 'colAvalue' for colA, and null for colB, because I am explicitly saying to use a null value for colB:

insert table (colA, colB) select 'colAvalue', null

However, the following statement will end up with a value of 'colAvalue' for colA, and the default value of 'colBDefault' for colB, because no explicit value is specified for colB:

insert table (colA) select 'colAvalue'

I'd bet that the DataGridView is specifying all columns with a null value for anything you don't specify. To prove this, you could run a trace on the Sql server to see what the actual insert command being executed is...

HTH,

|||Hello Chad,

Thanks for the reply. That did help.

Unfortunately I couldn't get the ADO.Net trace logging to work... my tracing abilities are pretty much nil...

Another way to look at this is I am only pulling certain text fields that I want (no default value assigned) from the adapter / dataset for that table, not all of the fields from that particlular table.
The fields that I have designated a default value for are not included in the dataset, so they do not have an insert command etc. (or value assigned) for those fields for that table.
But... those fields that are *not* included in the insert statement etc. for that table do in fact show a value of 'Null' for the new row even though they have a default value assigned for them at the table level...

Example:
Fields: (ID), (LastName), (FirstName), ((Age) - default value set to 0), ((DeptNo) - default set to 100)

The adapter is only pulling fields: (ID), (LastName) and (FirstName); the insert etc. commands only pertain to those fields.
When a new row is inserted fields: (Age) and (DeptNo) do show a 'Null' value, not their assigned default value.

Thanks,
Rick

|||Whooooops...

My apologies!

It does work as you suggested!
I literally had six different forms to test things and simply got them mixed up, of what worked and what didn't!!!

Thanks,
Rick