Database default value

markhh · February 19th, 2013, 05:59 PM

Hi Folks, I am trying to enter a default value (todays date) in my database by setting (getdate()) in the default value or binding in the Date column properties - but it is not running.

I am using a details view with a associated sqldata source and i have generated the usual insert / delete commands.

Any ideas of where I am going wrong,

Thanks
Mark

Imar · February 19th, 2013, 07:17 PM

Can you define "it's not running"? Do you get an error?

It's important to realize that defaults only work when your SQL statement is not supplying a value. So when you've set up your data controls for that date column, SQL will take the value supplied by the control, not it's default.

Cheers,

Imar

markhh · February 20th, 2013, 03:55 PM

Hi Imar, what I mean is that when I insert a new record the date field which I am trying to insert automatically using (getdate()) is not inserted - no errors are reported and the field remains null. What about using an event like ItemInserting would this be another way around it,

Regards
Mark

Imar · February 20th, 2013, 04:31 PM

It sounds like your UI is still supplying a value, even though it's null. A default only works when you're not supplying a value (even a null value) for the column with the default. So, let's say you have a table with an Id, a Name and a CreationDate, the following would trigger the default on CreationDate:

INSERT INTO SomeTable (Id, Name) VALUES (1, 'Something')

but this wouldn't:

INSERT INTO SomeTable (Id, Name, CreationDate) VALUES (1, 'Something', null)

If you have a data control with a parameter for CreationDate, ASP.NET sends null to the database which results in null, an not the result of GetDate() to be inserted.

Either remove the column / parameter from your data control, or replicate the default value in your code in one of the data events by supplying a value explicitly.

Hope this helps,

Imar

markhh · February 21st, 2013, 09:22 AM

Thanks again Imar, some very good points.

Regards
Mark

markhh · April 25th, 2013, 04:38 AM

Hi Imar, your solution works fine (removing the field in question from the sqldatasource).

Another query I have searched but I cannot find how to insert a value or text into my database using one of the data events (such as additional comments). Any ideas,

Regards
Mark

Imar · April 25th, 2013, 04:41 AM

Hi Mark,

I don't understand the question. Can you elaborate?

Also, can you please start a new thread for each new topic?

Thanks,

Imar