Click here to Skip to main content
15,940,218 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have repeater control and i am fetching records from database, so i have number of records and i want to filter my result by using dropdowns. I am using ajax. If i select first dropdown then it filter result according to first and again when i select second dropdown then it fetch result according to second only, but i want if one dropdown is already selected then on selecting second dropdown it should filter from previous dropdown result. How to do it...
My search dropdowns are in master page and among these only category dropdown is compulsory to select and result shows in result.aspx page and also my filter dropdowns are in result page.
master page code:
ASP.NET
<asp:DropDownList ID="dd_state" runat="server" CssClass="dropdownstyle2" >
            </asp:DropDownList>

            <asp:TextBox ID="txt_pincode" runat="server" CssClass="dropdownstyle3" value="Pincode" Text="" onKeyUp="numericFilter(this);" maxlength="6"
            onFocus="if(this.value==this.defaultValue) this.value=''; " onBlur="if(this.value=='') this.value=this.defaultValue;"></asp:TextBox>

            <asp:DropDownList ID="dd_category" runat="server" CssClass="dropdownstyle4" 
                AutoPostBack="True" onselectedindexchanged="dd_category_SelectedIndexChanged">
            </asp:DropDownList>

            <asp:DropDownList ID="dd_subcategory" runat="server" CssClass="dropdownstyle5" >
            </asp:DropDownList>

            <asp:Button ID="but_go" runat="server" Text="Go" CssClass="gobuttonstyle" OnClientClick="return validate();" 
                onclick="but_go_Click"/>

C#
    protected void but_go_Click(object sender, EventArgs e)
{
    Session["country"] = 1;
    Session["state"] = dd_state.SelectedValue;
    Session["pincode"]  = txt_pincode.Text;
    Session["category"] = dd_category.SelectedValue;
    Session["subcategory"] = dd_subcategory.SelectedValue;
    Response.Redirect("Results.aspx");
}

Result.aspx.cs
C#
protected void BindRepeaterData()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("sps_searchresult", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@country", 1);
        cmd.Parameters.AddWithValue("@state", state);
        cmd.Parameters.AddWithValue("@pincode", pincode);
        cmd.Parameters.AddWithValue("@category", category);
        cmd.Parameters.AddWithValue("@subcategory", subcategory);
        
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        rep_results.DataSource = ds;
        rep_results.DataBind();
        con.Close();
    }

SQL
ALTER PROCEDURE [dbo].[sps_searchresult]
	@country nvarchar(10),
	@state nvarchar(20),
	@pincode nvarchar(10),
	@category nvarchar(10),
	@subcategory nvarchar(20)
	
AS
BEGIN
	
	if(@state='Select State' and @pincode='Pincode' and @subcategory='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category
	end
	
	if(@state='Select State' and @pincode='Pincode' and @subcategory!='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category and subcategory=@subcategory
	end
	
	if(@state='Select State' and @pincode!='Pincode' and @subcategory='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category and pincode=@pincode
	end
	
	if(@state='Select State' and @pincode!='Pincode' and @subcategory!='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category and pincode=@pincode and subcategory=@subcategory
	end
	
	if(@state!='Select State' and @pincode='Pincode' and @subcategory='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category and state=@state
	end
	
	if(@state!='Select State' and @pincode='Pincode' and @subcategory!='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category and state=@state and subcategory=@subcategory
	end
	
	if(@state!='Select State' and @pincode!='Pincode' and @subcategory='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category and state=@state and pincode=@pincode
	end
	
	if(@state!='Select State' and @pincode!='Pincode' and @subcategory!='Select Sub-Category')
	begin
	select r.*, s.s_name, CASE iam WHEN 1 THEN 'Professional/Business' ELSE 'Individual' End as iamdetail, substring(description,1,200)+'...' AS shortdescription, substring(title,1,40) AS shorttitle from tbl_adregister r INNER JOIN tbl_state s ON r.state = s.s_id 
	where country=@country and category=@category and state=@state and pincode=@pincode and subcategory=@subcategory
	end
	
END

filter dropdowns at result page:
ASP.NET
<asp:DropDownList ID="dd_state" runat="server" AutoPostBack="True"
onselectedindexchanged="dd_state_SelectedIndexChanged" ></asp:DropDownList>
<br/>
<asp:DropDownList ID="dd_category" runat="server" AutoPostBack="True"
onselectedindexchanged="dd_category_SelectedIndexChanged"></asp:DropDownList>
<br/>
<asp:DropDownList ID="dd_subcategory" runat="server" AutoPostBack="True"
onselectedindexchanged="dd_subcategory_SelectedIndexChanged"></asp:DropDownList>
Posted
Updated 5-May-14 21:40pm
v3
Comments
DamithSL 5-May-14 22:18pm    
can you update the question with sps_searchresult code?
Raj Negi 6-May-14 3:32am    
yes sure, but it is not related to filter. This stored procedure work is to give result according to user selects dropdown in masterpage. Once results comes it's work is done. Now i have to write query for filter dropdowns. eg: suppose If user selects dropdown which is category then result should all records of selected category then if user select state...so the result should all records of that category which is in the state selected. hope you understand my requirement.

1 solution

This is not that complex and you are almost in right direct. Now here is the idea,
Like you to search for State and than Pin Code, Now there can be 2 situations:
1. Update result for first and than update for First + second drop down
2. Let user to select First and Than 2nd and than click search to update result.

For both the case you need t pass the two parameters to SQL Query(one for each drop-down). Also you can add check in your stored procedure to check if value for the parameter is supplied or not in case of single Drop-down search.

Let me know if idea isn't clear to you.
 
Share this answer
 
Comments
Raj Negi 6-May-14 4:04am    
thanks for answer but not get it, give some small example. And one more thing these 3 filters are not fixed, i mean it can be 5-6 according to requirement. So give me answer according to it. user can select any of them one, two, three...so on and result should filter according to dropdowns selection. eg: suppose If user selects dropdown which is category then result should all records of selected category then if user select state...so the result should all records of that category which is in the state selected. hope you understand my requirement.
Er. Puneet Goel 6-May-14 11:22am    
Add no of parameter to the query you want to search than add checks like:
if(@a != '')
@Query = @Query + ' and column1 = ' + @a + ''
if(@b != '')
@Query = @Query + ' and column2 = ' + @b + ''

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900