将SqlDataSource参数设置为null [英] Set SqlDataSource parameter to null
问题描述
如何将传递给SqlDataSource的参数设置为null?我在下面的代码中从TextBox设置了参数"@ var1".但是,如果未定义该值,则需要将"@ var1"设置为null,以便
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?
我还尝试设置 DefaultValue ="null"
和 dsABC.SelectParameters ["@ var1"] = null
都导致输入字符串不正确"格式正确"的错误.
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>
推荐答案
添加 ConvertEmptyStringToNull 和 CancelSelectOnNullParameter 属性.第一个将参数设置为空时将其设置为null,第二个将使查询运行(默认行为是不运行具有空参数的查询).
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>
这篇关于将SqlDataSource参数设置为null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!