Default Values with a DataSet and SQL Server Express don't work at the table level

Default Values with a DataSet and SQL Server Express don't work at the table level

• Roberto Jimenez

  SQL Server Express (a database) and the DataSet (an in-memory cache) are two different things.

  A DataTable is a collection of DataColumns and DataRows. The DataColumn.DefaultValue is DBNull.Value, which translates into NULL when inserting into a database. When you create a new DataRow, its initial values are from the DataColumn.DefaultValue - not the database.

  
  
  

• Mr. Greg

  Hello Adiel,

  Thanks for the tip.

  Another way to ensure that the default value gets assigned to those particular columns (when you have to include them in the DataSet) is to assign the columns default value at the DataSet property level through the DataSets designer...

  
  

• tobis

  there is a way to "trick" it: In design view for the table, remove "Allow Nulls" from the field, then it will insert the default value that you had specified for that field in sql server. of course...you have to make sure you don't have any null records before you set the field to not allow nulls.
  

• Ralph Gasser

  Hello Mark,
  
  Thanks for the reply.
  
  To be honest, I posted this question in the SQL Server Express forums (as a bug) and haven't got a reply back. Usually when this happens I have asked something totally moronic...
  
  I guess what I question is that when using my database tools and when inserting a new row all columns by default have a 'Null' value. The columns that have default values enabled/given for that particular column, have the assigned default values inserted correctly when the original value is 'Null' for a new row; and aborted if another value other than 'Null' was inserted into that column. Ok...
  
  So why doesn't that rule apply when making a new row with a dataset (a null value is still a null value in a new row)
  
  Another way to put this is I am expecting the database engine not the dataset to insert the default values (at the table level) when the value is null for that column in the new row.
  
  To make clear what I am doing; I am using SSMSE to assign the Default Value for a column at the table level, so all default values should be handled at the table level...
  
  Isn't this the purpose of the 'Default Value property' for a column
  
  I have always thought of a 'Default Value' property as a trigger that tested for nulls and then inserted that value if the original inserted value was in fact null.
  
  I understand that the database engine and dataset are two different things, I just assumed that they would work in conjunction with each other on this...
  
  An example of this would be creating new rows in a dataset with a column that is an Identity. For the values of each new row I can set the Identity value and increment them with negative numbers, just so they don't repeat. When I update the dataset to the database, the database engine assigns the correct Identity values for that table, it totally disregards the dataset values for the Identity column for each row.
  
  I haven't done any programming in a while, but I thought I could achieve this in the past with VistaDB and TurboDB (Table level default values and datasets). I will have to go back and re-visit them...
  
  What am I obviously missing
  
  Thanks
  
  

• mallikarjun

  Hello Mark,
  
  You were correct in what you were saying...
  After getting clarification from Chad Boyd...
  
  -----------
  

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

  ------------------------

  It really is pretty simple.
  

  So...

  If I pull a column that has a (table level) default value in it in a dataset and do not specify a value, it will recieve a 'Null' value in the new row that was created in the table(s).

  If I pull no columns that have a (table level) default value in it in a dataset, and when creating the new row(s) the table level default values will be inserted correctly into those tables.
  

  Thanks again.
  
  
• Default Values with a DataSet and SQL Server Express don't work at the table level