August 12, 2013 at 8:43 am
I have an Access application as a front-end to an SQL Server 2008 backend. There is one field (dtDateEmission) on one table that has a default value of (getdate()). This is on the SQL Server side when I look at the table's properties. When I manually enter a table directly using SSMS the default works and the field gets the current data and time.
In production, the Access application inserts a record in this table by calling a stored procedure. The stored procedure does not reference the field dtDateEmission, only other fields are given values and the record gets added to the table. Yet, when this is happenning, the dtDateEmission field is given a value of NULL. I traced the execution using SQL Profiler and there is nowhere in the application that assigns a null value to this field. Yet when the user clicks the button in the app to enter a new record, the field gets a NULL value, even with a default of GETDATE() at the table level.
I copied the database over to our development environment, and I ran the application myself. With me, the field gets the current date and time and everything works. But in production, the user ends up saving a NULL value for this field.
Very strange!
Can anyone explain this or has something like this happen to them?
Thanks!
August 12, 2013 at 8:51 am
Does this date column allow NULL? If you specify a default for a column but allow NULL it will NOT use the default when you don't include that column in a CRUD operation.
Change the column so it doesn't allow NULL and it should populate with the default as you are expecting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2013 at 9:01 pm
Yes, the column allows Nulls.
Ok, I'll give that a try tomorrow. However, it's strange that the current setup has been working for years and now all of a sudden it's storing Null values.
May 16, 2016 at 2:59 am
hi even though default is assigned on particular column,if you explicitly supply NULL as value the default don't get invoked,so its value will be NULL
Thanks,
Dileep
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply