Hello,

I'm currently working on a highly configurable Database Viewer webpart for WSS 3.0 which we are going to need for several customized sharepoint sites. Sorry in advance for the large wall of text, but i fear it's necessary to recap the whole issue.

As background information and to describe my problem as good as possible, I'll start by telling you what the webpart shall do:

Basically the webpart contains an UpdatePanel, which contains a GridView and an SqlDataSource. The select-query the Datasource uses can be set via webbrowseable properties or received from a consumer method from another webpart. Now i wanted to add a filtering feature to the webpart, so i want a dropdownlist in the headerrow for each column that should be filterable. As the select-query is completely dynamic and i don't know at design time which columns shall be filterable, i decided to add a webbrowseable property to contain an xml-formed string with filter information.

So i added the following into OnRowCreated of the gridview:

    void gridView_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {             
            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                if (e.Row.Cells[i].GetType() == typeof(DataControlFieldHeaderCell))
                {
                    string headerText = ((DataControlFieldHeaderCell)e.Row.Cells[i]).ContainingField.HeaderText;

                    // add sorting functionality
                    if (_allowSorting && !String.IsNullOrEmpty(headerText))
                    {
                        Label l = new Label();
                        l.Text = headerText;
                        l.ForeColor = Color.Blue;
                        l.Font.Bold = true;
                        l.ID = "Header" + i;
                        l.Attributes["title"] = "Sort by " + headerText;
                        l.Attributes["onmouseover"] = "this.style.cursor = 'pointer'; this.style.color = 'red'";
                        l.Attributes["onmouseout"] = "this.style.color = 'blue'";
                        l.Attributes["onclick"] = "__doPostBack('" + panel.UniqueID + "','SortBy$" + headerText + "');";
                        e.Row.Cells[i].Controls.Add(l);
                    }

                    // check if this column shall be filterable
                    if (!String.IsNullOrEmpty(filterXmlData))
                    {
                        XmlNode columnNode = GetColumnNode(headerText);

                        if (columnNode != null)
                        {
                            string dataValueField = columnNode.Attributes["DataValueField"] == null ? "" : columnNode.Attributes["DataValueField"].Value;
                            string filterQuery = columnNode.Attributes["FilterQuery"] == null ? "" : columnNode.Attributes["FilterQuery"].Value;

                            if (!String.IsNullOrEmpty(dataValueField) && !String.IsNullOrEmpty(filterQuery))
                            {
                                SqlDataSource ds = new SqlDataSource(_conStr, filterQuery);
                                DropDownList cbx = new DropDownList();
                                cbx.ID = "FilterCbx" + i;
                                cbx.Attributes["onchange"] = "__doPostBack('" + panel.UniqueID + "','SelectionChange$" + headerText + "$' + this.options[this.selectedIndex].value);";
                                cbx.Width = 150;
                                cbx.DataValueField = dataValueField;
                                cbx.DataSource = ds;

                                cbx.DataBound += new EventHandler(cbx_DataBound);
                                cbx.PreRender += new EventHandler(cbx_PreRender);
                                cbx.DataBind();

                                e.Row.Cells[i].Controls.Add(cbx);
                            }
                        }
                    }
                }
            }
        }
    }

GetColumnNode() checks in the filter property, if there is a node for the current column, which contains information about the Field the DropDownList should bind to, and the query for filling in the items.

In cbx_PreRender() i check ViewState and select an item in case of a postback.

In cbx_DataBound() i just add tooltips to the list items as the dropdownlist has a fixed width.

Previously, I used AutoPostback and SelectedIndexChanged of the DDL to filter the grid, but to my disappointment it was not always fired. Now i check __EVENTTARGET and __EVENTARGUMENT in OnLoad and call a function when the postback event was due to a selection change in a DDL:

    private void FilterSelectionChanged(string columnName, string selectedValue)
    {
        columnName = "[" + columnName + "]";
        if (selectedValue.IndexOf("--") < 0 ) // "-- All --" selected
        {
            if (filter.ContainsKey(columnName))
                filter[columnName] = "='" + selectedValue + "'";
            else
                filter.Add(columnName, "='" + selectedValue + "'");
        }
        else
        {
            filter.Remove(columnName);
        }

        gridView.PageIndex = 0;
    }

"filter" is a HashTable which is stored in ViewState for persisting the filters (got this sample somewhere on the web, don't remember where).

In OnPreRender of the webpart, i call a function which reads the ViewState and apply the filterExpression to the datasource if there is one. I assume i had to place it here, because if there is another postback (e.g. for sorting) the filters are not applied any more.

    private void ApplyGridFilter()
    {
        string args = " ";
        int i = 0;

        foreach (object key in filter.Keys)
        {
            if (i == 0)
                args = key.ToString() + filter[key].ToString();
            else
                args += " AND " + key.ToString() + filter[key].ToString();

            i++;
        }

        dataSource.FilterExpression = args;
        ViewState.Add("FilterArgs", filter);
    }

    protected override void OnPreRender(EventArgs e)
    {
        EnsureChildControls();

        if (WebPartManager.DisplayMode.Name == "Edit")
        {
            errMsg = "Webpart in Edit mode...";
            return;
        }

        if (useWebPartConnection == true) // get select-query from consumer webpart
        {
            if (provider != null)
            {
                dataSource.SelectCommand = provider.strQuery;
            }
        }

        try
        {
            int currentPageIndex = gridView.PageIndex;

            if (!String.IsNullOrEmpty(m_SortExpression))
            {
                gridView.Sort("[" + m_SortExpression + "]", m_SortDirection);
            }
            gridView.PageIndex = currentPageIndex; // for some reason, the current pageindex resets after sorting
            ApplyGridFilter();
            gridView.DataBind();
        }
        catch (Exception ex)
        {
            Functions.ShowJavaScriptAlert(Page, ex.Message);
        }

        base.OnPreRender(e);
    }

So i set the filterExpression and the call DataBind(). I don't know if this is ok on this late stage.. don't have a lot of asp.net experience after all. If anyone can suggest a better solution, please give me a hint.

This all works great so far, except when i have two or more filters and set them to a combination that returns zero records. Bam ... gridview gone, completely - without a possiblity of changing the filters back. So i googled and found out that i have to subclass gridview in order to always show the headerrow. I found this solution and implemented it with some modifications.

The headerrow get's displayed and i can change the filters even if the returned result contains no rows. But finally to my current problem:

When i have two or more filters set which return zero rows, and i change back one filter to something that should return rows, the gridview remains empty (although the pager is rendered). I have to completly refresh the page to reset the filters. When debugging, i can see in the overridden CreateChildControls of the grid, that the base method indeed returns > 0, but anyway... the gridView.RowCount remains 0 after databinding. Anyone have an idea what's going wrong here?