0

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.

2
Contributors
7
Replies
8
Views
5 Years
Discussion Span
Last Post by Momerath
Featured Replies
  • 1

    [LIST=1] [*]SELECT * FROM table [*]SELECT * FROM table WHERE type = @ddltypeselecteditem [*]SELECT * FROM table WHERE name = @ddlnameselecteditem [*]SELECT * FROM table WHERE name = @ddlnameselecteditem AND type = @ddltypeselecteditem [/LIST] 1) Both combo boxes are -- ALL -- 2) Name is -- ALL -- and type … Read More

  • 1

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

0

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.

0

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

Edited by AngelicOne: n/a

1
  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
Votes + Comments
Yes I have done this. Thank you!
0

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?

1

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

0

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

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.