Cannot insert default value into SQL database from Win Forms project

Question

I have a MS SQL database.

On one of my fields, LotNumber I set the default value to be [dbo].CalculateJulianDate which is a scalar-valued function that takes the current date and creates a Julian date formatted lot number.

When I insert values into my table using SSMS, the value is populated correctly replacing the empty value with the LotNumber.

I am trying to create a visual basic winforms front end for the database in visual studio 2010. I set my datasource, dragged a details view onto a form and I can see the data, make edits to the data, and delete data. However, when I try to insert new data it fills the LotNumber field with NULL.

I do not understand why MS SQL doesn't use the default value rather than the NULL value when data is entered from within my winForms application. I was under the impression that the point of setting up a default value in the SQL schema was to replace NULL values with the default value.

The code for the insert is auto-generated and I can't seem to find it in my application. The only code visible is:

Private Sub Dt_InventoryBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles Dt_InventoryBindingNavigatorSaveItem.Click
Me.Validate()
Me.Dt_InventoryBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.CSRPRDDataSet)
End Sub

Does anyone know why SQL Server doesn't insert the default value like it's supposed to? How do I get the default value to populate from within my application?

Answer 1

To make a custom INSERT query on the TableAdapter, right-click on your Table Adapter, choose Add Query and the Query Wizard will appear. Select "Use SQL Statements" and select "INSERT" for the query type.

Answer 2

The default value is used in SQL Server when you don't set that field value at all. If you set it to NULL then it stays set to NULL. If you remove that field from your INSERT statement then the default value will be used.