My "List View - Members" data type is configured for displaying the columns username, updateDate and createDate.
When I try to sort the list under "Members" by updateDate or createDate, I get an error:
An error occured
Invalid column name 'versionDate'.
Exception Details
System.Data.SqlClient.SqlException: Invalid column name 'versionDate'.
Stacktrace
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) in D:\a\1\s\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 172
at NPoco.Database.ExecuteReaderHelper(DbCommand cmd)
at NPoco.Database.ExecuteDataReader(DbCommand cmd)
at NPoco.Database.<QueryImp>d__164`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at NPoco.Database.<Page>b__170_0[T](Page`1 paged, Sql thesql)
at NPoco.Database.PageImp[T,TRet](Int64 page, Int64 itemsPerPage, String sql, Object[] args, Func`3 executeQueryFunc)
at NPoco.Database.Page[T](Int64 page, Int64 itemsPerPage, String sql, Object[] args)
at NPoco.Database.Page[T](Int64 page, Int64 itemsPerPage, Sql sql)
at Umbraco.Core.Persistence.Repositories.Implement.ContentRepositoryBase`3.GetPage[TDto](IQuery`1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, Func`2 mapDtos, Sql`1 filter, Ordering ordering) in D:\a\1\s\src\Umbraco.Core\Persistence\Repositories\Implement\ContentRepositoryBase.cs:line 494
at Umbraco.Core.Persistence.Repositories.Implement.MemberRepository.GetPage(IQuery`1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, IQuery`1 filter, Ordering ordering) in D:\a\1\s\src\Umbraco.Core\Persistence\Repositories\Implement\MemberRepository.cs:line 525
at Umbraco.Core.Services.Implement.MemberService.GetAll(Int64 pageIndex, Int32 pageSize, Int64& totalRecords, String orderBy, Direction orderDirection, Boolean orderBySystemField, String memberTypeAlias, String filter) in D:\a\1\s\src\Umbraco.Core\Services\Implement\MemberService.cs:line 406
at Umbraco.Web.Editors.MemberController.GetPagedResults(Int32 pageNumber, Int32 pageSize, String orderBy, Direction orderDirection, Boolean orderBySystemField, String filter, String memberTypeAlias) in D:\a\1\s\src\Umbraco.Web\Editors\MemberController.cs:line 73
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()
I have not been able to reproduce this exception for other columns than updateDate and createDate.
First, I reported it as bug (https://github.com/umbraco/Umbraco-CMS/issues/8012) but the problem could not be reproduced in a clean installation. So, it's something wrong in my database.
I was given the hint making sure that the umbracoContentVersion table had a versionDate column, and that exists indeed.
Does anybody have a clue how to solve or throubleshoot this?
I don't really have the answer for you, but here is a weird observation... when the MemberRepository applies which field to order by - UpdateDate + CreateDate are referring to different fields in different database tables eg
if (ordering.OrderBy.InvariantEquals("updateDate"))
return SqlSyntax.GetFieldName<ContentVersionDto>(x => x.VersionDate);
if (ordering.OrderBy.InvariantEquals("createDate"))
return SqlSyntax.GetFieldName<NodeDto>(x => x.CreateDate);
Hence when ordering by updateDate the advice was to check if the ContentVersion table has a versionDate field...
However when you order by createDate it's actually using the createDate field on the umbracoNode database table...
So it sounds really unlikely that you would have a problem with both columns missing...!
To further think about the problem... if you create a ListView in the content section of the site, and choose to order by these two dates do you get the same error?
The Member Repository is actually calling the ContentRepository's GetPage method, so if the problem lay with missing database columns... I'd expect you to get the same error here!
What could be going wrong?
Possibly the ownership of those SQL tables isn't accessible to the SQL User querying them? which is why you see the fields visually but the code does not...
or
if you have upgraded to 8.6.1 maybe the db is ok, but there is some older dlls that are skewing the query logic - in which case I'd download a vanilla brand new version of 8.6.1 and point it at the db to see if the problem persists?
I tried to create a new data type with a ListView editor and I was able to order by the create and update column without any error. So it's just the member administration that fails.
I also copied the database to a SQL Server 2016 machine (Task->Generate Scripts in SSMS), changed the db connection and the problem was gone!
Then I copied the database back to my machine but the error persisted.
So it's definitely something in the database. Maybe a compatibility issue with SQL Server 2019?
I have the same issue. My site has been upgraded throuhg several 6 and 7 versions and then migrated to v 8.7.0.
When comparing DB from clean v8.7.0 install with the migrated the only difference I can see is that the clean install has a column in umbracoContentVersion called "versionDate", while in my migrated DB it is called "VersionDate" (note the capital V).
I tried renaming the column in the migrated db to versionDate, but the error persisted.
May be related to nuCache maybe?
And why is it even looking for versionDate when we try to sort by updateDate? - Can there be a bug in the backoffice view it self?
Column ordering in Members ListView error
Hi,
I have an issue with an Umbraco 8.6.1 project.
My "List View - Members" data type is configured for displaying the columns username, updateDate and createDate.
When I try to sort the list under "Members" by updateDate or createDate, I get an error:
I have not been able to reproduce this exception for other columns than updateDate and createDate.
First, I reported it as bug (https://github.com/umbraco/Umbraco-CMS/issues/8012) but the problem could not be reproduced in a clean installation. So, it's something wrong in my database.
I was given the hint making sure that the umbracoContentVersion table had a versionDate column, and that exists indeed.
Does anybody have a clue how to solve or throubleshoot this?
Hi fedemarcgm
I don't really have the answer for you, but here is a weird observation... when the MemberRepository applies which field to order by - UpdateDate + CreateDate are referring to different fields in different database tables eg
Hence when ordering by updateDate the advice was to check if the ContentVersion table has a versionDate field...
However when you order by createDate it's actually using the createDate field on the umbracoNode database table...
So it sounds really unlikely that you would have a problem with both columns missing...!
To further think about the problem... if you create a ListView in the content section of the site, and choose to order by these two dates do you get the same error?
The Member Repository is actually calling the ContentRepository's GetPage method, so if the problem lay with missing database columns... I'd expect you to get the same error here!
What could be going wrong? Possibly the ownership of those SQL tables isn't accessible to the SQL User querying them? which is why you see the fields visually but the code does not...
or
if you have upgraded to 8.6.1 maybe the db is ok, but there is some older dlls that are skewing the query logic - in which case I'd download a vanilla brand new version of 8.6.1 and point it at the db to see if the problem persists?
weird one though!
regards
Marc
Thanks for the tips!
I tried to create a new data type with a ListView editor and I was able to order by the create and update column without any error. So it's just the member administration that fails.
I also copied the database to a SQL Server 2016 machine (Task->Generate Scripts in SSMS), changed the db connection and the problem was gone!
Then I copied the database back to my machine but the error persisted.
So it's definitely something in the database. Maybe a compatibility issue with SQL Server 2019?
I have the same issue. My site has been upgraded throuhg several 6 and 7 versions and then migrated to v 8.7.0.
When comparing DB from clean v8.7.0 install with the migrated the only difference I can see is that the clean install has a column in umbracoContentVersion called "versionDate", while in my migrated DB it is called "VersionDate" (note the capital V).
I tried renaming the column in the migrated db to versionDate, but the error persisted.
May be related to nuCache maybe?
And why is it even looking for versionDate when we try to sort by updateDate? - Can there be a bug in the backoffice view it self?
is working on a reply...