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.
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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.
All-Star
52673 Points
15717 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
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(); }Suwandi - Non Graduate Programmer
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.
All-Star
52673 Points
15717 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.
Suwandi - Non Graduate Programmer
Contributor
3370 Points
1409 Posts
Re: SQL query on button click that changes based on DDL selection.
Dec 20, 2019 03:29 AM|samwu|LINK
Hi Gojirha,
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