Using Parameters In SQL Query

Hi all,

I am trying to write an sql query in my web application to select some records from the database using parameters. I use SqlClient for that. The query has a WHERE clause where its supposed to values from the various dropdownlists that I have in my application. I bind the SelectedValue from the dropdownlists to the appropriate parameters and the SQL query does not work if all the conditions are satified in the WHERE clause.

Can I have an SQL query where I can provide all the parameters in the WHERE clause (with AND conditions) and it takes only those values where there is some value from the dropdownlist and ignore the rest of the parameters.

This is what I am trying to do:  private DataSet GetSalesDataBas()
{
DataSet ds = new DataSet();
SqlConnection connection = new SqlConnection("...");
SqlCommand command = connection.CreateCommand();

SqlParameter prm_1 = new SqlParameter();
SqlParameter prm_2 = new SqlParameter();
SqlParameter prm_3 = new SqlParameter();
SqlParameter prm_4 = new SqlParameter();
SqlParameter prm_5 = new SqlParameter();
SqlParameter prm_6 = new SqlParameter();
SqlParameter prm_7 = new SqlParameter();

prm_1.ParameterName = "@pNum";
prm_2.ParameterName = "@sNum";
prm_3.ParameterName = "@paramLoc";
prm_4.ParameterName = "@paramDist";
prm_5.ParameterName = "@paramPNum";
prm_6.ParameterName = "@paramDesc";
prm_7.ParameterName = "@paramBuild";

prm_1.Value = TextBox2.Text.Trim();
prm_2.Value = TextBox3.Text.Trim();
prm_3.Value = DropDownList15.SelectedValue;
prm_4.Value = DropDownList12.SelectedValue;
prm_5.Value = DropDownList21.SelectedValue;
prm_6.Value = DropDownList13.SelectedValue;
prm_7.Value = DropDownList18.SelectedValue;

command.Parameters.Add(prm_1);
command.Parameters.Add(prm_2);
command.Parameters.Add(prm_3);
command.Parameters.Add(prm_4);
command.Parameters.Add(prm_5);
command.Parameters.Add(prm_6);
command.Parameters.Add(prm_7);

string strWhere = "";

if (DropDownList23.SelectedIndex == 0)//if no sort field selected
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location = @paramLoc" +
" AND m.[District] = @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")";
}
else
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location= @paramLoc" +
" AND m.[District]= @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")" +
" ORDER BY " + DropDownList23.SelectedValue + " " + DropDownList24.SelectedValue;
}

string sqlSalesData = @"SELECT * FROM Main m" + strWhere;
command.CommandText = sqlSalesData;
SqlDataAdapter salesOrderAdapter = new SqlDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}