asp.net web application, using sqldatasource for a gridview datasource, eight
dropdownlists used as sources for @variables in the sqldatasource select
command. All dropdownlists use 0 as a "show all" default, all other
dropdownlist values are int16s:

0 = Show All
1 = Item 1
2 = Item 2
etc.

SQLDataSource Select Command is simple:
Select * from Table1 where Field1 = @DropDownList1 and Field2 =
@DropDownList2 order by SomeField ASC

It's simple to set this type of select statement up when using strings in
the Where part of the statement using like/%, but I have had no luck in
finding the equivalent numeric wildcard to use when the dropdownlist value is
0 - Show All. The table has no null values in the fields being queried, so
using Coalecse function doesn't help much. Any ideas?

thanks,
Sue

You can use a filter like this:

SELECT ...
FROM ...
WHERE (field1 = @DropDownList1 OR @DropDownList1 = 0)
AND (field2 = @DropDownList2 OR @DropDownList2 = 0)
ORDER BY ...;


--
Plamen Ratchev
http://www.SQLStudio.com

I want to make sure I'm not misunderstanding this. If the DropDownList1
returns a value of 0, and the DropDownList2 returns a value of 1 (as
examples), would the select statement evaluate to "Select...From...WHERE
(field1 = 0) and (field2 = 1)"? If so, this won't give me the results I'm
looking for. There are no zero's in field1 in the database.

If this were an alphanumeric field, I'd set up the DropDownLists to contain
'' = Show All
'a' = Ends with 'a'
'b' = Ends with 'b'
....

The select statement would then be "SELECT * FROM Table 1 where (field1 like
'%' + @DropDownList) ORDER BY..."

Am I understanding this correctly?

thanks!
Sue

If @DropDownList1 = 0 then it does not matter what is the value for field1 (so it is not compared to 0). If
@DropDownList1 <> 0 then field1 will be compared to the value of @DropDownList1.

--
Plamen Ratchev
http://www.SQLStudio.com

Did you try it??? If @DropDownList1 = 0 then the where clause doesn't care
what the value of field1 is.





On 10/5/09 10:23 PM, in article
5AA29E97-E850-4681-A83F-63626F12B362...soft (DOT) com, "Sue"
<Sue (AT) ojd (DOT) state.or.us> wrote:

Plamen and Aaron,

Thank you both - I was suspecting that I wasn't quite understanding how the
example you gave evaluated. Can't test it until I get to work tomorrow, but I
suspect the solution you provided will solve my coding problem here and in a
few other places I've been working on. Thank you for your replies!

Sue

"Plamen Ratchev" wrote:

Think outside the box. Suppose I'm supplying a DeptID and since it is ALWAYS a positive integer, I'll use 0 (zero) as my wildcard flag:

WHERE ...
AND (DeptID >= CASE @DeptID WHEN 0 THEN 0 ELSE @DeptID END
AND DeptID <= CASE @DeptID WHEN 0 THEN 2147483647 ELSE @DeptID END)

You'll have to study it a bit and figure out why it works. This same technique can be adapted to any numeric type.