I have drop down control ddlType which SelectedValue is used in data source dsOrders.
First item in dropdown list is "Any" with value "" (empty string), other items are appended (AppendDataBoundItems="Tru
e") from dsType datasource.
DropDown renders to (from source code):
<select name="ddlType" id="ddlType">
<option selected="selected" value="">Any</option>
<option value="1">Mail Out</option>
dsOrders uses selected value in SQL statement: SELECT * FROM tblWOOrder WHERE OrderType = ISNULL(@typestr, OrderType)
The problem is dsOrders datasource is not working when I select "Any" in the drop down. SelectedValue of item "Any" is empty string which should be converted to null and as a result following statement should be executed: SELECT * FROM tblWOOrder WHERE OrderType = ISNULL(null, OrderType). It seems to me that ConvertEmptyStringToNull doesn't work with drop down list control.
For testing, I changed SQL in dsOrders to SELECT * FROM tblWOOrder WHERE OrderType = ISNULL(null, OrderType) (all records should be showing and as you can see I am not even using the parameter), but as soon as I select "Any" on the list, gvOrders GridView doesn't produce any result. If I select some other item, GridView shows all the records as it is supposed.
So, what I am doing wrong, and is there any way around this problem?
I tested this with TextBox control, and if value is empty string, it would be converted to null and everything would work fine.
<asp:GridView ID="gvOrders" runat="server" DataSourceID="dsOrders">
<asp:SqlDataSource ID="dsType" runat="server" ConnectionString="<%$ ConnectionStrings:strCnn %>"
SelectCommand="SELECT * FROM tblWOType ORDER BY TypeName"></asp:SqlDataSource>
<asp:SqlDataSource ID="dsOrders" runat="server" ConnectionString="<%$ ConnectionStrings:strCnn %>"
SelectCommand="SELECT * FROM tblWOOrder WHERE OrderType = ISNULL(@typestr, OrderType)" OnSelecting="dsOrders_Selecting">
<asp:ControlParameter Type="String" ControlID="ddlType" Name="typestr" PropertyName="SelectedValue" ConvertEmptyStringToNull = "true" />