locked
SQL query on button click that changes based on DDL selection. RRS feed

  • 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