Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set SqlDataSource parameter to null

Tags:

asp.net

How do you set a parameter passed to a SqlDataSource to be null? I have the code below where the parameter "@var1" is set from a TextBox. However, if that value is not defined I need to set "@var1" to be null so that the coalesce returns true.

Is there a way to do this or am I going about it the wrong way?

I've also tried setting the DefaultValue="null" and dsABC.SelectParameters["@var1"]=null which both lead to "Input string was not in a correct format" errors.

   <asp:SqlDataSource ID="dsABC" runat="server" ConnectionString="<%$ ConnectionStrings:abc123 %>" 
   SelectCommand=" SELECT   col1, col2, col3                        
                   FROM table1 as T1
                   WHERE   COALESCE(@var1, T.ID) = T.ID">
     <SelectParameters>
        <asp:ControlParameter Name="var1" ControlID="inputA" PropertyName="Value" DefaultValue="0" Type="Int16" />
     </SelectParameters>  
   </asp:SqlDataSource>
like image 237
s303 Avatar asked Feb 29 '16 19:02

s303


1 Answers

Add the ConvertEmptyStringToNull and the CancelSelectOnNullParameter attributes. The first sets the parameter to null when empty, the second lets the query run (default behavior is to not run a query with a null parameter).

       <SelectParameters>
            <asp:ControlParameter Name="var1" ControlID="inputA" PropertyName="Value" DefaultValue="0" Type="Int16"
 ConvertEmptyStringToNull="true" 
CancelSelectOnNullParameter="false"  />
         </SelectParameters>  
like image 56
dbugger Avatar answered Nov 09 '22 16:11

dbugger