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.

Answer 1

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

Answer 2

User1149745897 posted

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

Answer 3

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.

Answer 4

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