Handling Optional Query Parameters

Question

I have a GridView bound to an DataSource query with parameters tied to form controls.

What is the standard / best-practices way to handle optional query parameters?

Germ · Accepted Answer · 2010-04-09 22:26:59Z

5

Something like.. set the control parameter to -1 then in your query do something like...

Select * From Blah Where
(Somefield = @param or @param = -1)

answered Apr 9, 2010 at 22:15

Germ

6,5704 gold badges28 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

p.campbell · Accepted Answer · 2010-04-09 22:22:30Z

3

On your DataSource, have your ControlParam use the DefaultValue:

<asp:ControlParameter Name="CustomerID" 
    ControlID="DropDownList1" PropertyName="SelectedValue"  
    DefaultValue="-1" />

Then in your SQL statement, check for that same default value from your ControlParameter:

 SELECT * 
 FROM Invoices
 WHERE (CustomerID = @CustomerID OR @CustomerID = -1)

answered Apr 9, 2010 at 22:22

p.campbell

101k70 gold badges263 silver badges326 bronze badges

@pcampbell: great answer, we think alike