Populate SQL Select Query depend on DropDown List

Hi,

I am trying to do a seach mechanizm. I have a page that has 3 dropdown controls. Here is what I try to accomplish

string Agency = ddlAgency.SelectedValue;
string Location = ddlBorough.SelectedValue;
string Type = ddlType.SelectedValue;

string strSql = "SELECT * FROM Projects";

 

By these I get the values of the drop down lists, I start building SQL statment like this and not sure if this correct.

  if (Agency == null || Location == null)
        {
            strSql = strSql + "ProjectTypeID=" + Type;
        }



        if (Agency != null)
        {
            strSql = strSql + " AgencyID=" + Agency;
        }
        else if (Agency == null)
        {
            strSql = strSql + " LocationID=" + Location;
        }
        if (int.Parse(Agency) == 0 && Agency == null)
        {
            strSql = strSql + " LocationID=" + Location;
        }




        if (Location != null)
        {
            strSql = strSql + " AND LocationID=" + Location;
        }
        if (Location == null)
        {
            strSql = strSql + " ProjectTypeID=" + Type;
        }



        if (Type != null)
        {
            strSql = strSql + " AND ProjectTypeID=" + Type;
        }
        if (Type == null)
        {
            strSql = strSql + " AND AgencyID=" + Agency;
        }

 

I cannot put WHERE clause on the main SQL sentence. What i am trying to do is a global search. SQL needs to be dynamic. If Agency is not selected Location should come first like this

"SELECT * FROM Projects WHERE LocationID="+Location

and same thing for Type. Can someone please help me with this. I spent a lot of time on this. Could not get a result. Thanks for the helps.

0
marijuana
5/4/2009 7:20:06 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
584 Views

Similar Articles

[PageSpeed] 41

You can add a dummy where like this:

string strSql = "SELECT * FROM Projects WHERE 1 = 1 ";

and add an 'AND' in the others:

        if (Agency == null || Location == null)
        {
            strSql = strSql + " AND ProjectTypeID=" + Type;
        }



        if (Agency != null)
        {
            strSql = strSql + " AND AgencyID=" + Agency;
        }
        else if (Agency == null)
        {
            strSql = strSql + " AND LocationID=" + Location;
        }
        if (int.Parse(Agency) == 0 && Agency == null)
        {
            strSql = strSql + " AND LocationID=" + Location;
        }

 Good Luck

 


Utomo IT Family
0
mutomo
5/4/2009 9:05:41 PM

This seems to be one of the most common questions on this forum. I believe in your case you need something like

select * from Projects where (@Agency IS NULL or Agency = @Agency) AND (@Location IS NULL or Location = @Location) AND (@Type is NULL or Type = @Type).

Now the only remaining thing is to set your parameters to be selected values of the drop downs. You don't even have to set them in code, you can create SQLDataSource and specify control parameters for your select parameters.

How exactly your page is currently set up? Do you use SQLDataSource or direct code?


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
5/4/2009 9:10:45 PM
Reply: