none
Validation Exception: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

    Question

  • I have a formview that shows data from one row in a table. The table has HireDate, BirthDate as "date" datatype and LastModifiedTime and CreatedTime as "datetime" datatype. The formview shows up OK on page load, but when I try to edit the data and click Update, it gives me this error.

    HireDate, CreatedTime and LastModifiedTime are set to Allow nulls. I get this exception if I kepp these fields empty in Edit mode. What is the reason for this errror? Also, there is another table with the same specifications and the same functionality works fine with that table, that is, if I enter null values in Hiredate, it does not return error.

    Other Info: I am using LINQDataSource for this formview and database is SQL Server 2008



    Manasi Ranade
    Thursday, October 20, 2011 8:20 PM

Answers

  • There are validators in place to check if the date entered is MM/dd/yyyy format and the user has to type it in. Even if I enter today's date, it does not return any error but saves the date as 1/1/0001 in the database. If I leave it blank, which is an OK condition as that field is not required, then I get this exception.

    So for the HireDate textbox if I enter : 11/18/2011, then the database saves it as 1/1/0001 and if I keep it blank, then it returns this error.

    I actually deleted the LinqDatasource and the FormView, closed the project and reopened it and crezted everything anew just like before and it now works fine *fingers crossed* and hopefully won't start giving any more troubles. But I would like to know what's with the SQL Server datatypes...


    Manasi Ranade
    • Marked as answer by MoneyR Friday, November 18, 2011 4:18 PM
    Friday, November 18, 2011 4:17 PM

All replies

  • Hello,

    Please, could you provide the definitions of these 3 fields ? I think there are differences between them.

    For the fields allowed to be null, you have to check where the field is reall empty and to use the System.DbNull if the date is null

    http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

    http://social.msdn.microsoft.com/Search/en-us?query=dbnull

    http://msdn.microsoft.com/en-us/library/ms172138.aspx

    As i am not using Linq, i cannot help more efficiently for the moment

    http://msdn.microsoft.com/en-us/library/bb547113.aspx

    http://msdn.microsoft.com/en-us/library/bb514963.aspx

    i think that with the last link , you should insert a DBNull value as it is explained in the 3rd link.

    Have a nice day

    PS for moderators : i think that this thread should be moved towards a LINQ Forum, but i don't know which one.


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Thursday, October 20, 2011 9:14 PM
  • Thanks for the links.. Following is the design as it shows in SQL Management Studio: This is how I created almost all other tables and they do not return this error for "date" and "datetime" datatype.

    CREATE

    TABLE [dbo].[MySampleTable]

    (

    [RowID] [int] NOT NULL,

     

     

     

    [BirthDate] [date]

    NULL,

    [HireDate] [date]

    NULL,
     

    [CreatedTime] [datetime]

    NULL,
     

     

    [LastModifiedTime] [datetime] NULL,

    ... (other fields)

     

     

    CONSTRAINT [PK_MySampleTable] PRIMARY KEY CLUSTERED

    (

    [RowID]

    ASC

    )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )

    ON [PRIMARY]


    Manasi Ranade
    Friday, October 21, 2011 1:04 PM
  • Hello,

    I think that it is existing a difference ( in the code ) between the treatement of your both tables in the LINQDataSource code. If it is a LINQ problem ( or maybe a problem in the formview ), a moderator may move your thread , but i have no idea to which forum.

    A little advice : when an error/exception occurs, display at least the StackTrace of the exception ( you have to use a try/catch in your code ) and you will know the line number and the code file name where the error is occuring.For the moment, i have not enough informations to try to help you efficiently ( but i am not a specialist of FormView or LINQ )

    Don't hesitate to post again for more help or explanations.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, October 22, 2011 9:51 AM
  • Hello,

    Please, could you have a look at this link ;

    http://forums.asp.net/p/1407200/3067908.aspx

    It contains a possible explanation of your problem.I think that it is coming from your code not from the definition of your database table.

    Don't hesitate to post again for more help or explanations.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, October 22, 2011 10:04 AM
  • But there's nothing in the code behind...

    When the page loads, the LinqDataSource simply reads an ID from the session and loads appropriate data into the formview. The formview loads correctly. When I click on "New" or "Edit", the proper templates load and if I make editions and keep the date fields blank (they are not required), then I get this error.

    I tried to enter today's date in there and when I checked the database, I saw "01/01/0001 12:00:00 AM" in the database. The only exception I get is when I keep it blank.

    As per Papy's sugestion, I am pasting here the Stack trace  I get when I enter empty data in HireDate, CreatedTime and LastModifiedTime fields:

    Server Error in '/' Application.

    SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


    Stack Trace:

    [SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.]
       System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) +2177837
       System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) +232
       System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) +46
       System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) +5056609
       System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) +6509
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
       System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +316
       System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +113
       System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +344
       System.Data.Linq.StandardChangeDirector.DynamicUpdate(TrackedObject item) +83
       System.Data.Linq.StandardChangeDirector.Update(TrackedObject item) +232
       System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +643
       System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +378
       System.Data.Linq.DataContext.SubmitChanges() +23
       System.Web.UI.WebControls.LinqToSqlWrapper.SubmitChanges(DataContext dataContext) +9
       System.Web.UI.WebControls.LinqDataSourceView.UpdateDataObject(Object dataContext, Object table, Object oldDataObject, Object newDataObject) +115
       System.Web.UI.WebControls.LinqDataSourceView.UpdateObject(Object oldEntity, Object newEntity) +262
       System.Web.UI.WebControls.QueryableDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +115
       System.Web.UI.WebControls.ContextDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +100
       System.Web.UI.WebControls.LinqDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +41
       System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +95
       System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +1154
       System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +445
       System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +112
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +125
       System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +169
       System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +9
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
    



    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.237


    Manasi Ranade
    Tuesday, October 25, 2011 6:50 PM
  • Hello,

    I think you have a problem to translate your date from your formview towards the database:  01/01/0001 12:00:00 AM is the date for the 1st of January Year 1 .

    Your error is explained in that link :

    http://msdn.microsoft.com/en-us/library/system.data.sqltypes.aspx  ( search SQLDateTime )

    Please, coul you explain how your code is using this date ? What control do you use to type your date ? If it is DateTimePicker or similar, you must use the Value property and not the Text property which must be transmitted to the database

    http://msdn.microsoft.com/en-us/library/system.windows.forms.datetimepicker.aspx

    http://msdn.microsoft.com/en-us/library/system.windows.forms.datetimepicker.value.aspx

    A little link about your problem :

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/1426459e-9d0a-4cf0-a1ef-55535e06c6ef 

    Anyway, please, could you provide the code which provides the values necessary to your LinqDatasource from the values coming from your formview ? It is where your error is occuring

    When you write about Formview, i suppose that you are using :

    http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.formview.aspx

    We are waiting for your feedback to try to help you more efficiently.

    Have a nice day

     

    PS : your date format is english or american ?


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    • Edited by Papy NormandModerator Tuesday, October 25, 2011 8:33 PM PS added
    • Proposed as answer by Iric WenModerator Tuesday, November 01, 2011 1:38 AM
    • Marked as answer by Stephanie Lv Friday, November 04, 2011 7:47 AM
    • Unmarked as answer by MoneyR Friday, November 18, 2011 4:18 PM
    Tuesday, October 25, 2011 8:31 PM
  • The formview is connected to a LinqDataSource which selects one row from the table and loads into the FormView.

    Formview has Edit, Insert and Item templates. Item Template is default and when I click "New" or "Edit" buttons on the formview, they load The Edit Template and Insert Template respectively. Also, these templates have textboxes in the formview. This is the FormView Datacontrol. i have not added any control inside it. I simply added the DataSources and Formview and Gridview and configured all of these, so I didn't have to write any code as these controls do everything on their own, just like ObjectDataSources. I never saw this problem with any of my other tables or similar pages, and I have quite a few of them, all of which work fine.

    That's why I am stumped with this one.


    Manasi Ranade
    Tuesday, October 25, 2011 8:45 PM
  • Hello,

    For the use of null values, i would suggest you to have a look at :

    http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.isnull.aspx

    http://msdn.microsoft.com/en-us/library/ms172138.aspx  ( for the handling of null values )

    To be clear, you have to set the SqlDateTime to DbNull to avoid your problem when you don't put a date ( DbNull is a specific value for the null values ).Or your configuration of the textbox is incorrect

    "I never saw this problem with any of my other tables or similar pages" : are you sure that you have already a textbox related to a date which is nullable ?

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Tuesday, October 25, 2011 9:14 PM
  • Make sure to add validators for your date controls so the user would not be able to enter invalid date (say, 01/01/1600).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Iric WenModerator Tuesday, November 01, 2011 1:38 AM
    • Marked as answer by Stephanie Lv Friday, November 04, 2011 7:48 AM
    • Unmarked as answer by MoneyR Friday, November 18, 2011 4:18 PM
    Tuesday, October 25, 2011 9:15 PM
  • There are validators in place to check if the date entered is MM/dd/yyyy format and the user has to type it in. Even if I enter today's date, it does not return any error but saves the date as 1/1/0001 in the database. If I leave it blank, which is an OK condition as that field is not required, then I get this exception.

    So for the HireDate textbox if I enter : 11/18/2011, then the database saves it as 1/1/0001 and if I keep it blank, then it returns this error.

    I actually deleted the LinqDatasource and the FormView, closed the project and reopened it and crezted everything anew just like before and it now works fine *fingers crossed* and hopefully won't start giving any more troubles. But I would like to know what's with the SQL Server datatypes...


    Manasi Ranade
    • Marked as answer by MoneyR Friday, November 18, 2011 4:18 PM
    Friday, November 18, 2011 4:17 PM