SQL statement for unselected fields

Question

I've got a number of drop down lists, which allow the user to select values. The user can also choose to leave some drop downs unselected.

I'm attempting to construct an SQL statement which would show all results even if the drop down lists have not been used. I've tried the following, however no results are returned:

string field1 = null;
string field2 = null;

using (SqlCommand command = new SqlCommand("SELECT * FROM Table WHERE ((@Field1 is null) OR (Field1=@Field1)) AND ((@Field2 is null) OR (Field2=@Field2))", connection))
    {
           command.Parameters.Add(new SqlParameter("@Field1", field1));
           command.Parameters.Add(new SqlParameter("@Field2", field2));
    }

Could anyone suggest any ideas? Ive got too many drop downs to make an SQL statement for each unselected combination.

EDIT:

To clarify, the value of 'Field1' and 'Field2' may be obtained from a drop down list. However, the user may not choose one if he desires. Thus, I'd like to cater the SQL statement for a general case, where if the user does not select anything, all the results would be displayed instead. This can be achieved by writing

WHERE Gender=Gender

in SQL Server, but no results are returning when done through C#.

Answer 1

Your query should be something like:

SELECT * FROM Table WHERE ( (@Field1 is null) or (Field1=@Field1)) AND ((@Field2 is null) or (Field2=@Field2))

Be sure to pass null in for those parameters where nothing was selected...