Narrrowing down DataSet/DataTable results with use of Dropdowns/Textboxes

See more:

SQL

Gridview

DataTable

, +

Gents,

I'm trying to create a dynamic pivot gridview table capable of updating on the fly.

I managed to populate the whole lot from my DB but what do I do if I want the user to have a number of dropdowns/textboxes so they can narrow down the result.

EDIT:

_dtProjects = taProjects.GetData(TextBox1.Text, DropDown1.SelectedValue, etc...);

Now what if the the values of those controls are blank? How do I overcome this?

In my scenario I've got 15 different dropdowns and txtboxes and tens of thousands of records so you surely understand user must be able to have such option to click on any of those to narrow down.

I've spent over a day trying to implement several techniques, with no luck.

Please give me a hint here

protected void LoadGrid()
    {
        grdMain.Controls.Clear();
        grdMain.Columns.Clear();
        _dtEntry.Columns.Clear();
        _dtEntry.Rows.Clear();

        // Load tables from data access layer. 
        TimeDSTableAdapters.PersonsTableAdapter taPersons = new TimeDSTableAdapters.PersonsTableAdapter();
        TimeDSTableAdapters.ProjectsTableAdapter taProjects = new TimeDSTableAdapters.ProjectsTableAdapter();
        TimeDSTableAdapters.TimeLogTableAdapter taTimeLog = new TimeDSTableAdapters.TimeLogTableAdapter();

        _dtPersons = taPersons.GetData();
        _dtProjects = taProjects.GetData();
        _dtTimeLog = taTimeLog.GetData(calendar.SelectedDate);

        // Add first column which is used to store labels 
        _dtEntry.Columns.Add("ProjectName");

        // Create the column in the grid
        TemplateField tfProject = new TemplateField();
        grdMain.Columns.Add(tfProject);
        tfProject.ItemTemplate = new GridViewTemplate(ListItemType.Item, "ProjectName", "0", "String",true);
        tfProject.HeaderTemplate = new GridViewTemplate(ListItemType.Header, "", "0", "String", true);

        // Create dynamic columns 
        int ic = 0;
        foreach (TimeDS.PersonsRow drPerson in _dtPersons)
        {
            ic++;
            TemplateField tf = new TemplateField();
            tf.ItemTemplate =
                new GridViewTemplate(ListItemType.Item, drPerson.PersonId.ToString(), ic.ToString(), "Int32", false);
            tf.HeaderTemplate =
                new GridViewTemplate(ListItemType.Header, drPerson.Name.ToString(), ic.ToString(), "String", true);
            grdMain.Columns.Add(tf);
            _dtEntry.Columns.Add(drPerson.PersonId.ToString());
        }

        // Create rows in table
        foreach (TimeDS.ProjectsRow drProject in _dtProjects)
        {
            DataRow r = _dtEntry.NewRow();
            _dtEntry.Rows.Add(r);
            r[0] = drProject.ProjectName.ToString();
        }

        // Do PIVOT processing for TimeLog rows 
        foreach (TimeDS.TimeLogRow drTimeLog in _dtTimeLog)
        {
            int ie = 0;
            int ip = 0;
            foreach (TimeDS.PersonsRow drPerson in _dtPersons)
                if (drPerson.PersonId == drTimeLog.PersonId)
                    break;
                else
                    ie++;
            if (ie == _dtPersons.Rows.Count)
                throw new Exception("Unknown Person");
            foreach (TimeDS.ProjectsRow drProject in _dtProjects)
                if (drProject.ProjectId == drTimeLog.ProjectId)
                    break;
                else
                    ip++;
            if (ip == _dtProjects.Rows.Count)
                throw new Exception("Unknown Project");
            _dtEntry.Rows[ip][ie + 1] = drTimeLog.Hours.ToString();
        }

        // Set datasource to our newly created table and bind it to the grid
        grdMain.DataSource = _dtEntry;
        grdMain.DataBind();

        // Save row and colum definitions to ViewState
        ViewState.Add("_dtPersons", _dtPersons);
        ViewState.Add("_dtProjects", _dtProjects);
    }

The code above belongs to Ole E from Norway:
Data Entry application with PIVOT processing in GridView[^]