locked
DropDownList.DataBind() aborts when SqlDataSource.SelectParameters[...].DefaultValue is null RRS feed

  • Question

  • User260076833 posted

    Hi,

    I have a SQLDataSource representing a list of cars (fictive) with one parameter:

    <asp:SqlDataSource ID="src_Cars" runat="server"
        ConnectionString='<%$ ConnectionStrings:MyConnectionString %>'
        SelectCommand="SELECT num FROM [cars] WHERE (num NOT IN (SELECT numFROM cars_sold WHERE (num IS NOT NULL)) OR num=@special);"
        <SelectParameters>
            <asp:Parameter Name="special" DbType="String" ConvertEmptyStringToNull="true"></asp:Parameter>
        </SelectParameters>
    </asp:SqlDataSource>
    

    I set the parameter like this:

    private void setNum(String num)
    {
       src_Cars.CancelSelectOnNullParameter = false;
       src_Cars.SelectParameters["num"].ConvertEmptyStringToNull = true;
       src_CarsSelectParameters["own"].DefaultValue = num;
       src_Cars.DataBind();
    }
    

    Then, I bind a DropDownList to the DataSource:

    DwopDownList lst;
    lst.DataSource = src_Cars;
    lst.DataBind();

    When num is not null, the list is filled as expected.

    When num is null, the list does not get filled. But attention: The list is not "filled" with an empty list. Instead the DataBind method must abort somehow.
    Here is why: The user may select from different categories: cars and bikes. When he selects the bikes category, the DropDownList is filled with bikes.
    When he then selects the cars category again, the list remains filled with bikes, even after DataBind is called!

    Therefore, I assume that the data binding process must be abort somewhere.

    When I copy the SQL string in SQL Management Studio and replace "@special" with "NULL", it works perfectly.

    What can be the problem here?

    Thanks
    Magnus

    Friday, October 30, 2015 4:54 AM

Answers

  • User-271186128 posted

    Hi Magnus,

    When num is not null, the list is filled as expected.

    When num is null, the list does not get filled. But attention: The list is not "filled" with an empty list. Instead the DataBind method must abort somehow.
    Here is why: The user may select from different categories: cars and bikes. When he selects the bikes category, the DropDownList is filled with bikes.
    When he then selects the cars category again, the list remains filled with bikes, even after DataBind is called!

    From your description, I suggest you could try to bind the DropDownList programmatically, you could set the select command and select parameters from code behind.

    Please refer the following code:

               <asp:DropDownList ID="DropDownList2" runat="server"
                     DataTextField="FirstName" DataValueField="EmployeeID"></asp:DropDownList>
    
                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:MyTestDBConnStr2 %>">
                    <SelectParameters>
                        <asp:Parameter Name="firstName" DbType="String" />
                    </SelectParameters>
                </asp:SqlDataSource>

    Code behind:

                    //check whether the select parameter is Null
    //if it is not null, using the following code to bind DropDownList
    SqlDataSource2.SelectCommand = "SELECT [EmployeeID], [FirstName], [LastName], [Title], [City] FROM [Employees] Where [FirstName] = @firstName"; SqlDataSource2.SelectParameters["FirstName"].DefaultValue = "Nancy"; DropDownList2.DataSourceID = "SqlDataSource2"; DropDownList2.DataBind();

    //If it is Null, Not bind DropDownList.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 1, 2015 9:07 PM

All replies

  • User260076833 posted

    Sorry for the long posting. I have made a minimal example:

    TestNullSelectParameterMin.aspx:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="TestNullSelectParameterMin.aspx.cs" Inherits="Governix.apl.tst.TestNullSelectParameterMin" %>
    
    <!DOCTYPE html>
    
    <asp:SqlDataSource ID="src_Test" runat="server" ConnectionString='<%$ ConnectionStrings:MyConStr %>' SelectCommand="SELECT num,num + ' ' + gruppe_name AS name FROM [slv_v_schluessel_a] WHERE (num NOT IN (SELECT schluessel_a FROM slv_zuordnung WHERE (schluessel_a IS NOT NULL)) OR num=@own);">
        <SelectParameters>
            <asp:Parameter Name="own" DbType="String" ConvertEmptyStringToNull="true"></asp:Parameter>
        </SelectParameters>
    </asp:SqlDataSource>
    
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        
            <asp:DropDownList ID="lst_Data" runat="server" DataTextField="num" DataValueField="num"></asp:DropDownList>
    
        </div>
        </form>
    </body>
    </html>
    

    TestNullSelectParameterMin.aspx.cs:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace Governix.apl.tst
    {
        public partial class TestNullSelectParameterMin : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                src_Test.SelectParameters["own"].DefaultValue = null;
                src_Test.DataBind();
                lst_Data.DataSource = src_Test;
                lst_Data.DataBind();
            }
        }
    }

    This produces an empty list, allthough it produces the whole table when executed in SQL Management Studio.

    Magnus

    Friday, October 30, 2015 5:48 AM
  • User-271186128 posted

    Hi Magnus,

    When num is not null, the list is filled as expected.

    When num is null, the list does not get filled. But attention: The list is not "filled" with an empty list. Instead the DataBind method must abort somehow.
    Here is why: The user may select from different categories: cars and bikes. When he selects the bikes category, the DropDownList is filled with bikes.
    When he then selects the cars category again, the list remains filled with bikes, even after DataBind is called!

    From your description, I suggest you could try to bind the DropDownList programmatically, you could set the select command and select parameters from code behind.

    Please refer the following code:

               <asp:DropDownList ID="DropDownList2" runat="server"
                     DataTextField="FirstName" DataValueField="EmployeeID"></asp:DropDownList>
    
                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:MyTestDBConnStr2 %>">
                    <SelectParameters>
                        <asp:Parameter Name="firstName" DbType="String" />
                    </SelectParameters>
                </asp:SqlDataSource>

    Code behind:

                    //check whether the select parameter is Null
    //if it is not null, using the following code to bind DropDownList
    SqlDataSource2.SelectCommand = "SELECT [EmployeeID], [FirstName], [LastName], [Title], [City] FROM [Employees] Where [FirstName] = @firstName"; SqlDataSource2.SelectParameters["FirstName"].DefaultValue = "Nancy"; DropDownList2.DataSourceID = "SqlDataSource2"; DropDownList2.DataBind();

    //If it is Null, Not bind DropDownList.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 1, 2015 9:07 PM