How to update the DropDownList based on the filtered GridView

Question

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

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}

Mairaj Ahmad · Accepted Answer

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

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.

How to update the DropDownList based on the filtered GridView

Answers (1)

Related Questions