Asked by:
SQL query on button click that changes based on DDL selection.

Question
-
User1149745897 posted
Okay so as the title says I'm trying to run a SELECT query on my SSMS database from a visual studio C# asp.net web form.
In my database I have three venues South, West, and East, and I've set them all as options in a drop down list in my web form tied to a button.
What I am trying to achieve is the following query:
SELECT court_number,timeslot
FROM Court
WHERE offer = 1 AND venue_number = 101;Venue_number changes from, 101 / 102 / 103 based on which venue is selected in the drop down list when the user clicks the submit button.
However I'm entirely lost, this is as far as I've gotten so far using multiple data sources, but it is only displaying one and ignoring the drop down list.
protected void btnCourtSearch_Click(object sender, EventArgs e)
{
if (DropDownList1.SelectedValue == "West")
{
GridView2.DataSource = SqlDataSource4;
GridView2.DataBind();
}
else if (DropDownList1.SelectedValue == "South")
{
GridView2.DataSource = SqlDataSource3;
GridView2.DataBind();
}else if (DropDownList1.SelectedValue == "East ")
{
GridView2.DataSource = SqlDataSource2;Thanks for any help you can offer.
Thursday, December 19, 2019 6:09 PM
All replies
-
User-1716253493 posted
Use single datasource with parameters
SELECT court_number,timeslot FROM Court WHERE offer = 1 AND venue_number = @venue_number;
Use ControlParameter to pass ddl selectedvalue for the parameter
In above way set ddl item text as "West" and value as "101"
Or set sqldatasource parameter value from the code like you have without changing the datasource
if (DropDownList1.SelectedValue == "West") { SqlDataSource1.SelectParameters["venue_number"].DefaultValue="101"; GridView2.DataBind(); }
Friday, December 20, 2019 12:38 AM -
User1149745897 posted
Ahhh this seems way more efficient, I got it to work but ended up with 9-10 datasources.
Friday, December 20, 2019 12:47 AM -
User-1716253493 posted
Don't use multiple sqldatasource or multiple query for single control.
Make single query with parameters, manipulate parameters values only to get it work.
Friday, December 20, 2019 1:57 AM -
User288213138 posted
Hi Gojirha,
In my database I have three venues South, West, and East, and I've set them all as options in a drop down list in my web form tied to a button.According to your description, i made demo for you.
I use string.format() to set the parameter.
<asp:GridView ID="GridView1" runat="server"></asp:GridView> <asp:DropDownList ID="DropDownList1" runat="server"> <asp:ListItem Value="101">West</asp:ListItem> <asp:ListItem Value="102">South</asp:ListItem> <asp:ListItem Value="103">East</asp:ListItem> </asp:DropDownList><br /> <asp:Button ID="btnCourtSearch" runat="server" Text="Button" OnClick="btnCourtSearch_Click" /> protected void btnCourtSearch_Click(object sender, EventArgs e) { string query = string.Format("SELECT * FROM Court WHERE offer = 1 AND venue_number = {0}", DropDownList1.SelectedValue); BindGridView(query); } public void BindGridView(string query) { string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlDataAdapter sda = new SqlDataAdapter(query, con)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } }
The result:
Best regards,
Sam
Friday, December 20, 2019 3:29 AM