Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Heather Floyd 582 posts 892 karma points MVP 4x c-trib
    Nov 12, 2020 @ 20:23
    Heather Floyd
    0

    Umbraco 8.9.1 - MemberService.Save() fails with SqlDateTime overflow error

    After updating a website from 8.6.x to 8.9.1, an Umbraco Forms Workflow which was previous working is now failing.

    The Workflow involves creating a member programmatically.

    Data entered on the form is used to set the standard member properties (name, email, login, password) then the other custom properties are looped-though and updated if the form includes a matching field.

    The member is initially created like this:

                MemberType mt = (MemberType)memberTypeService.Get(maps.MemTypeAlias);
                if (mt != null)
                {
                    IMember m = null;
                    int memberId;
                    try
                    {
                        m = memberService.CreateMemberWithIdentity(usernameMapping, emailMapping, nameMapping, mt);
                        memberService.SavePassword(m, passwordMapping);
                        memberService.Save(m);
    
                        memberId = m.Id;
                        ...
                     }
                    catch (Exception ex)
                    {
                      //error handling stuff
                    }
                }
    

    The problem I am having now is that an error is thrown at that ".Save()" line.

    On my local (SQL Ce) site, the error is:

    System.Data.SqlServerCe.SqlCeException (0x80004005): An overflow occurred while converting to datetime.
       at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
       at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
       at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
       at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
       at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 284
       at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
       at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
       at NPoco.Database.Execute(String sql, CommandType commandType, Object[] args)
       at NPoco.Database.<Update>b__214_0(String sql, Object[] args, Func`2 next)
       at NPoco.Database.UpdateImp[TRet](String tableName, String primaryKeyName, Object poco, Object primaryKeyValue, IEnumerable`1 columns, Func`4 executeFunc, TRet defaultId)
       at NPoco.Database.Update(String tableName, String primaryKeyName, Object poco, Object primaryKeyValue, IEnumerable`1 columns)
       at NPoco.Database.Update(Object poco, Object primaryKeyValue, IEnumerable`1 columns)
       at Umbraco.Core.Persistence.Repositories.Implement.ContentRepositoryBase`3.ReplacePropertyValues(TEntity entity, Int32 versionId, Int32 publishedVersionId, Boolean& edited, HashSet`1& editedCultures)
       at Umbraco.Core.Persistence.Repositories.Implement.MemberRepository.PersistUpdatedItem(IMember entity)
       at Umbraco.Core.Cache.DefaultRepositoryCachePolicy`2.Update(TEntity entity, Action`1 persistUpdated)
       at Umbraco.Core.Services.Implement.MemberService.Save(IMember member, Boolean raiseEvents)
       at Dragonfly.Umbraco8FormsMembers.Workflows.SaveAsUmbracoMember.Execute(Record record, RecordEventArgs e) in C:\Users\Heather\NAS Folders\Active Projects\Webs\~Code Projects\Dragonfly\Dragonfly.Umbraco8FormsMembers\src\Dragonfly\Umbraco8FormsMembers.Workflows\SaveAsUmbracoMember.cs:line 279
       at Umbraco.Forms.Core.WorkflowType.ExecuteWorkflow(Record record, RecordEventArgs e, IWorkflow workflow)
       at Umbraco.Forms.Core.Services.WorkflowService.ExecuteWorkflows(List`1 workflows, RecordEventArgs e)
    

    On the remote (SQL Server) site the error is:

    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 System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 272
       at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.<ExecuteNonQuery>b__31_0()
       at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.<>c__DisplayClass33_0`1.<Execute>b__0()
       at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
       at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.Execute[T](Func`1 f)
       at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteNonQuery()
       at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
       at NPoco.Database.NPoco.IDatabaseHelpers.ExecuteNonQueryHelper(DbCommand cmd)
       at NPoco.Database.Execute(String sql, CommandType commandType, Object[] args)
       at NPoco.Database.Execute(Sql Sql)
       at NPoco.Database.<Update>b__214_0(String sql, Object[] args, Func`2 next)
       at NPoco.Database.UpdateImp[TRet](String tableName, String primaryKeyName, Object poco, Object primaryKeyValue, IEnumerable`1 columns, Func`4 executeFunc, TRet defaultId)
       at NPoco.Database.Update(String tableName, String primaryKeyName, Object poco, Object primaryKeyValue, IEnumerable`1 columns)
       at NPoco.Database.Update(Object poco, Object primaryKeyValue, IEnumerable`1 columns)
       at NPoco.Database.Update(Object poco)
       at Umbraco.Core.Persistence.Repositories.Implement.ContentRepositoryBase`3.ReplacePropertyValues(TEntity entity, Int32 versionId, Int32 publishedVersionId, Boolean& edited, HashSet`1& editedCultures)
       at Umbraco.Core.Persistence.Repositories.Implement.MemberRepository.PersistUpdatedItem(IMember entity)
       at Umbraco.Core.Cache.DefaultRepositoryCachePolicy`2.Update(TEntity entity, Action`1 persistUpdated)
       at Umbraco.Core.Persistence.Repositories.Implement.RepositoryBase`2.Save(TEntity entity)
       at Umbraco.Core.Services.Implement.MemberService.Save(IMember member, Boolean raiseEvents)
       at Dragonfly.Umbraco8FormsMembers.Workflows.SaveAsUmbracoMember.Execute(Record record, RecordEventArgs e)
       at Umbraco.Forms.Core.WorkflowType.ExecuteWorkflow(Record record, RecordEventArgs e, IWorkflow workflow)
       at Umbraco.Forms.Core.Services.WorkflowService.ExecuteWorkflows(List`1 workflows, RecordEventArgs e)
    

    So, the issue is related to saving invalid DateTime property values. The thing is, I'm not actually SETTING any DateTime values in my code, so it has to do with the default values in the new IMember.

    If I examine some date properties on the Member record - specifically the standard properties:

    • Last Lockout Date
    • Last Login Date
    • Last Password Change Date

    They are set to the .Net minimum date - {1/1/0001 12:00:00 AM}. But it seems that when saving the member back to the DB, that date is perhaps causing a problem? Maybe if those values were Null rather than Minimum date it would work?

    I am a bit flummoxed because this seems to be pretty basic Member API functionality. This code used to work, so maybe something changed in a recent version of Umbraco 8? Is anyone else seeing this issue?

    My Setup:

    • Umbraco Cloud
    • Umbraco 8.9.1
    • Forms 8.5.5
    • Deploy 3.5.1
  • Nik 1527 posts 6760 karma points MVP 5x c-trib
    Nov 12, 2020 @ 21:49
    Nik
    100

    Hi Heather,

    Yeah, something changed in the member service in 8.9 that means you have to set the following field:

    member.LastPasswordChangeDate = DateTime.Now;
    

    before you save a member for the first time.

    Cheers

    Nik

  • Heather Floyd 582 posts 892 karma points MVP 4x c-trib
    Nov 12, 2020 @ 22:30
    Heather Floyd
    0

    Okay, for anyone coming across this issue...

    When I used the Members section back-office UI to add a new member, I saw that both "Last Login Date" & "Last Password Change Date" were set to the current time...

    It looks like in the 8.9 version, "Last Password Change Date" is now required.

    So, add this in before you try to call MemberService.Save()

     m.SetValue("umbracoMemberLastPasswordChangeDate", DateTime.UtcNow); //Required by v 8.9
    

    Thanks to friendly folks in the Umbraco Community Slack group for help working this out.

    Ps. I personally think this should be handled automatically without needing to be explicitly set. If you agree, comment over here: https://github.com/umbraco/Umbraco-CMS/issues/9390

Please Sign in or register to post replies

Write your reply to:

Draft