lundi 29 février 2016

Panel using stored procedure - Code not reacting as expected

I have hit a small issue and hoping someone might be able to assist. I am using a panel - On the page load, it should list all the products as no category has been selected as per stored procedure (this works perfectly).

When a user clicks on a specific category, it should only show the products that have the specific CategoryID. When I run the code in SQL, it works a dream for this part too, so assume the stored procedure is ok.

At "CategoryID = CategoryID" in GetProducts, I get "Warning: Assignment made to same variable; did you mean to assign something else?", however I am following a tutorial video and this works fine. Is there another silly error that is preventing it from working?

I think I have included all the required code - sorry if its a bit overkill!!

Thanks as ever in advance - Jack

pnlCategories

           <asp:Panel ID="pnlCategories" runat="server" ScrollBars="Auto" Height="550px" BorderColor="Black"
                                            BorderStyle="Inset" BorderWidth="1px">
                                            <asp:DataList ID="dlCategories" runat="server" BackColor="White" BorderColor="#CCCCCC"
                                                BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Horizontal"
                                                Width="252px">
                                                <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
                                                <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
                                                <ItemTemplate>
                                                    <asp:LinkButton ID="lblbtnCategory" runat="server" Text='<%# Eval("CategoryName") %>'
                                                        OnClick ="lblbtnCategory_Click" CommandArgument='<%# Eval("CategoryID") %>'></asp:LinkButton>
                                                </ItemTemplate>
                                                <SelectedItemStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
                                            </asp:DataList>
                                        </asp:Panel>        

Coding Behind pnlCategories

    private void GetProducts(int CategoryID)
    {
        ShoppingCart k = new ShoppingCart();
        {
            CategoryID = CategoryID;
        };

        dlProducts.DataSource = null;
        dlProducts.DataSource = k.GetProdcuts();
        dlProducts.DataBind();
    }

    protected void lblbtnCategory_Click(object sender, EventArgs e)
    {
        pnlBasket.Visible = false;
        pnlProducts.Visible = true;
        int CategoryID = Convert.ToInt16((((LinkButton)sender).CommandArgument));
        GetProducts(CategoryID);
    }


    public DataTable GetProdcuts()
    {
        SqlParameter[] parameters = new SqlParameter[1];
        parameters[0] = DataAccess.AddParamater("@CategoryID", CategoryID, System.Data.SqlDbType.Int, 20);
        DataTable dt = DataAccess.ExecuteDTByProcedure("mj350.GetProducts", parameters);
        return dt;
    }

ShoppingCart

    public DataTable GetProdcuts()
    {
        SqlParameter[] parameters = new SqlParameter[1];
        parameters[0] = DataAccess.AddParamater("@CategoryID", CategoryID, System.Data.SqlDbType.Int, 20);
        DataTable dt = DataAccess.ExecuteDTByProcedure("mj350.GetProducts", parameters);
        return dt;
    }

Stored Procedure - GetProducts

    IF( @CategoryID <> 0 )
        BEGIN
            SELECT * FROM    
                           (SELECT 
                           Prod.ProductID,
                           Prod.ProductName,
                           Prod.CategoryID,
                           Prod.ProductPrice,
                           Prod.ProductImageUrl,
                           Prod.ProductStock,
            -- Sum of all the products purchased
             ISNULL(Sum(SBI.ShoppingBasketItemQuantity), 0) AS ProductSold,
                           (Prod.ProductStock - ISNULL(Sum(SBI.ShoppingBasketItemQuantity), 0) ) AS AvailableStock

                    FROM   Product Prod
                           -- Join gets all the specific products which has the specifed category ID
                           INNER JOIN Category Cat ON Cat.CategoryID = Prod.CategoryID
                           LEFT JOIN ShoppingBasketItem SBI ON SBI.ProductID = Prod.ProductID

                           GROUP  BY Prod.ProductID,
                              Prod.ProductName,
                              Prod.CategoryID,
                              Prod.ProductPrice,
                              Prod.ProductImageUrl,
                              Prod.ProductStock) ProductStockTable

           --The important line of code where it specific's the ID of the products it should list        
           WHERE  AvailableStock > 0 AND CategoryID = @CategoryID

        END

      ELSE
        --If no specific Category ID has been selected, it should list for all and therfore trigger the else method as category ID is equal to 0
        BEGIN
            SELECT * FROM    
                           (SELECT 
                           Prod.ProductID,
                           Prod.ProductName,
                           Prod.CategoryID,
                           Prod.ProductPrice,
                           Prod.ProductImageUrl,
                           Prod.ProductStock,
            -- Sum of all the products purchased
             ISNULL(Sum(SBI.ShoppingBasketItemQuantity), 0) AS ProductSold,

                           (Prod.ProductStock - ISNULL(Sum(SBI.ShoppingBasketItemQuantity), 0) ) AS AvailableStock

                    FROM   Product Prod

                           INNER JOIN Category Cat ON Cat.CategoryID = Prod.CategoryID
                           LEFT JOIN ShoppingBasketItem SBI ON SBI.ProductID = Prod.ProductID


                    GROUP  BY Prod.ProductID,
                              Prod.ProductName,
                              Prod.CategoryID,
                              Prod.ProductPrice,
                              Prod.ProductImageUrl,
                              Prod.ProductStock) ProductStockTable

            WHERE  AvailableStock > 0
        END

Aucun commentaire:

Enregistrer un commentaire