All geek questions in one place

How to make a few DropDownList changes based on a choice from another DropDownList

I have the following ASP.net code that I use to enter my GridView :

 <asp:UpdatePanel runat="server" ClientIDMode="Static" ID="TasksUpdatePanel" UpdateMode="Conditional"> <ContentTemplate> <table style="width: 100%; padding: 0; margin: 0; border: 0; border-spacing: 0; border-collapse: separate;" class="GridviewTable"> <tr> <td style="width: 25%;"> <asp:DropDownList ID="ddlTaskName" CssClass="chosen-select" DataSourceID="dsPopulateTaskName" AutoPostBack="true" DataValueField="Task Name" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlTaskName_onSelectIndexChanged"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="dsPopulateTaskName" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2739] 'Task Name' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource> </td> <td style="width: 20%;"> <asp:DropDownList ID="ddlService" CssClass="chosen-select" DataSourceID="dsPopulateService" AutoPostBack="true" DataValueField="Service" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlService_onSelectIndexChanged"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="dsPopulateService" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2846] 'Service' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource> </td> <td style="width: 11%;"> <asp:DropDownList ID="ddlStatus" CssClass="chosen-select" DataSourceID="dsPopulateStatus" AutoPostBack="true" DataValueField="Status" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlStatus_onSelectIndexChanged"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="dsPopulateStatus" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2812] 'Status' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource> </td> <td style="width: 14%;"> <asp:DropDownList ID="ddlDueDate" CssClass="chosen-select" DataSourceID="dsPopulateDueDate" AutoPostBack="true" DataValueField="Due Date" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlDueDate_onSelectIndexChanged"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="dsPopulateDueDate" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT CONVERT(VARCHAR(14), [ATTR2752], 110) 'Due Date' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource> </td> <td style="width: 15%;"> <asp:DropDownList ID="ddlOwner" CssClass="chosen-select" DataSourceID="dsPopulateOwner" AutoPostBack="true" DataValueField="Owner" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlOwner_onSelectIndexChanged"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="dsPopulateOwner" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [REALNAME] 'Owner' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource> </td> <td style="width: 15%;"> <asp:DropDownList ID="ddlClient" CssClass="chosen-select" DataSourceID="dsPopulateClient" AutoPostBack="true" DataValueField="Client" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlClient_onSelectIndexChanged"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="dsPopulateClient" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2799] 'Client' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource> </td> </tr> </table> <asp:GridView ShowHeaderWhenEmpty="false" AlternatingRowStyle-BackColor="#EBE9E9" AutoGenerateColumns="false" OnSorting="yourTasksGV_Sorting" AllowSorting="true" ID="yourTasksGV" runat="server" ClientIDMode="Static" EmptyDataText="There is no data to display" OnRowDataBound="yourTasksGV_RowDataBound" OnRowCreated="yourTasksGV_RowCreated"> <Columns> <asp:HyperLinkField Target="_blank" DataNavigateUrlFields="Task Detail" DataTextField="Task Name" DataNavigateUrlFormatString="" HeaderText="Task Detail" SortExpression="Task Name" ItemStyle-Width="25%" ItemStyle-CssClass="taskTableColumn" /> <asp:BoundField DataField="Service" HeaderText="Service" SortExpression="Service" ItemStyle-Width="20%" ItemStyle-CssClass="taskTableColumn" /> <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" ItemStyle-Width="10%" ItemStyle-CssClass="taskTableColumn" /> <asp:BoundField DataField="Due Date" HeaderText="Due Date" SortExpression="Due Date" ItemStyle-Width="15%" ItemStyle-CssClass="taskTableColumn" /> <asp:BoundField DataField="Owner" HeaderText="Owner" SortExpression="Owner" ItemStyle-Width="15%" ItemStyle-CssClass="taskTableColumn" /> <asp:BoundField DataField="Client" HeaderText="Client" SortExpression="Client" ItemStyle-Width="15%" ItemStyle-CssClass="taskTableColumn" /> </Columns> </asp:GridView> </ContentTemplate> </asp:UpdatePanel>

My code for the ASP.net page above:

 protected void ddlTaskName_onSelectIndexChanged(object sender, EventArgs e) { string query = ""; string strText = ddlTaskName.SelectedItem.Text; ddlService.SelectedIndex = 0; ddlStatus.SelectedIndex = 0; ddlDueDate.SelectedIndex = 0; ddlClient.SelectedIndex = 0; ddlOwner.SelectedIndex = 0; //MessageBox.Show(strText); DataTable taskData = new DataTable(); if (strText == "All") { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0"; } else { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2739 = '" + strText + "'"; } using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (ViewState["sortExp"].ToString() != string.Empty) { //MessageBox.Show(sortExp); //MessageBox.Show(sortDir); myDataView.Sort = string.Format("{0} {1}", ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString()); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); TasksUpdatePanel.Update(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } } protected void ddlOwner_onSelectIndexChanged(object sender, EventArgs e) { string query = ""; string strText = ddlOwner.SelectedItem.Text; ddlService.SelectedIndex = 0; ddlStatus.SelectedIndex = 0; ddlDueDate.SelectedIndex = 0; ddlClient.SelectedIndex = 0; ddlTaskName.SelectedIndex = 0; //MessageBox.Show(strText); DataTable taskData = new DataTable(); if (strText == "All") { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0"; } else { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND UA.REALNAME = '" + strText + "'"; } using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (ViewState["sortExp"].ToString() != string.Empty) { //MessageBox.Show(sortExp); //MessageBox.Show(sortDir); myDataView.Sort = string.Format("{0} {1}", ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString()); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); TasksUpdatePanel.Update(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } } protected void ddlService_onSelectIndexChanged(object sender, EventArgs e) { string query = ""; string strText = ddlService.SelectedItem.Text; ddlTaskName.SelectedIndex = 0; ddlStatus.SelectedIndex = 0; ddlDueDate.SelectedIndex = 0; ddlClient.SelectedIndex = 0; ddlOwner.SelectedIndex = 0; //MessageBox.Show(strText); DataTable taskData = new DataTable(); if (strText == "All") { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0"; } else { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2846 = '" + strText + "'"; } using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (ViewState["sortExp"].ToString() != string.Empty) { //MessageBox.Show(sortExp); //MessageBox.Show(sortDir); myDataView.Sort = string.Format("{0} {1}", ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString()); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); TasksUpdatePanel.Update(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } } protected void ddlStatus_onSelectIndexChanged(object sender, EventArgs e) { string query = ""; string strText = ddlStatus.SelectedItem.Text; ddlService.SelectedIndex = 0; ddlTaskName.SelectedIndex = 0; ddlDueDate.SelectedIndex = 0; ddlClient.SelectedIndex = 0; ddlOwner.SelectedIndex = 0; //MessageBox.Show(strText); DataTable taskData = new DataTable(); if (strText == "All") { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0"; } else { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2812 = '" + strText + "'"; } using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (ViewState["sortExp"].ToString() != string.Empty) { //MessageBox.Show(sortExp); //MessageBox.Show(sortDir); myDataView.Sort = string.Format("{0} {1}", ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString()); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); TasksUpdatePanel.Update(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } } protected void ddlDueDate_onSelectIndexChanged(object sender, EventArgs e) { string query = ""; string strText = ddlDueDate.SelectedItem.Text; ddlService.SelectedIndex = 0; ddlStatus.SelectedIndex = 0; ddlTaskName.SelectedIndex = 0; ddlClient.SelectedIndex = 0; ddlOwner.SelectedIndex = 0; //MessageBox.Show(strText); DataTable taskData = new DataTable(); if (strText == "All") { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0"; } else { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '" + strText + "'"; } using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (ViewState["sortExp"].ToString() != string.Empty) { //MessageBox.Show(sortExp); //MessageBox.Show(sortDir); myDataView.Sort = string.Format("{0} {1}", ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString()); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); TasksUpdatePanel.Update(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } } protected void ddlClient_onSelectIndexChanged(object sender, EventArgs e) { string query = ""; string strText = ddlClient.SelectedItem.Text; ddlService.SelectedIndex = 0; ddlStatus.SelectedIndex = 0; ddlDueDate.SelectedIndex = 0; ddlTaskName.SelectedIndex = 0; ddlOwner.SelectedIndex = 0; //MessageBox.Show(strText); DataTable taskData = new DataTable(); if (strText == "All") { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0"; } else { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2799 = '" + strText + "'"; } using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (ViewState["sortExp"].ToString() != string.Empty) { //MessageBox.Show(sortExp); //MessageBox.Show(sortDir); myDataView.Sort = string.Format("{0} {1}", ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString()); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); TasksUpdatePanel.Update(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } }

Function that filters the GridView :

 public void PullData(string sortExp, string sortDir) { string query = ""; DataTable taskData = new DataTable(); connString = ""; //the connection string is here if (ddlTaskName.SelectedIndex == 0 && ddlService.SelectedIndex == 0 && ddlStatus.SelectedIndex == 0 && ddlDueDate.SelectedIndex == 0 && ddlOwner.SelectedIndex == 0 && ddlClient.SelectedIndex == 0) { query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0"; } else if (ddlTaskName.SelectedIndex > 0) { string strText = ddlTaskName.SelectedItem.Text; query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2739 = '" + strText + "'"; } else if (ddlService.SelectedIndex > 0) { string strText = ddlService.SelectedItem.Text; query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2846 = '" + strText + "'"; } else if (ddlStatus.SelectedIndex > 0) { string strText = ddlStatus.SelectedItem.Text; query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2812 = '" + strText + "'"; } else if (ddlDueDate.SelectedIndex > 0) { string strText = ddlDueDate.SelectedItem.Text; query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '" + strText + "'"; } else if (ddlOwner.SelectedIndex > 0) { string strText = ddlClient.SelectedItem.Text; query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND UA.REALNAME = '" + strText + "'"; } else if (ddlClient.SelectedIndex > 0) { string strText = ddlClient.SelectedItem.Text; query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0 AND CT.ATTR2799 = '" + strText + "'"; } using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (sortExp != string.Empty) { //MessageBox.Show(sortExp); //MessageBox.Show(sortDir); myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } }

What I want to do when the user selects a parameter from one of DropDownList , the other DropDownList will change to the values ​​for the filtered GridView , but since their DataSource hardcoded, I can not do this.

How do I filter each DropDownList from a filtered GridView after any of the DropDownList selections has been selected?

0
source share
1 answer

While I carefully advised you to use parameterized queries , this should work for you (maybe with minor adjustments, as I cannot see the full code of your code, of course)

This method should generate a valid where clause. You can use this method in various OnSelectedIndexChanged methods in your code to prevent many copies / pasted codes.

 public string GenerateWhereClause() { List<String> conditions = new List<String>(); conditions.Add("(CT.ACTIVESTATUS = 0)"); if (ddlTaskName.SelectedIndex > 0) { string strText = ddlTaskName.SelectedItem.Text; conditions.Add(String.Format("(CT.ATTR2739 = '{0}')", strText)); } if (ddlService.SelectedIndex > 0) { string strText = ddlService.SelectedItem.Text; conditions.Add(String.Format("(CT.ATTR2846 = '{0}')", strText)); } if (ddlStatus.SelectedIndex > 0) { string strText = ddlStatus.SelectedItem.Text; conditions.Add(String.Format("(CT.ATTR2812 = '{0}')", strText)); } if (ddlDueDate.SelectedIndex > 0) { string strText = ddlDueDate.SelectedItem.Text; conditions.Add(String.Format("(CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '{0}')", strText)); } if (ddlOwner.SelectedIndex > 0) { string strText = ddlOwner.SelectedItem.Text; conditions.Add(String.Format("(UA.REALNAME = '{0}')", strText)); } if (ddlClient.SelectedIndex > 0) { string strText = ddlClient.SelectedItem.Text; conditions.Add(String.Format("(CT.ATTR2799 = '{0}')", strText)); } // You can add additional filters here. This isn't the cleanest way of doing it, but it fairly quick and easy as long as you don't intend to add many more filters. string conditionsJoined = String.Join(" AND ", conditions); string whereClause = String.Format(" WHERE {0}", conditionsJoined); return whereClause; }

And this is a modified version of your PullData method. (note that I only changed the top where it creates the conditional statement.

 public void PullData(string sortExp, string sortDir) { string query = String.Format("{0}{1}", strMainQuery, GenerateWhereClause()); DataTable taskData = new DataTable(); connString = ""; //the connection string is here using (SqlConnection conn = new SqlConnection(connString)) { try { SqlCommand cmd = new SqlCommand(query, conn); // create data adapter SqlDataAdapter da = new SqlDataAdapter(query, conn); // this will query your database and return the result to your datatable DataSet myDataSet = new DataSet(); da.Fill(myDataSet); DataView myDataView = new DataView(); myDataView = myDataSet.Tables[0].DefaultView; if (sortExp != string.Empty) { myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir); } yourTasksGV.DataSource = myDataView; yourTasksGV.DataBind(); conn.Close(); } catch (Exception ex) { string error = ex.Message; } } }
+1
source

Source: https://habr.com/ru/post/1395521/

More articles:


All Articles