How do I filter a listview with multi parameters in c# using querystring

ProductList.aspx

<asp:DataList ID="dl_category" runat="server">
       <ItemTemplate>
            <li>
                <a href = "ProductList.aspx?ProdCategory=<%#Eval("Category_Name")%>"><%# Eval("Category_Name")%></a>
            </li>
       </ItemTemplate>
</asp:DataList>

<asp:DataList ID="dl_vendor" runat="server">
       <ItemTemplate>
            <li>
                <a href = "ProductList.aspx?Vendor=<%# Eval("Vendor_Name")%>"><%# Eval("Vendor_Name")%></a>
            </li>
        </ItemTemplate>
</asp:DataList>

Currently I have 2 datalist. One datalist filters the listview by product category while the other datalist filters the listview by vendor name.

ProductList.aspx.cs

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                loadcategory();
                loadvendorname();
                filter();
            }
        }

protected void filter()
        {
            if (Request.QueryString["ProdCategory"] != null)
            {
                string productcategory = Request.QueryString["ProdCategory"];
                string vendorname = null;
                List<Product> filterprodList = new List<Product>();

                conn.Open();
                string queryStr = "select * from products where product_category='" + productcategory + "'";
                SqlCommand cmd = new SqlCommand(queryStr, conn);
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                lv_Product.DataSourceID = null;
                lv_Product.DataSource = dt;
                lv_Product.DataBind();
                conn.Close();
            }

            if (Request.QueryString["Vendor"] != null)

            {
                string productcategory = null;
                string vendorname = Request.QueryString["Vendor"];
                List<Product> filterprodList = new List<Product>();
                filterprodList = aProd.filterProductAll(productcategory, vendorname);
                lv_Product.DataSourceID = null;
                lv_Product.DataSource = filterprodList;
                lv_Product.DataBind();
            }

            if (Request.QueryString["ProdCategory"] != null && Request.QueryString["Vendor"] != null)
            {
                string productcategory = Request.QueryString["ProdCategory"];
                string vendorname = Request.QueryString["Vendor"];
                List<Product> filterprodList = new List<Product>();
                filterprodList = aProd.filterProductAll(productcategory, vendorname);
                lv_Product.DataSourceID = null;
                lv_Product.DataSource = filterprodList;
                lv_Product.DataBind();
            }

However, currently I am only able to filter the listview by one parameter at a time as my current href only accept one parameter at a time (e.g ProductList.aspx?Vendor=<%# Eval(“Vendor_Name”) ) How can I make it so that I can filter 2 or more parameter at one time. Thank you for your time 😀

I hope to be able to get a url of example
(ProductList.aspx?ProdCategory=Category&Vendor=Vendor1)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

One of the often overlooked issues is that the sqlCommand object does NOT need the sql to be built before you add parameters, or in fact change the sql used!!!

So, the goal here should be none, 1, 2, 3 or even 5 parameters passed. And if any parameter is NOT passed, then don’t use or include any criteria for that “lack” of a filter.

Flip the problem the other way around. We ONLY add criteria for parameters passed.

That way, you can extend this code – it will work for 2 or 20 values. In fact since each code stub I have here is “repeating”, then I suppose we could break out the repeating code into a separate routine. For this example, we will 0-3.

This approach also means we handle all the permutations (combinations) of the possible values. We do this as noted by simply ignoring and not including non passed values.

So, your filter code can and should look something like this:

public void MyFilterTest()

{
   string strSQL = "SELECT * FROM products";
   string strWhere = "";

   SqlCommand cmdSQL = new SqlCommand("", new SqlConnection(My.Settings.Test3));

   if (Request.QueryString("ProdCategory") != Null)
    {
    strWhere = "(Product_Catagorory = @Pcata)";
    cmdSQL.Parameters.Add("@pcata", SqlDbType.NVarChar).Value = Request.QueryString("ProdCategory");
    }

if (Request.QueryString("Vendor") != Null)
   {
    if (strWhere != "") strWhere += " AND ";
    strWhere += "(VENDORNAME = @VendName)";
    cmdSQL.Parameters.Add("@VendName", SqlDbType.NVarChar).Value = Request.QueryString("Vendor");
   }

if (Request.QueryString("CompanyName") != Null)
  {
    if (strWhere != "") strWhere += " AND ";
    strWhere += "(CompanyName = @CompName)";
    cmdSQL.Parameters.Add("@CompName", SqlDbType.NVarChar).Value = Request.QueryString("CompanyName");
  }

if (strWhere != "") strSQL += " WHERE " + strWhere;

cmdSQL.CommandText = strSQL;
cmdSQL.Connection.Open();

DataTable MyTable = new DataTable();
MyTable.Load(cmdSQL.ExecuteReader);

lv_Product.DataSource = MyTable;
lv_Product.DataBind();

}

So two tricks:
You don’t have to set in stone the cmdSQL object and parameters ahead of time. The creating and adding of “parameters” can occur 100% independent of the SQL of the command object – they don’t know or care about each other until such time you actually create the sql for the command object – that can occur AFTER adding parameters!

next trick:
We build up the sqlWhere clause. If the previous where had no value, then we do NOT append a ” AND ” clause to the strWhere. So if we have none, or 20 – this works also!
eg this:

if (strWhere != "") strWhere += " AND ";

And yes – I put that code on one line – it is my party, and I’ll cry or put such one liners on one line of code if I want to!!!

So, build up the criteria as you “go along”. Non passed values simply do not become part of the final “where” clause, and thus this will eliminate the “combinations” of all possible values or not. We simply don’t care. As noted, the 3 example code stubs could in fact likely be written as a small function since those 3 code stubs look VERY similar to each other – but regardless – you get the idea. Also note how the sqlCommand object has 3 things:

A connection object (don't need a separate one).
A sql text - we could have in fact used cmdSQL object in place of strSQL
A execute reader - so we only need that one object + a data table - not separate objects


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x