U4-2514 - Checkboxlist prevalues in packages broken

Created by Matthew Jones 16 Jul 2013, 12:00:41 Updated by Matt Barlow 10 Sep 2013, 20:49:18

Relates to: U4-1802

Relates to: U4-2542

I'm having issues when trying to import a package from one Umbraco 6.1.2 instance to another Umbraco 6.1.2 instance. The checkbox prevalues never get populated on my CMS content.

When I look through the log file of the site that I installed the package to I see the following error:

(Sorry if this is in the wrong place, this is the first issue I've submitted).

2013-07-16 12:54:10,522 [114] ERROR umbraco.DataLayer.SqlHelper1[[System.Data.SqlClient.SqlParameter, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]] - [Thread 138] Error executing query Select [value] from cmsDataTypeprevalues where id in (Consultant,Team,District,Field) System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Field'. Invalid column name 'District'. Invalid column name 'Team'. Invalid column name 'Consultant'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 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 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at umbraco.DataLayer.SqlHelpers.SqlServer.SqlServerHelper.ExecuteReader(String commandText, SqlParameter[] parameters) at umbraco.DataLayer.SqlHelper1.ExecuteReader(String commandText, IParameter[] parameters)

Here's some extra information to explain the package file that I've uploaded to reproduce the issue. The package file that I have attached contains the following:

A datatype called "OurWorld Roles List", this is a checkbox list and has 5 prevalues defined:

<DataType Name="OurWorld Roles List" (snip) DatabaseType="Nvarchar">

A document type called Base, this has a property called requiredRoles which is an "OurWorld Roles List".

A document type called SiteRoot, this inherits from Base.

A SiteRoot node called "Our World". This has This is defined in the package as follows:

<SiteRoot (snip) nodeName="Our World" (snip) nodeTypeAlias="SiteRoot"> (snip) (snip)

Our World {code}

As you can see, the requiredRoles are set as "Consultant, Team, District and Field", but when this package gets imported, none of these values are present in the node, and I get the related SQL exception in the umbraco log file.

This appears to be related to this issue (http://issues.umbraco.org/issue/U4-1802) that was listed as fixed in 6.0.1.

=Update: Reopened issue with simple repro steps=

Here are the steps to reproduce the issue.

  1. Download the 6.1.2 Umbraco zip file from this url http://our.umbraco.org/ReleaseDownload?id=77775

  2. Right click on the extracted folder and run it with web matrix, or point IIS at the folder.

  3. Run through the install process and select the "I want to use SQL CE 4, a free, quick-and-simple embedded database" option.

  4. Import the sample package attached to this issue.

  5. Click on the Our World content node and look at the Roles tab. Refer to the images attached to this issue. If there are no boxes ticked like in "Expected (incorrect) result.jpg" then you have successfully recreated the issue. The boxes should be ticked as per "desired result.jpg".

  6. Check the UmbracoTraceLog.txt file, if you have recreated the issue you should see the following lines. You can see from the trace log that our tick boxes were not checked because of a SQL error that matches the values that should have been ticked (i.e. Consultant,Team,District,Field).

2013-07-22 12:35:18,429 [7] ERROR umbraco.DataLayer.SqlHelper`1[[System.Data.SqlServerCe.SqlCeParameter, System.Data.SqlServerCe, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91]] - [Thread 14] Error executing query Select [value] from cmsDataTypeprevalues where id in (Consultant,Team,District,Field) SqlCE4Umbraco.SqlCeProviderException: Error running Reader: SQL Statement: Select [value] from cmsDataTypeprevalues where id in (Consultant,Team,District,Field)

Exception: System.Data.SqlServerCe.SqlCeException (0x80004005): The column name is not valid. [ Node name (if any) = ,Column name = Consultant ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior) at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlCeParameter[] commandParameters) at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlCeParameter[] commandParameters) at SqlCE4Umbraco.SqlCEHelper.ExecuteReader(String commandText, SqlCeParameter[] parameters) at umbraco.DataLayer.SqlHelper`1.ExecuteReader(String commandText, IParameter[] parameters)

6 Attachments

Download Our_World_2_Root_1.0.0.zip

Download UmbracoTraceLog.txt


Matthew Jones 16 Jul 2013, 13:27:22

I've added the package that I was trying to install and the trace log. Please let me know if there's any other information you require to look into the issue.

Matthew Jones 18 Jul 2013, 09:26:39

This is turning into quite a show stopper for us now, because this makes the package utility useless for pushing content to live. All of the checkbox pre values get ignored, which is a fairly big deal.

Sebastiaan Janssen 18 Jul 2013, 14:34:38

I'm afraid I'm unable to reproduce your issue on a clean 6.1.2 site. I installed your package (awesome, thanks for that!) and everything looks fine (see attached screenshots).

The related error you get is interesting and I can fix that but it shouldn't be the cause of your problem, the package installer doesn't touch that method.

Matthew Jones 18 Jul 2013, 14:47:17

Thanks for replying so quickly Sebastiaan, I had been trying to fix this problem for quite a few days before submitting a bug report (even trying a clean Umbraco 6.1.2 install). I'll go away and try a clean install again and then get back to you.

Sebastiaan Janssen 18 Jul 2013, 15:00:01

Not trying to send you away, I just can't help at the moment! :-) Make sure to reopen the issue if you have good steps to repro!

Matthew Jones 22 Jul 2013, 10:34:53

Sebastian, what type of database are you using when trying to replicate our problems? We're using SQL server 2008 R2. I'm currently running tests to see if I can find a reason for you guys not being able to replicate our issue and I have a hunch that it could be down to us using a different type of database to you.

Sebastiaan Janssen 22 Jul 2013, 11:26:20

I'm usually using SQL CE but I checked in SQL2012 as well, just in case. It shouldn't make a difference in this case (and it didn't).

Matthew Jones 22 Jul 2013, 12:00:08

Hi Sebastian, I've reopened the issue along with some simple repro steps. I hope this helps.

Sebastiaan Janssen 22 Jul 2013, 14:24:11

Thanks Matthew, fixed for version 6.1.3 in revision 775892ff5a279cc05751099388c54be009b16626

Matthew Jones 22 Jul 2013, 15:27:24

Thanks Sebastiaan, that's great. I will look forward to 6.1.3 being released then! :-)

Shannon Deminick 23 Jul 2013, 00:53:40

Have re-opened this for seb to review my notes since I think this change will cause unwanted side affects since the change is changing the way that values are persisted to cache, the underlying problem is probably due to the packager/importer to deal with the values differently.

Sebastiaan Janssen 23 Jul 2013, 11:19:24

Okay, I found the actual source of the problem. The Checkboxlist datatype stores the selected Ids on the content and in the database, BUT in the umbraco.config it will store the string values of the selected items. The new PackagingService didn't account for this in the CreateContentFromXml method, so it would store the string values in the database. I've put in a (admittedly hacky..) fix for now that works. Hopefully Shannon or Morten can pretty up and/or seperate out this code a little bit.

Revision: e634dba4ea2fb2e3c53be4b49283ecedf8873c0b

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 6.1.2

Due in version: 6.1.3


Story Points: