SQL query on button click that changes based on DDL selection.RSS

• • Reply
  Gojirha

  None

  0 Points

  2 Posts

  SQL query on button click that changes based on DDL selection.

  Dec 19, 2019 06:09 PM|Gojirha|LINK

  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.

• • Reply
  oned_gk

  All-Star

  52423 Points

  15634 Posts

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

  Dec 20, 2019 12:38 AM|oned_gk|LINK

  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();
  }

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  Gojirha

  None

  0 Points

  2 Posts

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

  Dec 20, 2019 12:47 AM|Gojirha|LINK

  Ahhh this seems way more efficient, I got it to work but ended up with 9-10 datasources.

• • Reply
  oned_gk

  All-Star

  52423 Points

  15634 Posts

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

  Dec 20, 2019 01:57 AM|oned_gk|LINK

  Don't use multiple sqldatasource or multiple query for single control.

  Make single query with parameters, manipulate parameters values only to get it work.

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  samwu

  Contributor

  2500 Points

  1051 Posts

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

  Dec 20, 2019 03:29 AM|samwu|LINK

  Hi Gojirha,

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.