How to use one SQL query for multiple dropdownlists

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!

Answer 1

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"); 

Answer 2

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

Answer 3

User1279376247 posted

Thank you for this!