Answered by:
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!
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