How to update the DropDownList based on the filter

问题:

I have the following code which is a GridView and right above it I have DropDownList which allows me to filter:

<table class="taskGridView">
    <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>

Here is the code which allows me to populate and filter the table:

public void PullData(string sortExp, string sortDir)
{
    string query = "";
    DataTable taskData = new DataTable();
    connString = @""; //my connection string
    if (ddlTaskName.SelectedIndex > 0)
    {
        strClause += " AND CT.ATTR2739 = '" + ddlTaskName.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CT.ATTR2739 LIKE '%'";
    }
    if (ddlService.SelectedIndex > 0)
    {
        strClause += " AND CT.ATTR2846 = '" + ddlService.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CT.ATTR2846 LIKE '%'";
    }
    if (ddlStatus.SelectedIndex > 0)
    {
        strClause += " AND CT.ATTR2812 = '" + ddlStatus.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CT.ATTR2812 LIKE '%'";
    }
    if (ddlDueDate.SelectedIndex > 0)
    {
        strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '" + ddlDueDate.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) LIKE '%'";
    }
    if (ddlOwner.SelectedIndex > 0)
    {
        strClause += " AND UA.REALNAME = '" + ddlOwner.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND UA.REALNAME LIKE '%'";
    }
    if (ddlClient.SelectedIndex > 0)
    {
        strClause += " AND CT.ATTR2799 = '" + ddlClient.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CT.ATTR2799 LIKE '%'";
    }

    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
    {
        query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0" + strClause;
    }

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

            TasksUpdatePanel.Update();

            conn.Close();
        }
        catch (Exception ex)
        {
            string error = ex.Message;
        }
    }
}
protected void ddlTaskName_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlOwner_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlService_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlStatus_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlDueDate_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlClient_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}

public string sortOrder
{
    get
    {
        if (ViewState["sortOrder"].ToString() == "Desc")
        {
            ViewState["sortOrder"] = "Asc";
        }
        else
        {
            ViewState["sortOrder"] = "Desc";
        }

        return ViewState["sortOrder"].ToString();
    }
    set
    {
        ViewState["sortOrder"] = value;
    }
}

Now what is happening is when the page is loaded, the GridView is populated and also the DropDownList is populated. When I filter the GridView from one of the DropDownList, how do I modify my ASP.net page or code behind so the rest of the DropDownList is changed based on the filtered GridView?

Right now the DropDownList is hard coded, but I would like it to be dynamic so the DropDownList shows the data from the updated GridView.

UPDATE: I updated the PullData function to this:

public void PullData(string sortExp, string sortDir)
        {
            string query = "";
            DataTable taskData = new DataTable();
            connString = @""; //connection string
            if (ddlTaskName.SelectedIndex > 0)
            {
                strClause += " AND CT.ATTR2739 = '" + ddlTaskName.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND CT.ATTR2739 LIKE '%'";
            }
            if (ddlService.SelectedIndex > 0)
            {
                strClause += " AND CT.ATTR2846 = '" + ddlService.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND CT.ATTR2846 LIKE '%'";
            }
            if (ddlStatus.SelectedIndex > 0)
            {
                strClause += " AND CT.ATTR2812 = '" + ddlStatus.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND CT.ATTR2812 LIKE '%'";
            }
            if (ddlDueDate.SelectedIndex > 0)
            {
                strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '" + ddlDueDate.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) LIKE '%'";
            }
            if (ddlOwner.SelectedIndex > 0)
            {
                strClause += " AND UA.REALNAME = '" + ddlOwner.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND UA.REALNAME LIKE '%'";
            }
            if (ddlClient.SelectedIndex > 0)
            {
                strClause += " AND C.ATTR2815 = '" + ddlClient.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND C.ATTR2815 LIKE '%'";
            }
            if (ddlSite.SelectedIndex > 0)
            {
                strClause += " AND SI.ATTR2819 = '" + ddlSite.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND SI.ATTR2819 LIKE '%'";
            }
            if (ddlPractice.SelectedIndex > 0)
            {
                strClause += " AND PR.ATTR2817 = '" + ddlPractice.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND PR.ATTR2817 LIKE '%'";
            }
            if (ddlProvider.SelectedIndex > 0)
            {
                strClause += " AND P.ATTR2919 = '" + ddlProvider.SelectedItem.Text + "'";
            }
            else
            {
                strClause += " AND P.ATTR2919 LIKE '%'";
            }


            if (ddlTaskName.SelectedIndex == 0 && ddlService.SelectedIndex == 0 && ddlStatus.SelectedIndex == 0 && ddlDueDate.SelectedIndex == 0 && ddlOwner.SelectedIndex == 0 && ddlClient.SelectedIndex == 0 && ddlSite.SelectedIndex == 0 && ddlPractice.SelectedIndex == 0 && ddlProvider.SelectedIndex == 0)
            {
                query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0";
            }
            else
            {
                query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0" + strClause;
            }

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

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

                    DataTable dt = myDataView.ToTable(true, "Task Name");
                    var rows = (from DataRow dRow in dt.Rows select new { Name = dRow["Task Name"], ID = dRow["Task Detail"] }).Distinct();
                    ddlTaskName.DataSource = rows;
                    ddlTaskName.DataTextField = "Task Name";
                    ddlTaskName.DataValueField = "Task Detail";
                    ddlTaskName.DataBind();

                    TasksUpdatePanel.Update();

                    conn.Close();
                }
                catch (Exception ex)
                {
                    string error = ex.Message;
                }
            }
        }

It is failing with this error:

{System.InvalidOperationException: Both DataSource and DataSourceID are defined on 'ddlTaskName'.  Remove one definition.
   at System.Web.UI.WebControls.DataBoundControl.EnsureSingleDataSource()
   at System.Web.UI.WebControls.DataBoundControl.ConnectToDataSourceView()
   at System.Web.UI.WebControls.DataBoundControl.GetData()
   at System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
   at System.Web.UI.WebControls.ListControl.PerformSelect()
   at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
   at OB.Pages.YT.PullData(String sortExp, String sortDir) in Tasks.aspx.cs:line 210}

回答1:

From your question what i understand is you want to associate every dropdown with respective columns of gridview.if i am right you can do this

    ddlTaskName.DataSource = myDataView;
    ddlTaskName.DataTextField = "Name";//Name of column which you want to display as text
    ddlTaskName.DataValueField = "ID";//Name of column which you want to be Value of dropdown list items.
    ddlTaskName.DataBind();

You will have to do this for every dropdown in your PullData method.

Update 1

Here are two methods you can try if your DataValueFiled and DataTextFeild are same than you can try this

    DataTable dt = myDataView.ToTable(true, "Name");
    ddlTaskName.DataSource = dt;
    ddlTaskName.DataTextField = "Name";
    ddlTaskName.DataValueField = "Name";
    ddlTaskName.DataBind(); 

This will only select one column and that is Name column. If you have different DataValueField and DataTextFeild than you can try this

var rows = (from DataRow dRow in dt.Rows select new { Name = dRow["Name"], ID = dRow["ID"] }).Distinct();
 ddlTaskName.DataSource = rows;
    ddlTaskName.DataTextField = "Name";
    ddlTaskName.DataValueField = "ID";
    ddlTaskName.DataBind();

===========================Update 2===============

Ok Here you go

Now your first drop down will look like this

<asp:DropDownList ID="ddlTaskName" CssClass="chosen-select" AutoPostBack="true" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlTaskName_onSelectIndexChanged"></asp:DropDownList>

Now on page load you will fill this with data what ever you want like this

    ddlTaskName.DataSource = "your dataset ot datatable or dataview name";
    ddlTaskName.DataTextField = "Name";
    ddlTaskName.DataValueField = "ID";
    ddlTaskName.DataBind();

And in Pull data method you will do like this

 var rows = (from DataRow dRow in dt.Rows select new { Name = dRow["Name"], ID = dRow["ID"] }).Distinct();
    ddlTaskName.DataSource = rows;
    ddlTaskName.DataTextField = "Name";
    ddlTaskName.DataValueField = "ID";
    ddlTaskName.DataBind();

The dt here is DataTable object if you are filling a dataset you can use like this myDataSet.Tables[0] instead of dt.

And to add an option "All" you can do like this

ddlTest.Items.Insert(0, new ListItem("All"));

This will insert "All" at the top you can change its location.