CODEWITHSUNDEEP
asp.netsql
the sandman
the sandman

Reputation: 1029

wildcard in SQLDataSource WHERE clause to show all results

How can i have a wildcard in a where clause to where it will show ALL in a certain situation?

I have a dropdown list that contains:

My SQL select statement originally was:

SELECT * FROM [DB_Table] WHERE ([Site] = @Site) AND (RoleType=@RoleType)

The Role Type equals either Staff, Manager or Distributor and is never blank. This works great to filter by each of the 3 roles, but how can i make it so when ALL is selected, it shows all instead?

I believe i need to do something like this, but can't get it right:

SELECT * FROM [DB_Table] WHERE ([Site] = @Site) AND (RoleType=@RoleType OR @RoleType <> NULL)

ANSWER:

I couldn't leave the DDL value for ALL empty, so this is what it looks like and works now.

<asp:ListItem Text="All" Value="All" Selected="True"></asp:ListItem>
<asp:ListItem Text="Staff" Value="Staff"></asp:ListItem>
<asp:ListItem Text="Manager" Value="Manager"></asp:ListItem>
<asp:ListItem Text="Distributor" Value="Distributor"></asp:ListItem>

SELECT * FROM [DB_Table] WHERE ([Site] = @Site) AND (RoleType=@RoleType OR @RoleType = 'ALL')

Upvotes: 0

Views: 672

Answers (1)

Andrew Walters
Andrew Walters

Reputation: 4803

SELECT * FROM [DB_Table] WHERE ([Site] = @Site) AND (RoleType=@RoleType OR @RoleType is NULL)

I think it should be null RoleType for the All drop down choice right?

Upvotes: 3

Related Questions