error when enabling sidebar

[meer2005]

I get this error when I enable the right sidebar with the Newest Products, Subscribe to mailing list, and recently purchased.

[SqlException (0x80131904): Syntax error converting the varchar value 'False' to a column of data type bit.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212
System.Data.SqlClient.SqlDataReader.Read() +9
CommerceBuilder.Products.ProductDataSource.LoadForCriteria(String sqlCriteria, Int32 maximumRows, Int32 startRowIndex, String sortExpression) +263
Webparts_NewestProductsDialog.Page_PreRender(Object sender, EventArgs e) +78
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
System.Web.UI.Control.OnPreRender(EventArgs e) +2117788
System.Web.UI.Control.PreRenderRecursiveInternal() +86
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.WebControls.WebParts.WebPart.PreRenderRecursiveInternal() +62
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

[sohaib]

Can you post the source of NewestProductsDialog?
Something is wrong in the query created there. If you are passing a value 'False' somewhere try passing 0 instead.

[meer2005]

NewestProductsDialog.ascx.cs:

Code: Select all

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CommerceBuilder.Common;
using CommerceBuilder.DigitalDelivery;
using CommerceBuilder.Products;
using CommerceBuilder.Orders;
using CommerceBuilder.Utility;

public partial class Webparts_NewestProductsDialog : System.Web.UI.UserControl
{
    private string _Caption = "Newest Products";
    private int _MaxItems = 3;
    private string _Orientation = "HORIZONTAL";
    private int _Columns = 3;

    /// <summary>
    /// Default is 3 columns, Only for HORIZONTAL Orientation
    /// </summary>
    [Personalizable(), WebBrowsable()]
    public int Columns
    {
        get { return _Columns; }
        set { 
            _Columns = value;
            if (Orientation == "HORIZONTAL") ProductList.RepeatColumns = Columns;
        }
    }
    

    [Personalizable(), WebBrowsable()]
    public string Orientation
    {
        get
        {
            return _Orientation;
        }
        set
        {
            _Orientation = value.ToUpperInvariant();
            if ((_Orientation != "HORIZONTAL") && (_Orientation != "VERTICAL")) _Orientation = "HORIZONTAL";
            if (_Orientation == "HORIZONTAL")
            {
                ProductList.RepeatColumns = Columns;
                ProductList.RepeatDirection = RepeatDirection.Horizontal;
            }else{
                ProductList.RepeatColumns = 1;
                ProductList.RepeatDirection = RepeatDirection.Vertical;
            }
        }
    }

    [Personalizable(), WebBrowsable()]
    public string Caption
    {
        get { return _Caption; }
        set { _Caption = value; }
    }

    [Personalizable(), WebBrowsable()]
    public int MaxItems
    {
        get { return _MaxItems; }
        set { _MaxItems = value; }
    }

    protected void ProductList_ItemDataBound(object sender, DataListItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            Product product = (Product)e.Item.DataItem;
            Image thumbnail = PageHelper.RecursiveFindControl(e.Item, "Thumbnail") as Image;
            if (thumbnail != null)
            {
                if (!string.IsNullOrEmpty(product.ThumbnailUrl))
                {
                    thumbnail.ImageUrl = product.ThumbnailUrl;
                    thumbnail.Attributes.Add("hspace", "2");
                    thumbnail.Attributes.Add("vspace", "2");
                }
                else
                {
                    thumbnail.Visible = false;
                }
            }
            //TODO: SHOW/HIDE ADD TO CART
        }
    }

    protected void ProductList_ItemCommand(object source, DataListCommandEventArgs e)
    {
        if (e.CommandName == "AddToCart")
        {
            int productId = AlwaysConvert.ToInt(e.CommandArgument);
            BasketItem basketItem = BasketItemDataSource.CreateForProduct(productId, 1);
            if (basketItem != null)
            {
                // DETERMINE IF THE LICENSE AGREEMENT MUST BE REQUESTED
                BasketItemLicenseAgreementCollection basketItemLicenseAgreements = new BasketItemLicenseAgreementCollection(basketItem, LicenseAgreementMode.OnAddToBasket);
                if ((basketItemLicenseAgreements.Count > 0))
                {
                    // THESE AGREEMENTS MUST BE ACCEPTED TO ADD TO BASKET
                    List<BasketItem> basketItems = new List<BasketItem>();
                    basketItems.Add(basketItem);
                    string guidKey = Guid.NewGuid().ToString("N");
                    Cache.Add(guidKey, basketItems, null, System.Web.Caching.Cache.NoAbsoluteExpiration, new TimeSpan(0, 10, 0), System.Web.Caching.CacheItemPriority.NotRemovable, null);
                    string acceptUrl = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes("~/Basket.aspx"));
                    string declineUrl = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(Request.Url.ToString()));
                    Response.Redirect("~/BuyWithAgreement.aspx?Items=" + guidKey + "&AcceptUrl=" + acceptUrl + "&DeclineUrl=" + declineUrl);
                }
                Basket basket = Token.Instance.User.Basket;
                basket.Items.Add(basketItem);
                basket.Save();
                basket.Package();
                basket.Combine();
                Response.Redirect("~/Basket.aspx");
            }
        }
    }

    protected bool ShowAddToCart(object dataItem)
    {
        Product product = (Product)dataItem;
        return ((product.ProductOptions.Count == 0) && (product.KitStatus != KitStatus.Master));
    }

    protected void Page_PreRender(object sender, EventArgs e)
    {
        List<Product> products = new List<Product>(); //= ProductDataSource.GetPopularProducts(this.MaxItems);
        // 01-08-08 JAP:  Modified to restrict list to products that are not locked or prohibited from sale
        ProductCollection _ProductCollection = ProductDataSource.LoadForCriteria("IsProhibited='False' and DisablePurchase='False' and VisibilityId=0", this.MaxItems, 0, "CreatedDate DESC");
        foreach (Product item in _ProductCollection) 
        {
            products.Add(item);
        }

        if (products != null && products.Count > 0)
        {
            CaptionLabel.Text = this.Caption;
            ProductList.DataSource = products;
            ProductList.DataBind();
        }
        else
        {
            this.Visible = false;
        }
    }
}

NewestProductsDialog.ascx:

Code: Select all

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="NewestProductsDialog.ascx.cs" Inherits="Webparts_NewestProductsDialog" %>
<%--    Modified:   12-10-2007
        Author:     Joe Payne
        Company:    Solunar Services LLC
        URL:        http://www.Solunar.com
        Original:   PopularProductsDialog.ascx
        Notes:      Displays latest products added to ac_Products based on CreatedDate field
--%>
<%--
<conlib>
<summary>Display newest products added to database.</summary>
<param name="Caption" default="Newest Products">Possible value can be any string.  Title of the control.</param>
<param name="MaxItems" default="3">Possible value can be any integer greater then zero. Indicates that at maximum how many items can be shown.</param>
<param name="Orientation" default="HORIZONTAL">Possible values are 'HORIZONTAL' or 'VERTICAL'.  Indicates whether the contents will be displayed vertically or horizontally, In case of vertical orientation only one column will be displayed.</param>
<param name="Columns" default="3">Possible value can be any integer greater then zero. Indicates the number of columns, for 'VERTICAL' orientation there will always be a single column.</param>
</conlib>
--%>
<%@ Register Src="~/ConLib/Utility/ProductPrice.ascx" TagName="ProductPrice" TagPrefix="uc" %>
<%@ Register Src="~/ConLib/AddToCartLink.ascx" TagName="AddToCartLink" TagPrefix="uc" %>
<asp:PlaceHolder ID="phContent" runat="server">
<div class="section">
    <div class="header">
        <h2><asp:Localize ID="CaptionLabel" runat="server" Text="Top Sellers" /></h2>
    </div>
    <div class="content">
        <asp:DataList ID="ProductList" runat="server" OnItemDataBound="ProductList_ItemDataBound" OnItemCommand="ProductList_ItemCommand">
            <ItemStyle HorizontalAlign="center" CssClass="ProductItemView" />
            <ItemTemplate>
                <asp:HyperLink ID="ThumbnailLink" runat="server" NavigateUrl='<%# UrlGenerator.GetBrowseUrl(Container.DataItem) %>'>
		            <asp:Image ID="Thumbnail" runat="server" />
		        </asp:HyperLink>
		        <p class="image_desc">
		        <b><a href="<%# Page.ResolveClientUrl(Eval("NavigateUrl").ToString()) %>"><%#Eval("Name")%></a></b><br />
		            <asp:PlaceHolder ID="phSku" runat="server" Visible='<%# (Eval("SKU").ToString().Length > 0) %>'><strong>SKU:</strong>&nbsp;&nbsp;<%#Eval("SKU")%><br /></asp:PlaceHolder>
					<asp:PlaceHolder ID="phPrice" runat="server" Visible='<%# !((bool)Eval("UseVariablePrice")) %>'>
    			    <strong><asp:Literal ID="PriceLabel" runat="server" Text="Price: " /></strong>
    			    <uc:ProductPrice ID="Price" runat="server" Product='<%#Container.DataItem%>' />
					</asp:PlaceHolder>
					<br />
					<div style="margin-top:5px"><uc:AddToCartLink ID="Add2Cart" runat="server" ProductId='<%#Eval("ProductId")%>' /></div>
					<br />
	            </p>
            </ItemTemplate>
        </asp:DataList>
    </div>
</div>
</asp:PlaceHolder>

[sohaib]

Code: Select all

ProductCollection _ProductCollection = ProductDataSource.LoadForCriteria("IsProhibited='False' and DisablePurchase='False' and VisibilityId=0", this.MaxItems, 0, "CreatedDate DESC");

should be

Code: Select all

ProductCollection _ProductCollection = ProductDataSource.LoadForCriteria("IsProhibited=0 and DisablePurchase=0 and VisibilityId=0", this.MaxItems, 0, "CreatedDate DESC");