[ASP.net] Return all values from table if query string is empty

Question

Ok...so I have a page that lists products from a table based on the querystring.

So if I say foo.aspx?fam=1 all the products from family 1 will be listed.

How can I make my code list all the values if query string is empty?

My SQL command would have to be different...can't really see how I can do this.

<asp:SqlDataSource ID="ds_produtos" runat="server" 
   ConnectionString="<%$ ConnectionStrings:LocalSqlServer2 %>" 

    SelectCommand="SELECT DISTINCT [IdProduct], [Name], [Description], [IdFamily], [Price],  [Stock] FROM [Product] WHERE ([IdFamily] = @IdFamily )">
    <SelectParameters>
        <asp:QueryStringParameter Name="IdFamily" QueryStringField="fam" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

Answer 1

Change your query to

SELECT DISTINCT [IdProduct], [Name], [Description], [IdFamily], [Price], [Stock] FROM [Product] WHERE ([IdFamily] = @IdFamily OR @IdFamily Is NULL)

and pass null when there is no value specified for fam.

Answer 2

Set a default value for the property so that it has a value when you don't pass any value to the page:

<asp:QueryStringParameter Name="IdFamily" QueryStringField="fam" Type="Int32" DefaultValue="-1" />

Use the default value in the query:

SelectCommand="select IdProduct, [Name], Description, IdFamily, Price, Stock from Product where IdFamily = @IdFamily or @IdFamily = -1"