SQL default value for DateTime

8 posts, 0 answers
  1. Adam
    Adam avatar
    39 posts
    Member since:
    Jun 2009

    Posted 12 Aug 2009 Link to this post

    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?



  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 17 Aug 2009 Link to this post

    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.
  3. DevCraft banner
  4. Adam
    Adam avatar
    39 posts
    Member since:
    Jun 2009

    Posted 18 Aug 2009 Link to this post

    Is there a way to set that in the reverse mapping?
  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 19 Aug 2009 Link to this post

    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.
  6. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 09 Oct 2009 Link to this post

    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
  7. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 09 Oct 2009 Link to this post

    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.
  8. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 09 Oct 2009 Link to this post

    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
  9. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 12 Oct 2009 Link to this post

    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.
Back to Top
DevCraft banner