0

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?

gwhenning
  • 138
  • 1
  • 3
  • 14
  • I should point out that I know I can put an IF statement before "Me.Validate()", but I'm looking more for a technical answer as to why it doesn't work and if it's possible to get it to work without duplicating the code to generate the lotNumbers. There is also a default value field in the dataset designer, but I can't seem to get that to use either the SQL function or a custom function from within the application. – gwhenning Aug 29 '14 at 23:07

2 Answers2

3

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.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • Yes, but VB is adding the NULL value. The question is how to remove the field from the VB generated insert statement. I do not see that code listed anywhere. – gwhenning Sep 02 '14 at 15:16
  • As I said against the other answer, you need to edit the `CommandText` of the `InsertCommand` of the table adapter in question. A bit of reading would have told you that the `SELECT`, `INSERT`, `UPDATE` and `DELETE` statements generated by the Data Source Wizard are contained in the `SelectCommand`, `InsertCommand`, `UpdateCommand` and `DeleteCommand` properties of the table adapter. You'll also need to remove the parameter that corresponds to that column as you'll be removing it from the SQL code. – jmcilhinney Sep 02 '14 at 23:16
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.

nshah
  • 340
  • 1
  • 5
  • OK, did that, this is exactly the type of answer I was looking for. However, when I add a new row and click save it is still trying to add a row to the field that I removed on the UpdateAll function. How / where do I edit that function to use the new insert query? – gwhenning Sep 02 '14 at 14:52
  • You don't add a custom query. You edit the existing `INSERT` statement, which is contained in the `InsertCommand` of the table adapter. – jmcilhinney Sep 02 '14 at 22:48