locked
Gridview header dropdown with rowdatabound RRS feed

  • Question

  • User2091173246 posted

    Hi,

    I have an issue with my gridivew header dropdownlist.

    I can populate the gridview header with data only if i Comment out  my WHERE clause e.g. /* WHERE [Status] LIKE '%' + @Status + '%'*/

        <HeaderTemplate>
            <asp:DropDownList ID="ddlStatusHeader" AutoPostBack="true" runat="server">
                <asp:ListItem>Status</asp:ListItem>
            </asp:DropDownList>
        </HeaderTemplate>
        protected void gwActivity_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if(e.Row.RowType == DataControlRowType.Header)
            {
                if(gwActivity.EditIndex == e.Row.RowIndex)
                {
    
    
                    DropDownList ddlStatus = (DropDownList)e.Row.Cells[4].FindControl("ddlStatusHeader");
                   
    
                    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(CS))
                    {
                        SqlCommand cmd = new SqlCommand("SELECT DISTINCT [Status] FROM [BI_Planning].[dbo].[tblStatus] WHERE [Status] LIKE '%' + @Status  + '%'  ", con);
                        cmd.Parameters.AddWithValue("@Status", ddlStatus.Text);
                        con.Open();
                        ddlStatus.DataSource = cmd.ExecuteReader();
                        ddlStatus.DataTextField = "Status";
                        ddlStatus.DataBind();
    
                    }              
    
                }
    
            }
        }

    I do not understand why i can´t populate my DDL header and then filter the gridview depending in what value i choose in the DDL

    I bind my gridview in page load () so that is not the problem.

    can anyone see the problem?

    Monday, April 23, 2018 5:56 PM

Answers

  • User-369506445 posted

    please try below code . in this sample first load data from database in drop-down and grid , next when the drop-down changes , the gridview be filter

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    LoadData();
                }
            }
            public void LoadData()
            {
                gwActivity.DataSource = SelectFromDB();
                gwActivity.DataBind();
            }
            protected void ddlStatusHeader_SelectedIndexChanged(object sender, EventArgs e)
            { 
                /*<***** I want something like this but how do i rebind my gridview???****>*/
              DropDownList ddlStatusHeader = ((DropDownList)sender);
    
                 
                gwActivity.DataSource = SelectFromDBByParam(ddlStatusHeader.SelectedItem.Text);
                gwActivity.DataBind();
            }
    
            private DataTable SelectFromDB()
            {
                var query = "SELECT [Status],[StatusId] FROM [dbo].[tblStatus]";
                return select(query);
            }
    
            private DataTable SelectFromDBByParam(string param)
            {
                var query="SELECT DISTINCT [Status],StatusId FROM [dbo].[tblStatus] where [Status] LIKE '%"+param+"%' ";
                return select(query);
            }
    
            private DataTable select(string query)
            {
                DataTable dataTable = new DataTable();
    
                string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.Connection = con;
                    con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    // this will query your database and return the result to your datatable
                    da.Fill(dataTable);
                    con.Close();
                    da.Dispose();
    
                }
                return dataTable;
            }
            protected void gwActivity_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    if (gwActivity.EditIndex == e.Row.RowIndex)
                    { 
    
                        DropDownList ddlStatus = (DropDownList)e.Row.FindControl("ddlStatusHeader");
                        ddlStatus.DataSource = SelectFromDB();
                        ddlStatus.DataValueField = "StatusID";
                        ddlStatus.DataTextField = "Status";
                        ddlStatus.DataBind();
                    }
                }
            }


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 24, 2018 10:16 AM

All replies

  • User283571144 posted

    Hi Skwizzy,

    I do not understand why i can´t populate my DDL header and then filter the gridview depending in what value i choose in the DDL

    According to your codes, I found you don't use the OnSelectedIndexChanged method to rebind girdview with ddl selected value.

    If you want to generate the girdview according to the DDL selected value, I suggest you could  the  DDL's OnSelectedIndexChanged method.

    More details, you could refer to below codes:

    ASPX:

                <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound">
                    <Columns>
                        <asp:TemplateField>
                            <HeaderTemplate>
                                <asp:DropDownList ID="ddlStatusHeader" AutoPostBack="true" OnSelectedIndexChanged="ddlStatusHeader_SelectedIndexChanged" runat="server">
                                     
                                </asp:DropDownList>
                            </HeaderTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

    Code-behind:

            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    LoadData();
                }
            }
    
            public void LoadData()
            {
                DataTable d1 = new DataTable() { Columns = { "ID", "Product_Name", "Type" } };
                d1.Rows.Add("1", "aa", "bb");
                d1.Rows.Add("2", "bb", "cc");
                d1.Rows.Add("3", "cc", "dd");
                GridView1.DataSource = d1;
                GridView1.DataBind();
            }
            protected void ddlStatusHeader_SelectedIndexChanged(object sender, EventArgs e)
            {
    
    
                DataTable d1 = new DataTable() { Columns = { "ID", "Product_Name", "Type" } };
                d1.Rows.Add("2", "bb", ((DropDownList)sender).SelectedItem.Text);
                d1.Rows.Add("3", "cc", "dd");
                GridView1.DataSource = d1;
                GridView1.DataBind();
            }
    
            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    if (GridView1.EditIndex == e.Row.RowIndex)
                    {
                        //string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                        //using (SqlConnection con = new SqlConnection(CS))
                        //{
                        //    SqlCommand cmd = new SqlCommand("SELECT DISTINCT [Status] FROM [BI_Planning].[dbo].[tblStatus] WHERE [Status] LIKE '%' + @Status  + '%'  ", con);
                        //    cmd.Parameters.AddWithValue("@Status", ddlStatus.Text);
                        //    con.Open();
                        //    ddlStatus.DataSource = cmd.ExecuteReader();
                        //    ddlStatus.DataTextField = "Status";
                        //    ddlStatus.DataBind();
    
                        //}                
                        DropDownList ddlStatus = (DropDownList)e.Row.FindControl("ddlStatusHeader");
                        DataTable d1 = new DataTable() { Columns = { "ID", "Product_Name", "Status" } };
                        d1.Rows.Add("1", "aa", "bb");
                        d1.Rows.Add("2", "bb", "cc");
                        d1.Rows.Add("3", "cc", "dd");
                        ddlStatus.DataSource = d1;
                        ddlStatus.DataTextField = "Status";
                        ddlStatus.DataValueField = "Product_Name";
                        ddlStatus.DataBind();
                        ddlStatus.Items.Insert(0, new ListItem("Status", "0"));
                    }
                }
            }

    Result:

    Best Regards,

    Brando

    Tuesday, April 24, 2018 2:22 AM
  • User2091173246 posted

    Hi,

    Thank you for your help.  OnSelectedIndexChanged is ofcource the method (event?) i want to use, I missed that.

    The ASPX markup is straightforward however your SelectedIndexChanged event i do not know exactly how to implement with my select statement from DB

    SELECT DISTINCT [StatusID],[Status] FROM [BI_Planning].[dbo].[tblStatus]

    This does not work:

        protected void ddlStatusHeader_SelectedIndexChanged(object sender, EventArgs e)
        {
    
    
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("SELECT DISTINCT [StatusID],[Status] FROM [BI_Planning].[dbo].[tblStatus]", con);            
                con.Open();
                ddlStatusHeader.DataSource = cmd.ExecuteReader();
                ddlStatusHeader.DataValueField = "StatusID";
                ddlStatusHeader.DataTextField = "Status";
                ddlStatusHeader.DataBind();               
    
            }
        }

    And it looks like i still should use RowDataBound and this does fill my dropdownlist. so this works however im not sure if im doing this right?

        protected void gwActivity_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.Header)
            {
                if (gwActivity.EditIndex == e.Row.RowIndex)
                {
                    DropDownList ddlStatus = (DropDownList)e.Row.Cells[4].FindControl("ddlStatusHeader");
                    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(CS))
                    {
                        SqlCommand cmd = new SqlCommand("SELECT DISTINCT [Status] FROM [BI_Planning].[dbo].[tblStatus] ", con);
                        con.Open();
                        ddlStatus.DataSource = cmd.ExecuteReader();
                        ddlStatus.DataTextField = "Status";
                        ddlStatus.DataBind();
    
                    }
    
                }
    
            }
        }

    I try to change your code to suit mine but i think im doing something wrong

    Tuesday, April 24, 2018 7:16 AM
  • User-369506445 posted

    hi

    i changed it and you can try below code :

    protected void ddlStatusHeader_SelectedIndexChanged(object sender, EventArgs e)
            { 
                DropDownList ddlStatusHeader = ((DropDownList) sender);
    
                string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand("SELECT DISTINCT [StatusID],[Status] FROM [dbo].[tblStatus]", con);
                    con.Open();
                    ddlStatusHeader.DataSource = cmd.ExecuteReader();
                    ddlStatusHeader.DataValueField = "StatusID";
                    ddlStatusHeader.DataTextField = "Status";
                    ddlStatusHeader.DataBind();
    
                }
            }

    and 

    protected void gwActivity_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    if (GridView1.EditIndex == e.Row.RowIndex)
                    { 
    
                        DropDownList ddlStatus = (DropDownList)e.Row.FindControl("ddlStatusHeader");
                        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                        using (SqlConnection con = new SqlConnection(CS))
                        {
                            SqlCommand cmd = new SqlCommand("SELECT DISTINCT [Status],StatusId FROM [dbo].[tblStatus]", con);
                            con.Open();
                            ddlStatus.DataSource = cmd.ExecuteReader();
                            ddlStatus.DataValueField = "StatusID";
                            ddlStatus.DataTextField = "Status";
                            ddlStatus.DataBind();
    
    
                        }
                    }
                }
            }

    Tuesday, April 24, 2018 7:50 AM
  • User2091173246 posted

    Hi,

    Thank you.

    However what the SelectedIndexChanged event does is that it adds the same rows twice (and more) everytime i select a value and the gridview does not get filtered.

    The gridview name is gwActivity but it is placed in a method called BindGridviewActivity()

    Tuesday, April 24, 2018 8:14 AM
  • User-369506445 posted

    try below code :

     protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    LoadData();
                }
            }
            public void LoadData()
            {
                DataTable d1 = new DataTable() { Columns = { "ID", "Product_Name", "Type" } };
                d1.Rows.Add("1", "aa", "bb");
                d1.Rows.Add("2", "bb", "cc");
                d1.Rows.Add("3", "cc", "dd");
                gwActivity.DataSource = d1;
                gwActivity.DataBind();
            }
            protected void ddlStatusHeader_SelectedIndexChanged(object sender, EventArgs e)
            { 
                /*DropDownList ddlStatusHeader = ((DropDownList) sender);
    
                string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand("SELECT DISTINCT [StatusID],[Status] FROM [dbo].[tblStatus]", con);
                    con.Open();
                    ddlStatusHeader.DataSource = cmd.ExecuteReader();
                    ddlStatusHeader.DataValueField = "StatusID";
                    ddlStatusHeader.DataTextField = "Status";
                    ddlStatusHeader.DataBind();
    
                }*/
    
                DataTable d1 = new DataTable() { Columns = { "ID", "Product_Name", "Type" } };
                d1.Rows.Add("2", "bb", ((DropDownList)sender).SelectedItem.Text);
                d1.Rows.Add("3", "cc", "dd");
                gwActivity.DataSource = d1;
                gwActivity.DataBind();
            }
            protected void gwActivity_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    if (gwActivity.EditIndex == e.Row.RowIndex)
                    { 
    
                        DropDownList ddlStatus = (DropDownList)e.Row.FindControl("ddlStatusHeader");
                        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                        using (SqlConnection con = new SqlConnection(CS))
                        {
                            SqlCommand cmd = new SqlCommand("SELECT DISTINCT [Status],StatusId FROM [dbo].[tblStatus]", con);
                            con.Open();
                            ddlStatus.DataSource = cmd.ExecuteReader();
                            ddlStatus.DataValueField = "StatusID";
                            ddlStatus.DataTextField = "Status";
                            ddlStatus.DataBind();
    
    
                        }
                    }
                }
            }

    Tuesday, April 24, 2018 8:22 AM
  • User2091173246 posted

    Hi thank you again.

    i might repete my selfe but the RowDatabound does work fine. But the SelectedIndexChanged does not.

    I want to use my data from DB table not rows.add()

    protected void ddlStatusHeader_SelectedIndexChanged(object sender, EventArgs e)
            { 
    
    	/*<***** I want something like this but how do i rebind my gridview???****>*/
              DropDownList ddlStatusHeader = ((DropDownList)sender);
    
                /*************Connectionstring is located in Web.config ******************/
                string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("SELECT [Status] FROM [BI_Planning].[dbo].[tblStatus] WHERE [Status] LIKE '%' + @Status  + '%' ", con);
                cmd.Parameters.AddWithValue("@Status", ddlStatusHeader.SelectedValue);
                cmd.Connection = con;
                con.Open();
                ddlStatusHeader.DataSource = cmd.ExecuteReader();
                ddlStatusHeader.DataTextField = "Status";
                ddlStatusHeader.DataBind();
               
            }
    
    	/*<***** CAN I REPLCAE THIS WITH THE FOLLOWING CODE UP?****>*/
                DataTable d1 = new DataTable() { Columns = { "ID", "Product_Name", "Type" } };
                d1.Rows.Add("2", "bb", ((DropDownList)sender).SelectedItem.Text);
                d1.Rows.Add("3", "cc", "dd");
                gwActivity.DataSource = d1;
                gwActivity.DataBind();
            }

    Tuesday, April 24, 2018 8:28 AM
  • User-369506445 posted

    please try below code . in this sample first load data from database in drop-down and grid , next when the drop-down changes , the gridview be filter

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    LoadData();
                }
            }
            public void LoadData()
            {
                gwActivity.DataSource = SelectFromDB();
                gwActivity.DataBind();
            }
            protected void ddlStatusHeader_SelectedIndexChanged(object sender, EventArgs e)
            { 
                /*<***** I want something like this but how do i rebind my gridview???****>*/
              DropDownList ddlStatusHeader = ((DropDownList)sender);
    
                 
                gwActivity.DataSource = SelectFromDBByParam(ddlStatusHeader.SelectedItem.Text);
                gwActivity.DataBind();
            }
    
            private DataTable SelectFromDB()
            {
                var query = "SELECT [Status],[StatusId] FROM [dbo].[tblStatus]";
                return select(query);
            }
    
            private DataTable SelectFromDBByParam(string param)
            {
                var query="SELECT DISTINCT [Status],StatusId FROM [dbo].[tblStatus] where [Status] LIKE '%"+param+"%' ";
                return select(query);
            }
    
            private DataTable select(string query)
            {
                DataTable dataTable = new DataTable();
    
                string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.Connection = con;
                    con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    // this will query your database and return the result to your datatable
                    da.Fill(dataTable);
                    con.Close();
                    da.Dispose();
    
                }
                return dataTable;
            }
            protected void gwActivity_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    if (gwActivity.EditIndex == e.Row.RowIndex)
                    { 
    
                        DropDownList ddlStatus = (DropDownList)e.Row.FindControl("ddlStatusHeader");
                        ddlStatus.DataSource = SelectFromDB();
                        ddlStatus.DataValueField = "StatusID";
                        ddlStatus.DataTextField = "Status";
                        ddlStatus.DataBind();
                    }
                }
            }


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 24, 2018 10:16 AM