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.

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.

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

  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
commented: Yes I have done this. Thank you! +1

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?

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).

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.