ASP.NET - SQLDataSource - FilterExpression - Problem with Empty Strings
In this test application, I am filtering by two ControlParameters. As you can see in the first codeblock, both parameters have a default value of %. This code works great. However, in the second code block, please note that the Default Values are eliminated. The second code block works as long as neither Textbox's text is blank. For some reason, when .NET evaluates the filter expression and inserts "" into the expression, it seems to bug out and case none of my results to be filtered.
I am able to verify this behavior by setting the defaultvalue="" for each ControlParameter.
Obviously I have found a workaround, but I would like to know why the FilterExpression breaks down when an empty string is provided.
Any ideas?
Code Block 1:
<asp:SqlDataSource ID="Customer_Data" runat="server"
ConnectionString='<%$ ConnectionStrings:connectionString %>'
SelectCommand="SELECT Station, StoreFront, CustomerID as CustID FROM vStations"
ProviderName="System.Data.SqlClient"
FilterExpression="StoreFront LIKE '%{0}%' and CustID LIKE '{1}%'"
EnableCaching="True"
CacheDuration="60">
<FilterParameters>
<asp:ControlParameter ControlID="TextBox1" PropertyName="Text" DefaultValue="%" />
<asp:ControlParameter ControlID="TextBox2" PropertyName="Text" DefaultValue="%" />
</FilterParameters>
</asp:SqlDataSource>
Code Block 2:
<asp:SqlDataSource ID="Customer_Data" runat="server"
ConnectionString='<%$ ConnectionStrings:connectionString %>'
SelectCommand="SELECT Station, StoreFront, CustomerID a开发者_开发技巧s CustID FROM vStations"
ProviderName="System.Data.SqlClient"
FilterExpression="StoreFront LIKE '%{0}%' and CustID LIKE '{1}%'"
EnableCaching="True"
CacheDuration="60">
<FilterParameters>
<asp:ControlParameter ControlID="TextBox1" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox2" PropertyName="Text" />
</FilterParameters>
</asp:SqlDataSource>
Try setting the ConvertEmptyStringToNull
property of the two <asp:ControlParameter>
's to false. They're true
by default and I'm guessing the nulls are throwing things off.
精彩评论