filter syntax where some items may or may not be selected

Question

I've got 3 dropdown boxes on my asp.net form: Country, State and City. I want to use these to filter my data. However, I also want the user to have the ability to not use the filters, or not use all the filters.

For instance, they may not want to filter at all. Or they may only want to filter by Country. Or only by Country/State. Or, if it's not the US, then by Country/City.

I'm stuck on how to write this. Right now I have:

 SqlDataAdapter adapter = new SqlDataAdapter("Select * from [MOS_Role] where [Country_ID] = @CoID AND [State_ID] = @StID AND [City_ID] = @CiID ORDER BY [Role] ASC", con2);
 adapter.SelectCommand.Parameters.AddWithValue("@CoID", Convert.ToInt32(ddlCountry.SelectedValue));
 adapter.SelectCommand.Parameters.AddWithValue("@StID", Convert.ToInt32(ddlState.SelectedValue));
 adapter.SelectCommand.Parameters.AddWithValue("@CiID", Convert.ToInt32(ddlCity.SelectedValue));

but that doesn't work when you leave a dropdown unselected.

Answer 1

Here is the query which will help you

Select * from [MOS_Role]
where ([Country_ID] = @CoID OR @CoID IS NULL)
AND ([State_ID] = @StID OR @StID IS NULL)
AND ([City_ID] = @CiID OR @CiID IS NULL)
ORDER BY [Role] ASC

You should pass as parameter selected value or null if not selected.

Answer 2

You need to check if it's empty first, then add. Do this for each AddWithValue call:

if (!string.IsNullOrWhiteSpace(ddlCountry.SelectedValue))
    adapter.SelectCommand.Parameters.AddWithValue("@CoID", Convert.ToInt32(ddlCountry.SelectedValue));