Total Pageviews

Tuesday, 9 December 2014

Fill the filter area in online store from db

Fill the checkboxlist in the filter area:

IMAGE
----------


HTML
-------
<tr>
         <td>
                                <asp:CheckBoxList ID="brand_cbl" runat="server">
                                </asp:CheckBoxList>
          </td>
</tr>


CODE
--------
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

string con = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

public void show_filter()
    {
        SqlConnection items_con = new SqlConnection(con);

        try
        {
            items_con.Open();
//Call the stored procedure
            SqlCommand cmd = new SqlCommand("select_filter_brand_sp", items_con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ListItem li = new ListItem();
                li.Text = dr["brand"].ToString();
                brand_cbl.Items.Add(li);

            }



        }
        catch (Exception ex)
        {
           Response.Write(ex);
        }
        finally
        {
            items_con.Close();
        }
 
    }


Stored procedure
--------------------
ALTER PROCEDURE select_filter_brand_sp

AS

begin

SELECT DISTINCT brand,
                          (SELECT     COUNT(brand) AS c_b
                            FROM          Items AS x
                            WHERE      (brand = items.brand)) AS No_of_products
FROM         Items
ORDER BY brand

end
RETURN