CODEWITHSUNDEEP
c#asp.nethtml
Ye Kyaw Kyaw Htoo
Ye Kyaw Kyaw Htoo

Reputation: 712

two dropdown lists search

I am new to c#.

I'm currently working on a search function in asp.net with c# using three DropDownLists and a submit button. Let's say, it's a car website.

There are 2 DropDownLists:

  1. Make (for example - Toyota, Nissan, Honda . . .)
  2. Model (for example - Prius, X-trail, Insight . . )

If user choose only the first one (for example - he/she choose 'Toyota' and doesn't choose other)and click submit, it will show all Toyota cars from the database. If user choose both 'Make' and 'Model', it will narrow the search.

If user doesn't choose all dropdownlists, how should I write sql query for that and what if user choose all.

And the items of dropdownlists are directly added from the database. I also don't know how to set the default value text at the top of the dropdownlist.

con.Open(); 
 if (!IsPostBack) 
 { 
     cmd = new SqlCommand("SELECT DISTINCT CarMake FROM Car", con); 
     dr = cmd.ExecuteReader(); 
     while (dr.Read()) 
     { 
        DropDownList1.Items.Add(dr[0].ToString());
     } 
 }

Upvotes: 0

Views: 581

Answers (3)

Jordan Parmer
Jordan Parmer

Reputation: 37254

You essentially want a basic expression builder. Based on the options they choose, you'll build up a list of conditions. Then you'll join them all together and put them with the WHERE clause.

NOTE: You need to be careful when generating SQL from user-input (especially from public web pages). This puts you at risk for SQL-injection. I recommend looking up parameterized SQL and building SQL commands using parameters.

var parts = new List<string>();

if( criteria.Make.HasValue )
{
   parts.Add( string.Format( "make = '{0}'", criteria.Make.Value ) );
}

if( criteria.Model.HasValue )
{
   parts.Add( string.Format( "model = '{0}'", criteria.Model.Value ) );
}

query += string.Join( " AND ", parts );

Upvotes: 0

Joshua Honig
Joshua Honig

Reputation: 13235

Especially when building SQL from a public webform like this you should (I would say MUST) build your queries using parameters, not string concatenation. Otherwise it is a SQL injection attack waiting to happen.

One simple way to handle multiple filter possibilities is to use the Like operator in the base SQL command. If either make or model is not chosen, just include a wild card instead of a make or model name:

var baseQuery = "SELECT * FROM Car " 
                "WHERE MAKE LIKE @MakeFilter AND MODEL LIKE @ModelFilter";
var cmd = new SqlCommand(baseQuery, con);
cmd.Parameters.Add(new SqlParameter( 
    "@MakeFilter", (string)ddlMake.SelectedValue ?? "%"));
cmd.Parameters.Add(new SqlParameter( 
    "@ModelFilter", (string)ddlModel.SelectedValue ?? "%"));

Upvotes: 3

S. Ravi Kiran
S. Ravi Kiran

Reputation: 4303

You can have a generic SQL query assigned to a string like this:

string query = "SELECT * FROM cars";

Based on user's selection from the drop down, add conditions to your SQL query:

if (ddlMake.SelectedIndex != 0)
{
   query = query+" WHERE make='"+ddlMake.SelectedValue+"'"+;
}
if (ddlModel.SelectedIndex != 0)
{
   if(query.Contains("WHERE"))
      query = query + " AND model='"+ddlModel.SelectedValue+"'";
   else
      query = query + " WHERE model='"+ddlModel.SelectedValue+"'";
}

Hope this helps.

Upvotes: 0

Related Questions