SQL default value for DateTime

[Adam]

Perhaps I am missing something, but I have a table with default values:

CREATE TABLE [dbo].[Person](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [PersonTypeID] [int] NOT NULL,
    [Title] [varchar](50) NULL,
    [FirstName] [varchar](50) NOT NULL,
    [MiddleName] [varchar](50) NULL,
    [LastName] [varchar](50) NOT NULL,
    [Suffix] [varchar](50) NULL,
    [Active] [bit] NOT NULL,
    [DateModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

,,,
ALTER TABLE [dbo].[Person] ADD  CONSTRAINT [DF_Person_Active]  DEFAULT ((1)) FOR [Active]
GO

ALTER TABLE [dbo].[Person] ADD  CONSTRAINT [DF_Person_DateModified]  DEFAULT (getdate()) FOR [DateModified]
GO

And I don't want to set the Active or DateModified fields so that the system will do it automatically.
However, I cannot set
 this.person.DateModified = null
but have to do:
 this.person.DateModified = DateTime.Now;

Error    25    Cannot convert null to 'System.DateTime' because it is a non-nullable value type   
Error    26    Cannot convert null to 'bool' because it is a non-nullable value type    (for the Active field)

Do I need to do everything through a stored procedure?

[PetarP]

Hello Adam,

We are guessing that you need those default values on insert. If that is correct you can initialize those fields in the constructor of your class. For example if you would like the DateModified to be date time today you will need to do something like:

//The 'no-args' constructor required by OpenAccess.
        public SomeClass()
        {
            this.dateModified = DateTime.Today;
        }

If you have this in place you wont need to assign any value to this property as it will automatically receive default value. If you would like to assign some value to it than the one in the constructor will be override.
Note that in the forward mapping wizard there is an option for date times called Set Field to current date during create. If this option is set to true for a given field than every time an object of this class is inserted the selected field will be inserted with the current date. The other option called Set Field to current date during update  if set to true, would always keep the date of your last update in this field.

Kind regards,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Adam]

Is there a way to set that in the reverse mapping?

[PetarP]

Hi Adam,

Unfortunately you can not set custom logic in your constructor through the wizard.
Currently the wizard supports setting a default value only for date time fields. However you can set default values through the app.config file for the following fields: date time,int, sbyte and short. Here is some additional information on how t his is done through the app.config file: AutoSet

Sincerely yours,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Daniel Plomp]

Hello,

In my solution I want to set the DateTime fields in my tables to be auto generated. So in SQL I add 'getdate()' inside the default value property. Now when I use ORM to add a new record in a table with this option set, I'm getting an error 'SqlDateTime overflow'. It looks like ORM is trying to insert a record, but the getdate() is not recognized?

If I set the DateTime fields to allow NULL and manually add a 'DateTime.Now' then it works okay, but this is not the behaviour I'd like to use?

Any idea's?

Daniel

[Alexander]

Hello Daniel,

You should better use the integrated mechanism of Telerik OpenAccess ORM for setting default DateTime values. Just open the Forward mapping wizard, select the DateTime field from the treeview and enable the "Set field to current date during create" option. If you want to update the field when the object is modified, enable the "Set field to current date during update" option as well.
Hope that helps.

Best wishes,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Daniel Plomp]

Hello Alexander,

This option works for the 'update' part. But when creating new records I'm getting this javascript error:

Insert of '1265630872-' failed: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
INSERT INTO [Project] ([DatetimeChanged], [DatetimeCreated], [Description], [Title]) VALUES (?, ?, ?, ?)
select scope_identity()
(set event logging to all to see parameter values) System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)

I did exactly what you told me...

Daniel

[Alexander]

Hi Daniel,

This is very odd, I tested the functionality again and it works fine with the latest few versions. Please note that if you have enabled for particular persistent type the "Set field to current date during update" option but not the other one, the date will not be automatically set for new objects. In order to have the date set for new instances and updated when they are changed, you should have the two options checked at the same time.
Please also verify that all of the DateTime fields have at least the "Set field to current date during create" option enabled.

Kind regards,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.