CODEWITHSUNDEEP
c#asp.net
navio legacy
navio legacy

Reputation: 31

Dynamic query or advanced search filter

I want to create an advanced search query that allows the user to enter as many options as they want.

I'm using in this ASP.NET dropdownlist, checkboxlist and I am using Datalist to show this data.

The problem is that it require the enter or select all filter to work.

Check my code what is wrong and please correct it.(thanks in advance) (please please post your answers with modifying my code for better understanding.)

This is my sending code

protected void searchfilter_Click(object sender, EventArgs e)
{
    Session["price1"] = Convert.ToString(dropdownrs.SelectedItem);
    Session["price2"] = Convert.ToString(dropdownrs2.SelectedItem);
    Session["osx"] = Convert.ToString(dropdownos.SelectedItem);

    List<ListItem> ckbl1 = new List<ListItem>();

    foreach (ListItem li in CheckBoxList_internet.Items)
    {
        if (li.Selected)
        {
            ckbl1.Add(li);
            //Label1.Text += li.Value;
        }
    }

    Session["chkbx1"] = ckbl1;
    Session["phonetype"] = Convert.ToString(dropdown_phone_type.SelectedItem);
    Session["cam"] = Convert.ToString(dropdowncamera.SelectedItem);
    Session["ram"] = Convert.ToString(dropdownram.SelectedItem);
    Session["sim"] = Convert.ToString(dropdownsim.SelectedItem);

    List<ListItem> selection = new List<ListItem>();

    foreach (ListItem li in CheckBoxList1.Items)
    {
        if (li.Selected)
        {
            selection.Add(li);
            //Label1.Text += li.Value;
        }
    }

    Session["chkbx"] = selection;
    Response.Redirect("Searchpage.aspx");
}

On aspx page

public partial class Searchpage : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();

    int price1;
    int price2;

    string osx;
    string chkbox_internet;
    string phonetype;
    string cam;
    string ram;
    string sim;
    string chkbox_brand;
    string q;

    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        con.Open();

        try
        {
            price1 = Convert.ToInt32(Session["price1"]);
            price2 = Convert.ToInt32(Session["price2"]);

            osx = Convert.ToString(Session["osx"]);
            chkbox_brand = Convert.ToString(Session["chkbx1"]);
            phonetype = Convert.ToString(Session["phonetype"]);
            cam = Convert.ToString(Session["cam"]);
            ram = Convert.ToString(Session["phonetype"]);       
            sim = Convert.ToString(Session["sim"]);
            chkbox_brand = Convert.ToString(Session["chkbx"]);

            q = "select * from legacy where price >= " + price1 + " and price <= " + price2 + " and os = '" + osx + "' and connectivity = '" + chkbox_internet + "' and phonetype= '" + phonetype + "' and camera='" + cam + "' and ram='" + ram + "' and sim='" + sim + "' and brand='" + chkbox_brand + "'";
            SqlDataSource1.SelectCommand = q;

            SqlDataSource1.DataBind();
        }
    }
}

Upvotes: 0

Views: 309

Answers (1)

Mario Mazzotti
Mario Mazzotti

Reputation: 11

It looks like your problem is that you are not getting any results, is that right?

The point is, you are creating a query that receives all the filters and making a and between all of them, which means that you are filtering the data by everything, even so the user has selected only one or two of them.

What you are looking for is a query that handles the possibility of not having one of the filters, you may do that by using multiple "ifs" and concatenating the pieces.

I strongly suggest you using command parameters or some ORM to avoid sql injection if this is intended for production usage, concatenating query text will lead to huge security vulnerabilities.

Upvotes: 1

Related Questions