filtering record using comboboxes with additional item

[AngelicOne]

I have two combobox, which is 'names' and 'types' and it's being populated by a dataset. It contains an additional item "-- ALL --" to indicate that all items of a particular field are being shown. I have an SQL select statement to filter the records according to the selected item of the two combobox. I used a select statement to bind the records to a datagridview with regards to the selected items.

SELECT * FROM table where name = ddlnameselecteditem AND type = ddltypeselecteditem

The problem now is when I select a name from the names combobox and types combobox has a selection of -- ALL --, there would be no return records because there's no such thing as '-- ALL --' record in column 'types' i'm querying. I need to display rows of the selected 'name' while still showing all the 'types'. How could I achieve this? I think I need to avoid the '-- ALL --' item from the combobox. I'm quite confused, really.

[Momerath]

You'll need four select statements and some if/else statements. I'd make sure the -- ALL -- always had an index of 0 for ease of use.

[AngelicOne]

Four select statement? Could you please elaborate those and the if/else statement

[Momerath]

1. SELECT * FROM table
2. SELECT * FROM table WHERE type = @ddltypeselecteditem
3. SELECT * FROM table WHERE name = @ddlnameselecteditem
4. SELECT * FROM table WHERE name = @ddlnameselecteditem AND type = @ddltypeselecteditem

1) Both combo boxes are -- ALL --
2) Name is -- ALL -- and type is something else
3) Type is -- ALL -- and Name is something else
4) Both Name and Type are not -- ALL --

So let's say you've forced -- ALL -- to have an index of 0 in each combobox:

if (name.SelectedIndex == 0 && type.SelectedIndex == 0) {
    // use sql command 1
} else if (name.SelectedIndex == 0) {
    // use sql command 2
    // add parameter to command object
} else if (type.SelectedIndex == 0) {
    // use sql command 3
    // add parameter to command object
} else {
    // use sql command 4
    // add both parameters to command object
}

// execute sql command and get results here

[AngelicOne]

So I'll have the same number of stored procedure as the select statements? Actually I will have three or might be four combobox. Then the if/else condition and select statements will be quite more?

[Momerath]

Yes, it gets more complicated the more you add. At some point I'd start to think about building the command dynamically (adding a WHERE clause if needed and the various comparisons).

[AngelicOne]

Is it fine to have over 10 stored procedures of select statement for one function that is filter?

[Momerath]

It is, but it's better to dynamically build the statement appending what you need to the basic select command.