Set SqlDataSource parameter to null

Question

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>

dbugger · Accepted Answer · 2016-02-29 19:43:30Z

8

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>

edited Feb 29, 2016 at 19:43

answered Feb 29, 2016 at 19:17

dbugger

16.6k10 gold badges34 silver badges36 bronze badges

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

4 Comments

s303 Over a year ago

Thanks for the tip dbugger. However, by setting this the query never seems to execute. What I've done in the code behind is set the value of inputA to string.empty. If I do a trace in Sql Server Profiler the query never runs. If I set it to an integer it will run correct. Any ideas?

dbugger Over a year ago

@s303, forgot one little bit, the CancelSelectOnNullParameter attribute. Edited the answer.

mmcfly Over a year ago

Good answer, except I believe the CancelSelectOnNullParameter="False" needs to go up in the <asp:SqlDataSource> root node.

JuSTMOnIcAjUSTmONiCAJusTMoNICa Over a year ago

FWIW this still didn't work for me until I added EnableViewState="False" to the SqlDataSource tag (found on MSDN forums if I remember right).

Collectives™ on Stack Overflow

Set SqlDataSource parameter to null

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related