locked
How to use one SQL query for multiple dropdownlists RRS feed

  • Question

  • User1279376247 posted

    Hi,

    I have a SQLDataSource that can take a parameter, and I want to use the same query for 3 different dropdownlists, and just change the parameter.

    Normally, I can set the parameter on the Selecting event for the SqlDataSource but that's not going to work in this case.

    How can I change the parameter prior to binding each of the 3  dropdownlists?

    Thanks!

    Thursday, February 14, 2019 8:15 PM

Answers

  • User-1174608757 posted

    Hi DaveBF,

    According to your description, I have made a sample here. To use one SQL query for multiple dropdownlists, I suggest you to dynamically add value of parameters  in code behind. 

    Here is my demo ,I hope it could help you.

    table students

    Aspx:

    <div>
                <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList>
                <asp:DropDownList ID="DropDownList2" runat="server"></asp:DropDownList>
                <asp:DropDownList ID="DropDownList3" runat="server"></asp:DropDownList>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mssqlserver %>" SelectCommand="SELECT NAME  FROM [students] WHERE SCORE=@SCORE" >
                </asp:SqlDataSource>
            </div>

    Code behind:

    public partial class dropdown : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.SelectParameters.Add("SCORE","70");// add parameter name and value
                DropDownList1.DataSource = SqlDataSource1;//bind datasource of dropdownlist
                DropDownList1.DataTextField = "name";// set the text as value
    DropDownList1.DataBind(); SqlDataSource1.SelectParameters.Clear();// Clear original data SqlDataSource1.SelectParameters.Add("SCORE", "60"); DropDownList2.DataSource = SqlDataSource1; DropDownList1.DataTextField = "name";
    DropDownList2.DataBind(); SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectParameters.Add("SCORE", "50"); DropDownList3.DataSource = SqlDataSource1;
    DropDownList1.DataTextField = "name"; DropDownList3.DataBind(); } }

    It shows good as below:

    Best Regards

    Wei Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2019 5:32 AM

All replies

  • User1120430333 posted

    How can I change the parameter prior to binding each of the 3 dropdownlists?

    You make method/fucnction call it GetDropDownListData(string parm) that goes get the data based on the parm, it returns a datatable or a list of custom objects that is blindable to the control.Datasource.

    control.Datasource = GetDropDownListData("1"); 

    Thursday, February 14, 2019 8:29 PM
  • User-1174608757 posted

    Hi DaveBF,

    According to your description, I have made a sample here. To use one SQL query for multiple dropdownlists, I suggest you to dynamically add value of parameters  in code behind. 

    Here is my demo ,I hope it could help you.

    table students

    Aspx:

    <div>
                <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList>
                <asp:DropDownList ID="DropDownList2" runat="server"></asp:DropDownList>
                <asp:DropDownList ID="DropDownList3" runat="server"></asp:DropDownList>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mssqlserver %>" SelectCommand="SELECT NAME  FROM [students] WHERE SCORE=@SCORE" >
                </asp:SqlDataSource>
            </div>

    Code behind:

    public partial class dropdown : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.SelectParameters.Add("SCORE","70");// add parameter name and value
                DropDownList1.DataSource = SqlDataSource1;//bind datasource of dropdownlist
                DropDownList1.DataTextField = "name";// set the text as value
    DropDownList1.DataBind(); SqlDataSource1.SelectParameters.Clear();// Clear original data SqlDataSource1.SelectParameters.Add("SCORE", "60"); DropDownList2.DataSource = SqlDataSource1; DropDownList1.DataTextField = "name";
    DropDownList2.DataBind(); SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectParameters.Add("SCORE", "50"); DropDownList3.DataSource = SqlDataSource1;
    DropDownList1.DataTextField = "name"; DropDownList3.DataBind(); } }

    It shows good as below:

    Best Regards

    Wei Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2019 5:32 AM
  • User1279376247 posted

    Thank you for this!

    Friday, February 15, 2019 11:32 AM