0

I have 2 combo boxes bound to a data-source, in order to make a search web page in ASP.NET. Both combo boxes are bound to database tables. The data-source determines its SQL SELECT statement based on the values of the combo boxes, so results are filtered. But, I want to include the case where the user doesn't want to set a value in some of the combos and wants to retrieve all database records regarding this combobox. I have included static entries in both combos (e.g. ), but how can I program the datasource, so as when is selected by user, no WHERE statement will be applied? I found this answer a bit useful, but there are security issues and is not so straightforward. Changing SqlDataSource.SelectCommand at runtime breaks pagination

Thanks in advance

Community
  • 1
  • 1
user2419312
  • 3
  • 1

1 Answers1

0

Assuming that the value in the comboboxes when the user selects nothing is -1 you can modify your sql like this:

SELECT * 
FROM TABLE
WHERE ((COLUMN_1 = @PARAM_1) OR @PARAM_1 = -1)
AND   ((COLUMN_2 = @PARAM_2) OR @PARAM_2 = -1)

if you pass -1 as value for both parameters the query will return all the records in the table

Andrea
  • 11,801
  • 17
  • 65
  • 72
  • Thanks a lot. Although I already had found the answer myself, your solution is good. I used the above approach together with a stored procedure. – user2419312 Jun 12 '13 at 18:10