Invalid column name - When order / where clause applied to filter.

Shane Harris asked on September 18, 2015 03:24

I'm trying to add a filter to a document list. I've modified my code from a version 6 install to run under 8 and it seems fine until the filter is applied. Looking that the error it seems that generated SQL is wrong. It's putting "Insurer AS [CMS_O1]" into the sql, when insurer is already defined earlier in the sql. I've double triple checked my code and I can't figure this out, anyone else having issues?

Code is below: 

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="CustomWordingsFilter.ascx.cs" Inherits="CustomWordingsFilter" %>
<br /> 
<table cellpadding="2">
<tr>
  <td>
    <cms:LocalizedLabel ID="lblOrder" runat="server" Text="Order by" DisplayColon="true">
    </cms:LocalizedLabel>
  </td>
  <td>
    <cms:LocalizedDropDownList ID="drpOrder" runat="server" Width="180" AutoPostBack="true">
    </cms:LocalizedDropDownList>
  </td>
</tr>
<tr runat="server" visible="true">
  <td>
    <cms:LocalizedLabel ID="lblInsurer" runat="server" Text="Insurer" DisplayColon="true" >
    </cms:LocalizedLabel>
  </td>
  <td>
    <cms:LocalizedDropDownList ID="drpInsurer" runat="server" Width="180" AutoPostBack="true">
    </cms:LocalizedDropDownList>
  </td>
</tr>
<tr id="Tr1" runat="server" visible="true">
  <td>
    <cms:LocalizedLabel ID="lblProduct" runat="server" Text="Product:" DisplayColon="true">
    </cms:LocalizedLabel>
  </td>
  <td>
    <cms:LocalizedDropDownList ID="drpProduct" runat="server" Width="180" AutoPostBack="true">
    </cms:LocalizedDropDownList>
  </td>
</tr>
</table>
<br />

and

using System;
using System.Data;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Linq;
 
using CMS.Controls;
using CMS.DataEngine;
using CMS.DocumentEngine;
using CMS.Helpers;
using CMS.Ecommerce;
using CMS.Membership;

public partial class CustomWordingsFilter : CMSAbstractDataFilterControl
{
   
    /// <summary>
    /// Setup the inner child controls
    /// </summary>
    private void SetupControl()
    {
        // Hide the filter if StopProcessing is enabled
        if (this.StopProcessing)
        {
            this.Visible = false;
        }


        // Initialize only if the current request is NOT a postback
        else if (!RequestHelper.IsPostBack())
        {
            // Loads product departments as filtering options
            InitializeDropDowns();


            // Initialize the order by drop-down list
            IntializeOrder();
            
          
        }
    }

 
    /// <summary>
    /// Ensures that the ordering options are loaded into the order by drop-down list
    /// </summary>
    private void IntializeOrder()
    {
        // Initialize options
        this.drpOrder.Items.Add(new ListItem("Insurer - Ascending", "insurerAsc"));
        this.drpOrder.Items.Add(new ListItem("Insurer - Descending", "insurerDesc"));
        this.drpOrder.Items.Add(new ListItem("Product - Ascending", "productAsc"));
        this.drpOrder.Items.Add(new ListItem("Product - Descending", "productDesc"));
        this.drpOrder.Items.Add(new ListItem("Date Changed", "date"));
    }

    /// <summary>
    /// 
    /// </summary>
    private void InitializeDropDowns()
    {

        //string culture = CultureHelper.GetPreferredCulture();                       //CMS.CMSHelper.CMSContext.PreferredCultureCode;
        //string siteName = CMSHttpContext.Current.Application.                                                //CMS.CMSHelper.CMSContext.CurrentSite.SiteName;
        //string className = "custom.PolicyWordings";

       

        TreeProvider provider = new TreeProvider(MembershipContext.AuthenticatedUser);
        DataSet nodes = provider.SelectNodes("NZbrokers.PolicyDocuments");                                                                     //provider.SelectNodes(siteName, "/%", culture, true, className);

        this.drpInsurer.Items.Insert(0, new ListItem("(all)", "##ALL##"));
        this.drpProduct.Items.Insert(0, new ListItem("(all)", "##ALL##"));

        foreach (DataRow dr in nodes.Tables[0].Rows)
        {
            ListItem item = new ListItem(dr["Insurer"].ToString(), dr["Insurer"].ToString());
            if (!this.drpInsurer.Items.Contains(item))
            {
                this.drpInsurer.Items.Add(item);
            }
            ListItem item2 = new ListItem(dr["Product"].ToString(), dr["Product"].ToString());
            if (!this.drpProduct.Items.Contains(item2))
            {
                this.drpProduct.Items.Add(item2);
            }
        }
        SortDDL(ref drpInsurer);
        SortDDL(ref drpProduct);
    }

    private void SortDDL(ref CMS.ExtendedControls.LocalizedDropDownList ddl)
    {
        ListItem[] items = new ListItem[ddl.Items.Count];
        ddl.Items.CopyTo(items, 0);
        ddl.Items.Clear();
        Array.Sort(items, (x, y) => { return x.Text.CompareTo(y.Text); });
        ddl.Items.AddRange(items);
    } 

    /// <summary>
    /// Generates a WHERE condition and ORDER BY clause based on the current filtering selection
    /// </summary>
    private void SetFilter()
    {
        string where = null;
        string order = null;


        // Generate a WHERE condition based on the selected product department
        if (this.drpInsurer.SelectedValue != null)
        {
            if (this.drpInsurer.SelectedValue != "##ALL##")
            {
                where = "Insurer = '" + this.drpInsurer.SelectedValue + "' ";
            }
        }

        if (this.drpProduct.SelectedValue != null)
        {
            if (this.drpProduct.SelectedValue != "##ALL##")
            {
                if (where != null)
                    where = where + " AND ";

                where = where + "Product = '" + this.drpProduct.SelectedValue + "' ";
            }
        }

        // Apply the selected ordering direction        
        if (this.drpOrder.SelectedValue != "")
        {
            switch (this.drpOrder.SelectedValue)
            {
                case "insurerAsc":
                    order = "Insurer";
                    break;


                case "insurerDesc":
                    order = "Insurer Desc";
                    break;


                case "productAsc":
                    order = "Product";
                    break;

                case "productDesc":
                    order = "Product Desc";
                    break;

                case "date":
                    order = "DocumentModifiedWhen Desc";
                    break;
            }
        }

        if (where != null)
        {
            // Set where condition
            this.WhereCondition = where;
        }

        if (order != null)
        {
            // Set orderBy clause
            this.OrderBy = order;
        }
        
        // Raise the filter changed event
        this.RaiseOnFilterChanged();
        
    }

    /// <summary>
    /// Init event handler
    /// </summary>
    protected override void OnInit(EventArgs e)
    {
        // Create child controls
        SetupControl();


        base.OnInit(e);
    }


    private void ReloadProductsDropDown(){
        //string culture = CMS.CMSHelper.CMSContext.PreferredCultureCode;
        //string siteName = CMS.CMSHelper.CMSContext.CurrentSite.SiteName;
        //string className = "custom.PolicyWordings";

        //CMS.TreeEngine.TreeProvider provider = new CMS.TreeEngine.TreeProvider();
        //DataSet nodes = provider.SelectNodes(siteName, "/%", culture, true, className);
        TreeProvider provider = new TreeProvider(MembershipContext.AuthenticatedUser);
        DataSet nodes = provider.SelectNodes("NZbrokers.PolicyDocuments"); 
        this.drpProduct.Items.Insert(0, new ListItem("(all)", "##ALL##"));

        foreach (DataRow dr in nodes.Tables[0].Rows)
        {
            ListItem item = new ListItem(dr["Insurer"].ToString(), dr["Insurer"].ToString());
            ListItem item2 = new ListItem(dr["Product"].ToString(), dr["Product"].ToString());
            if (!this.drpProduct.Items.Contains(item2))
            {
                if (item.Value == drpInsurer.SelectedValue || drpInsurer.SelectedValue == "##ALL##")
                {
                    this.drpProduct.Items.Add(item2);
                }
            }
        }
        SortDDL(ref drpProduct);
    }

    /// <summary>
    /// PreRender event handler
    /// </summary>
    protected override void OnPreRender(EventArgs e)
    {
        // Check if the current request is a postback
        if (RequestHelper.IsPostBack())
        {
            string itemCausedPostback = Request["__EVENTTARGET"];
            if (itemCausedPostback == "plc$lt$zoneContent$pageplaceholder$pageplaceholder$lt$zoneContent$CustomWordingsFilter$filterControl$drpInsurer")
            {
                drpProduct.Items.Clear();
                ReloadProductsDropDown();
            }
            
            // Apply the filter to the displayed data
            SetFilter();
            
        }

        base.OnPreRender(e);
    }

}

Error is:

[SqlException (0x80131904): Invalid column name 'Insurer'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +388
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +688
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4403
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +82
   System.Data.SqlClient.SqlDataReader.get_MetaData() +135
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6665901
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +6667768
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +577
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +21
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +325
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +420
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +278
   CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) +261

[Exception: 
[DataConnection.HandleError]: 

Query: 
SELECT   * FROM 
(
    (SELECT   [NZbDocumentID], [FileName], [FileDescription], [FileAttachment], NULL AS [PolicyDocumentsID], NULL AS [Product], NULL AS [SubProducts], NULL AS [Insurer], NULL AS [AttachmentPDF], NULL AS [AttachmentDOC], NULL AS [Attachment], NULL AS [EffectiveDate], [NodeID], [NodeAliasPath], [NodeName], [NodeAlias], [NodeClassID], [NodeParentID], [NodeLevel], [NodeACLID], [NodeSiteID], [NodeGUID], [NodeOrder], [IsSecuredNode], [NodeCacheMinutes], [NodeSKUID], [NodeDocType], [NodeHeadTags], [NodeBodyElementAttributes], [NodeInheritPageLevels], [RequiresSSL], [NodeLinkedNodeID], [NodeOwner], [NodeCustomData], [NodeGroupID], [NodeLinkedNodeSiteID], [NodeTemplateID], [NodeWireframeTemplateID], [NodeWireframeComment], [NodeTemplateForAllCultures], [NodeInheritPageTemplate], [NodeWireframeInheritPageLevels], [NodeAllowCacheInFileSystem], [NodeHasChildren], [NodeHasLinks], [DocumentID], [DocumentName], [DocumentNamePath], [DocumentModifiedWhen], [DocumentModifiedByUserID], [DocumentForeignKeyValue], [DocumentCreatedByUserID], [DocumentCreatedWhen], [DocumentCheckedOutByUserID], [DocumentCheckedOutWhen], [DocumentCheckedOutVersionHistoryID], [DocumentPublishedVersionHistoryID], [DocumentWorkflowStepID], [DocumentPublishFrom], [DocumentPublishTo], [DocumentUrlPath], [DocumentCulture], [DocumentNodeID], [DocumentPageTitle], [DocumentPageKeyWords], [DocumentPageDescription], [DocumentShowInSiteMap], [DocumentMenuItemHideInNavigation], [DocumentMenuCaption], [DocumentMenuStyle], [DocumentMenuItemImage], [DocumentMenuItemLeftImage], [DocumentMenuItemRightImage], [DocumentPageTemplateID], [DocumentMenuJavascript], [DocumentMenuRedirectUrl], [DocumentUseNamePathForUrlPath], [DocumentStylesheetID], [DocumentContent], [DocumentMenuClass], [DocumentMenuStyleOver], [DocumentMenuClassOver], [DocumentMenuItemImageOver], [DocumentMenuItemLeftImageOver], [DocumentMenuItemRightImageOver], [DocumentMenuStyleHighlighted], [DocumentMenuClassHighlighted], [DocumentMenuItemImageHighlighted], [DocumentMenuItemLeftImageHighlighted], [DocumentMenuItemRightImageHighlighted], [DocumentMenuItemInactive], [DocumentCustomData], [DocumentExtensions], [DocumentCampaign], [DocumentTags], [DocumentTagGroupID], [DocumentWildcardRule], [DocumentWebParts], [DocumentRatingValue], [DocumentRatings], [DocumentPriority], [DocumentType], [DocumentLastPublished], [DocumentUseCustomExtensions], [DocumentGroupWebParts], [DocumentCheckedOutAutomatically], [DocumentTrackConversionName], [DocumentConversionValue], [DocumentSearchExcluded], [DocumentLastVersionName], [DocumentLastVersionNumber], [DocumentIsArchived], [DocumentLastVersionType], [DocumentLastVersionMenuRedirectUrl], [DocumentHash], [DocumentLogVisitActivity], [DocumentGUID], [DocumentWorkflowCycleGUID], [DocumentSitemapSettings], [DocumentIsWaitingForTranslation], [DocumentSKUName], [DocumentSKUDescription], [DocumentSKUShortDescription], [DocumentWorkflowActionStatus], [DocumentMenuRedirectToFirstChild], [Published], [SiteName], [ClassName], [ClassDisplayName], [NodeXML], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 0 AS [CMS_SN], 'cms.document.nzbrokers.document' AS [CMS_T] FROM View_CMS_Tree_Joined_Versions INNER JOIN NZbrokers_Document ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = NZbrokers_Document.NZbDocumentID WHERE ([ClassName] = N'nzbrokers.document') AND ([NodeSiteID] = @NodeSiteID AND (Insurer = 'Ace' ) AND ((NodeAliasPath LIKE N'/Products/Policy-Wordings/BrokerWeb/%') AND [DocumentCulture] = @DocumentCulture)))
UNION ALL
(SELECT   NULL AS [NZbDocumentID], NULL AS [FileName], NULL AS [FileDescription], NULL AS [FileAttachment], [PolicyDocumentsID], [Product], [SubProducts], [Insurer], [AttachmentPDF], [AttachmentDOC], [Attachment], [EffectiveDate], [NodeID], [NodeAliasPath], [NodeName], [NodeAlias], [NodeClassID], [NodeParentID], [NodeLevel], [NodeACLID], [NodeSiteID], [NodeGUID], [NodeOrder], [IsSecuredNode], [NodeCacheMinutes], [NodeSKUID], [NodeDocType], [NodeHeadTags], [NodeBodyElementAttributes], [NodeInheritPageLevels], [RequiresSSL], [NodeLinkedNodeID], [NodeOwner], [NodeCustomData], [NodeGroupID], [NodeLinkedNodeSiteID], [NodeTemplateID], [NodeWireframeTemplateID], [NodeWireframeComment], [NodeTemplateForAllCultures], [NodeInheritPageTemplate], [NodeWireframeInheritPageLevels], [NodeAllowCacheInFileSystem], [NodeHasChildren], [NodeHasLinks], [DocumentID], [DocumentName], [DocumentNamePath], [DocumentModifiedWhen], [DocumentModifiedByUserID], [DocumentForeignKeyValue], [DocumentCreatedByUserID], [DocumentCreatedWhen], [DocumentCheckedOutByUserID], [DocumentCheckedOutWhen], [DocumentCheckedOutVersionHistoryID], [DocumentPublishedVersionHistoryID], [DocumentWorkflowStepID], [DocumentPublishFrom], [DocumentPublishTo], [DocumentUrlPath], [DocumentCulture], [DocumentNodeID], [DocumentPageTitle], [DocumentPageKeyWords], [DocumentPageDescription], [DocumentShowInSiteMap], [DocumentMenuItemHideInNavigation], [DocumentMenuCaption], [DocumentMenuStyle], [DocumentMenuItemImage], [DocumentMenuItemLeftImage], [DocumentMenuItemRightImage], [DocumentPageTemplateID], [DocumentMenuJavascript], [DocumentMenuRedirectUrl], [DocumentUseNamePathForUrlPath], [DocumentStylesheetID], [DocumentContent], [DocumentMenuClass], [DocumentMenuStyleOver], [DocumentMenuClassOver], [DocumentMenuItemImageOver], [DocumentMenuItemLeftImageOver], [DocumentMenuItemRightImageOver], [DocumentMenuStyleHighlighted], [DocumentMenuClassHighlighted], [DocumentMenuItemImageHighlighted], [DocumentMenuItemLeftImageHighlighted], [DocumentMenuItemRightImageHighlighted], [DocumentMenuItemInactive], [DocumentCustomData], [DocumentExtensions], [DocumentCampaign], [DocumentTags], [DocumentTagGroupID], [DocumentWildcardRule], [DocumentWebParts], [DocumentRatingValue], [DocumentRatings], [DocumentPriority], [DocumentType], [DocumentLastPublished], [DocumentUseCustomExtensions], [DocumentGroupWebParts], [DocumentCheckedOutAutomatically], [DocumentTrackConversionName], [DocumentConversionValue], [DocumentSearchExcluded], [DocumentLastVersionName], [DocumentLastVersionNumber], [DocumentIsArchived], [DocumentLastVersionType], [DocumentLastVersionMenuRedirectUrl], [DocumentHash], [DocumentLogVisitActivity], [DocumentGUID], [DocumentWorkflowCycleGUID], [DocumentSitemapSettings], [DocumentIsWaitingForTranslation], [DocumentSKUName], [DocumentSKUDescription], [DocumentSKUShortDescription], [DocumentWorkflowActionStatus], [DocumentMenuRedirectToFirstChild], [Published], [SiteName], [ClassName], [ClassDisplayName], [NodeXML], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 1 AS [CMS_SN], 'cms.document.nzbrokers.policydocuments' AS [CMS_T] FROM View_CMS_Tree_Joined_Versions INNER JOIN NZbrokers_PolicyDocuments ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = NZbrokers_PolicyDocuments.PolicyDocumentsID WHERE ([ClassName] = N'nzbrokers.policydocuments') AND ([NodeSiteID] = @NodeSiteID AND (Insurer = 'Ace' ) AND ((NodeAliasPath LIKE N'/Products/Policy-Wordings/BrokerWeb/%') AND [DocumentCulture] = @DocumentCulture)))
) 
AS SubData ORDER BY CMS_SRN, CMS_SN

Caused exception: 
Invalid column name 'Insurer'.
]
   CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex) +181
   CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) +776
   CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query) +399
   CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query) +401
   CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords) +51
   CMS.DataEngine.DataQueryBase`1.GetDataFromDB() +133
   CMS.DataEngine.DataQueryBase`1.GetData() +140
   CMS.DataEngine.DataQueryBase`1.get_Result() +106
   CMS.DocumentEngine.DocumentQueryProperties.FilterResultsByPermissions(IDataQuery query, Int32& totalRecords) +2009
   CMS.DocumentEngine.DocumentQueryProperties.GetResults(IDataQuery query, Boolean coupledData, Int32& totalRecords) +25
   CMS.DataEngine.MultiQueryBase`2.GetDataFromDB() +78
   CMS.DataEngine.DataQueryBase`1.GetData() +140
   CMS.DataEngine.DataQueryBase`1.get_Result() +106
   CMS.Controls.CMSAbstractDataProperties.GetDataSet(String path, String where, Boolean forceReload, Int32 offset, Int32 maxRecords, Int32& totalRecords) +2109
   CMS.Controls.CMSAbstractDataProperties.LoadData(Object& dataSource, Boolean forceReload, Int32 offset, Int32 maxRecords, Int32& totalRecords) +412
   CMS.Controls.CMSDocumentsDataSource.GetDataSource(Int32 offset, Int32 maxRecords) +360
   CMS.Controls.CMSBaseDataSource.get_DataSource() +77
   CMS.Controls.CMSDataList.ReloadData(Boolean forceReload) +719
   CustomWordingsFilter.OnPreRender(EventArgs e) +304
   System.Web.UI.Control.PreRenderRecursiveInternal() +113
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4297

Recent Answers

Maarten van den Hooven answered on September 18, 2015 11:50 (last edited on September 18, 2015 11:50)

Sorry but I can not see "Insurer AS [CMS_O1]" in the generated SQL. Now it seems that "insurer" column is not present in the "nzbrokers" page type. Did you try to run the SQL in the SQL management studio?

0 votesVote for this answer Mark as a Correct answer

Shane Harris answered on September 18, 2015 13:02 (last edited on September 18, 2015 13:03)

Sorry lets try that again ..... not sure what I did there I only changed the order by drop down. If I removed I Insurer AS [CMS_O1], in two places the sql works in SQL manager

Other than the changes made to reflect API changes it is the same code as I used in V6 .... I can't see anything I'm doing wrong. 

[SqlException (0x80131904): Invalid column name 'Insurer'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +388
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +688
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4403
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +82
   System.Data.SqlClient.SqlDataReader.get_MetaData() +135
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6665901
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +6667768
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +577
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +21
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +325
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +420
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +278
   CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) +261

[Exception: 
[DataConnection.HandleError]: 

Query: 
SELECT   * FROM 
(
    (SELECT   [NZbDocumentID], [FileName], [FileDescription], [FileAttachment], NULL AS [PolicyDocumentsID], NULL AS [Product], NULL AS [SubProducts], NULL AS [Insurer], NULL AS [AttachmentPDF], NULL AS [AttachmentDOC], NULL AS [Attachment], NULL AS [EffectiveDate], [NodeID], [NodeAliasPath], [NodeName], [NodeAlias], [NodeClassID], [NodeParentID], [NodeLevel], [NodeACLID], [NodeSiteID], [NodeGUID], [NodeOrder], [IsSecuredNode], [NodeCacheMinutes], [NodeSKUID], [NodeDocType], [NodeHeadTags], [NodeBodyElementAttributes], [NodeInheritPageLevels], [RequiresSSL], [NodeLinkedNodeID], [NodeOwner], [NodeCustomData], [NodeGroupID], [NodeLinkedNodeSiteID], [NodeTemplateID], [NodeWireframeTemplateID], [NodeWireframeComment], [NodeTemplateForAllCultures], [NodeInheritPageTemplate], [NodeWireframeInheritPageLevels], [NodeAllowCacheInFileSystem], [NodeHasChildren], [NodeHasLinks], [DocumentID], [DocumentName], [DocumentNamePath], [DocumentModifiedWhen], [DocumentModifiedByUserID], [DocumentForeignKeyValue], [DocumentCreatedByUserID], [DocumentCreatedWhen], [DocumentCheckedOutByUserID], [DocumentCheckedOutWhen], [DocumentCheckedOutVersionHistoryID], [DocumentPublishedVersionHistoryID], [DocumentWorkflowStepID], [DocumentPublishFrom], [DocumentPublishTo], [DocumentUrlPath], [DocumentCulture], [DocumentNodeID], [DocumentPageTitle], [DocumentPageKeyWords], [DocumentPageDescription], [DocumentShowInSiteMap], [DocumentMenuItemHideInNavigation], [DocumentMenuCaption], [DocumentMenuStyle], [DocumentMenuItemImage], [DocumentMenuItemLeftImage], [DocumentMenuItemRightImage], [DocumentPageTemplateID], [DocumentMenuJavascript], [DocumentMenuRedirectUrl], [DocumentUseNamePathForUrlPath], [DocumentStylesheetID], [DocumentContent], [DocumentMenuClass], [DocumentMenuStyleOver], [DocumentMenuClassOver], [DocumentMenuItemImageOver], [DocumentMenuItemLeftImageOver], [DocumentMenuItemRightImageOver], [DocumentMenuStyleHighlighted], [DocumentMenuClassHighlighted], [DocumentMenuItemImageHighlighted], [DocumentMenuItemLeftImageHighlighted], [DocumentMenuItemRightImageHighlighted], [DocumentMenuItemInactive], [DocumentCustomData], [DocumentExtensions], [DocumentCampaign], [DocumentTags], [DocumentTagGroupID], [DocumentWildcardRule], [DocumentWebParts], [DocumentRatingValue], [DocumentRatings], [DocumentPriority], [DocumentType], [DocumentLastPublished], [DocumentUseCustomExtensions], [DocumentGroupWebParts], [DocumentCheckedOutAutomatically], [DocumentTrackConversionName], [DocumentConversionValue], [DocumentSearchExcluded], [DocumentLastVersionName], [DocumentLastVersionNumber], [DocumentIsArchived], [DocumentLastVersionType], [DocumentLastVersionMenuRedirectUrl], [DocumentHash], [DocumentLogVisitActivity], [DocumentGUID], [DocumentWorkflowCycleGUID], [DocumentSitemapSettings], [DocumentIsWaitingForTranslation], [DocumentSKUName], [DocumentSKUDescription], [DocumentSKUShortDescription], [DocumentWorkflowActionStatus], [DocumentMenuRedirectToFirstChild], [Published], [SiteName], [ClassName], [ClassDisplayName], Insurer AS [CMS_O1], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 0 AS [CMS_SN], 'cms.document.nzbrokers.document' AS [CMS_T] FROM View_NZbrokers_Document_Joined WHERE [NodeSiteID] = @NodeSiteID AND ([Published] = @Published AND (NodeAliasPath LIKE N'/Products/Policy-Wordings/BrokerWeb/%') AND [DocumentCulture] = @DocumentCulture))
UNION ALL
(SELECT   NULL AS [NZbDocumentID], NULL AS [FileName], NULL AS [FileDescription], NULL AS [FileAttachment], [PolicyDocumentsID], [Product], [SubProducts], [Insurer], [AttachmentPDF], [AttachmentDOC], [Attachment], [EffectiveDate], [NodeID], [NodeAliasPath], [NodeName], [NodeAlias], [NodeClassID], [NodeParentID], [NodeLevel], [NodeACLID], [NodeSiteID], [NodeGUID], [NodeOrder], [IsSecuredNode], [NodeCacheMinutes], [NodeSKUID], [NodeDocType], [NodeHeadTags], [NodeBodyElementAttributes], [NodeInheritPageLevels], [RequiresSSL], [NodeLinkedNodeID], [NodeOwner], [NodeCustomData], [NodeGroupID], [NodeLinkedNodeSiteID], [NodeTemplateID], [NodeWireframeTemplateID], [NodeWireframeComment], [NodeTemplateForAllCultures], [NodeInheritPageTemplate], [NodeWireframeInheritPageLevels], [NodeAllowCacheInFileSystem], [NodeHasChildren], [NodeHasLinks], [DocumentID], [DocumentName], [DocumentNamePath], [DocumentModifiedWhen], [DocumentModifiedByUserID], [DocumentForeignKeyValue], [DocumentCreatedByUserID], [DocumentCreatedWhen], [DocumentCheckedOutByUserID], [DocumentCheckedOutWhen], [DocumentCheckedOutVersionHistoryID], [DocumentPublishedVersionHistoryID], [DocumentWorkflowStepID], [DocumentPublishFrom], [DocumentPublishTo], [DocumentUrlPath], [DocumentCulture], [DocumentNodeID], [DocumentPageTitle], [DocumentPageKeyWords], [DocumentPageDescription], [DocumentShowInSiteMap], [DocumentMenuItemHideInNavigation], [DocumentMenuCaption], [DocumentMenuStyle], [DocumentMenuItemImage], [DocumentMenuItemLeftImage], [DocumentMenuItemRightImage], [DocumentPageTemplateID], [DocumentMenuJavascript], [DocumentMenuRedirectUrl], [DocumentUseNamePathForUrlPath], [DocumentStylesheetID], [DocumentContent], [DocumentMenuClass], [DocumentMenuStyleOver], [DocumentMenuClassOver], [DocumentMenuItemImageOver], [DocumentMenuItemLeftImageOver], [DocumentMenuItemRightImageOver], [DocumentMenuStyleHighlighted], [DocumentMenuClassHighlighted], [DocumentMenuItemImageHighlighted], [DocumentMenuItemLeftImageHighlighted], [DocumentMenuItemRightImageHighlighted], [DocumentMenuItemInactive], [DocumentCustomData], [DocumentExtensions], [DocumentCampaign], [DocumentTags], [DocumentTagGroupID], [DocumentWildcardRule], [DocumentWebParts], [DocumentRatingValue], [DocumentRatings], [DocumentPriority], [DocumentType], [DocumentLastPublished], [DocumentUseCustomExtensions], [DocumentGroupWebParts], [DocumentCheckedOutAutomatically], [DocumentTrackConversionName], [DocumentConversionValue], [DocumentSearchExcluded], [DocumentLastVersionName], [DocumentLastVersionNumber], [DocumentIsArchived], [DocumentLastVersionType], [DocumentLastVersionMenuRedirectUrl], [DocumentHash], [DocumentLogVisitActivity], [DocumentGUID], [DocumentWorkflowCycleGUID], [DocumentSitemapSettings], [DocumentIsWaitingForTranslation], [DocumentSKUName], [DocumentSKUDescription], [DocumentSKUShortDescription], [DocumentWorkflowActionStatus], [DocumentMenuRedirectToFirstChild], [Published], [SiteName], [ClassName], [ClassDisplayName], Insurer AS [CMS_O1], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 1 AS [CMS_SN], 'cms.document.nzbrokers.policydocuments' AS [CMS_T] FROM View_NZbrokers_PolicyDocuments_Joined WHERE [NodeSiteID] = @NodeSiteID AND ([Published] = @Published AND (NodeAliasPath LIKE N'/Products/Policy-Wordings/BrokerWeb/%') AND [DocumentCulture] = @DocumentCulture))
) 
AS SubData ORDER BY CMS_O1 Desc, CMS_SRN, CMS_SN

Caused exception: 
Invalid column name 'Insurer'.
]
   CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex) +181
   CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) +776
   CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query) +399
   CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query) +401
   CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords) +51
   CMS.DataEngine.DataQueryBase`1.GetDataFromDB() +133
   CMS.DataEngine.DataQueryBase`1.GetData() +140
   CMS.DataEngine.DataQueryBase`1.get_Result() +106
   CMS.DocumentEngine.DocumentQueryProperties.FilterResultsByPermissions(IDataQuery query, Int32& totalRecords) +2009
   CMS.DocumentEngine.DocumentQueryProperties.GetResults(IDataQuery query, Boolean coupledData, Int32& totalRecords) +25
   CMS.DataEngine.MultiQueryBase`2.GetDataFromDB() +78
   CMS.DataEngine.DataQueryBase`1.GetData() +140
   CMS.DataEngine.DataQueryBase`1.get_Result() +106
   CMS.Controls.CMSAbstractDataProperties.GetDataSet(String path, String where, Boolean forceReload, Int32 offset, Int32 maxRecords, Int32& totalRecords) +2109
   CMS.Controls.CMSAbstractDataProperties.LoadData(Object& dataSource, Boolean forceReload, Int32 offset, Int32 maxRecords, Int32& totalRecords) +412
   CMS.Controls.CMSDocumentsDataSource.GetDataSource(Int32 offset, Int32 maxRecords) +360
   CMS.Controls.CMSBaseDataSource.get_DataSource() +77
   CMS.Controls.CMSDataList.ReloadData(Boolean forceReload) +719
   CustomWordingsFilter.OnPreRender(EventArgs e) +304
   System.Web.UI.Control.PreRenderRecursiveInternal() +113
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Control.PreRenderRecursiveInternal() +222
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4297
0 votesVote for this answer Mark as a Correct answer

Maarten van den Hooven answered on September 18, 2015 20:24

Strange that Kentico gives an SQL error and the query works in the SQL management studio. Than it is hard to find the problem because I don't have the objects to reproduce it in my instance. Hopefully somebody from Kentico can have a look at this and maybe sees what is wrong. You can always send an mail to support they answer real quickly and offer quality support. If you known what went wrong please let us know here on Devnet.

0 votesVote for this answer Mark as a Correct answer

Shane Harris answered on September 18, 2015 23:46

Well no it doesn't work from cut and paste it works when I remove "Insurer AS CMS_01". It seems to define Insurer twice. I'm not sure why. I'll submit a support request I guess and post back what I find out if anything.

0 votesVote for this answer Mark as a Correct answer

Shane Harris answered on October 6, 2015 22:23

So it turns out this was actually very simple.

I had somehow selected two Page/class types, one of which had no Insurer in it. Changing this to only have the one page type fixed everything. Looking at the error I guess it kind of makes sense. It would have been nice if it gave a friendly error for this though.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.