CODEWITHSUNDEEP
c#asp.netdatalist
Naveen Zehra
Naveen Zehra

Reputation: 1

Filter products associated with their categories C# ASP.NET

Picture Click here

How to search products by their categories associated with them if the categories links are navigation.

  1. I have created a Category table = category_id, product_category
  2. I have added product_category in the products table as well.
  3. I can add categories through admin panel which is showing perfectly if you look at the screenshot I have attached.

But when I click certain categories it is not showing the products related to that categories, showing literally all the products.

For example, if I click Starters only starters products will show not all the products.

I am using datalist to display products. I have been trying to sort out this from 2 nights and researched a lot but couldn’t find specific to what I need.

Can you please help me with this??

I am using this coding in my products page but it is not working

    con.Open();
        SqlCommand cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
         if (Request.QueryString["category"] ==null)

        {

            cmd.CommandText = "select * from Products";

        }
        else
        {
            cmd.CommandText = "select * from Products where product_category='" + Request.QueryString["category"].ToString() + "'";
        }

        cmd.CommandText = "select * from Products";
        cmd.ExecuteNonQuery();
        DataTable dtt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dtt);

        DataList1.DataBind();

        con.Close();

Upvotes: -1

Views: 2024

Answers (3)

Naveen Zehra
Naveen Zehra

Reputation: 1

I have sorted this out myself. Just for help this is my code and it works for me.

           //This is for displaying PRODUCTS.
            #region
            con.Open();
            SqlCommand cmdp = con.CreateCommand();
            cmdp.CommandType = CommandType.Text;

            var categoryID = Request.QueryString["category"];
            int catId = string.IsNullOrEmpty(categoryID) ? 0 : int.Parse(categoryID);
            if (!string.IsNullOrEmpty(categoryID))
                cmdp.CommandText = " select * from products where [category_id] = " + catId;
            else
                cmdp.CommandText = "select * from products";
            cmdp.ExecuteNonQuery();
            DataTable dttp = new DataTable();
            SqlDataAdapter dap = new SqlDataAdapter(cmdp);
            dap.Fill(dttp);
            Datalist1.DataSource = dttp;
            Datalist1.DataBind();

            con.Close();
            #endregion

Upvotes: 0

Marco
Marco

Reputation: 23937

You have 3 problems:

  1. You are not using SqlParameters (Exploits of a Mom).
  2. You reset your sql query after your else block. This will return all items unfiltered - everytime.
  3. You do not bind your datalist to your datatable. (maybe you set the datasource somewhere else, but it is not visible to us. I'm just listing this for good measure)

Remove cmd.CommandText = "select * from Products"; after your else block. and set the datasource:

if (Request.QueryString["category"] ==null)
{
     cmd.CommandText = "select * from Products";
}
else
{
     //use parameters to prevent SQL injection
     cmd.CommandText = "select * from Products where product_category=@category";
     cmd.Parameters.Add(new SqlParameter("category", Request.QueryString["category"]));
}
//cmd.CommandText = "select * from Products";<--- delete this line
/*.. exeute quesry and load into datatable */
//bind datalist:
DataList1.DataSource = dtt;
DataList1.DataBind();

Upvotes: 1

Gilbert Adjin Frimpong
Gilbert Adjin Frimpong

Reputation: 54

After the if statement, you are still setting the command text to select all products, so comment the cmd.CommandText = "select * from Products"; after the if statement and try again

Upvotes: 0

Related Questions