How to add a filtering capability to a gridview which is populated by a stored procedure

Hi Experts,
 
I have a standard AJAX toolkit for .NET framework 2.0 gridview which gets populated by a stored procedure. My database connection uses a connection string from the web.config.

Could you give me a step-by-step on how to add the filter row in the header of the gridview, and hook up the filtering capability to the gridview's data population?

When the page loads I call this:
    public DataTable GetJobStatus(string JobName)
    {
        DataSet ds = new DataSet();
        DbCommand dbCommand = _database.GetStoredProcCommand("proc_CD_Web_GetJobStatus");
        _database.AddInParameter(dbCommand, "JobName", DbType.String, JobName);
        ds = _database.ExecuteDataSet(dbCommand);

        return ds.Tables[1];
    }

When a page loads or a row is deleted or added, I call UpdateGrid() which calls the above to get the datatable and binds that datatable to the gridview.

I'm confused as to how I hook up this filtering capabilitying into my existing process. I guess I have to add each column as a parater into the stored proc, then add all of them as parameters into each function? How do I get the searched for text from the gridview to the code behind?

It's urgent. Really appreciate your help!

rss2
rss2Asked:
Who is Participating?
 
mac-willConnect With a Mentor Commented:
You can also use the DataTable.DefaultView.RowFilter.

EXAMPLE:

myDataTable.DefaultView.RowFilter = " name LIKE '%john%'";

This filters the view but not the actual datatable, usually this is much more useful.

MAC
0
 
rss2Author Commented:
I guess, if the dataset is relatively small, it is better to search the data from the datatable object rather than make a trip to the server..?

How do I search the datatable?
0
 
Fernando SotoRetiredCommented:
Hi rss2;

Use the DataTable.Select method to return an array of DataRow that meet the Expression. For example

DataRow[ ] selectedRows = DataTableName.Select("ColumnName = ValueToFind");

The following link will show how to build an Expression.
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.71).aspx

Fernando
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rss2Author Commented:
Mac-Will, what do I do after that? How do I display if rows are found? How do I check if rows are found, then refresh the view of the gridview?

Thanks!

rss2
0
 
Fernando SotoRetiredCommented:
Hi rss2;

If you use mac-will solution all rows NOT matching the RowFilter expression will be hidden from view in the grid view leaving only the rows that match the filter displayed in the grid view.

Fernando
0
 
rss2Author Commented:
Could anyone give me a code example?
0
 
Fernando SotoRetiredCommented:
Post your code and what you want the outcome to be and we can modify your code.
0
 
rss2Author Commented:
I have attached the aspx and code-behind.

Firstly, I don't want the header row to have the filter dropdowns. I want the dropdowns to either be above the header row or just below it. Could you tell me how to write that into the aspx code?

I'm using the RowFilter approach. I think I've inadvertently coded in a conflict of interest between my UpdateGrid() function and the BindGridView() function. I get an error on the Databind() in the BindGridView() function. The error is:
A field or property with the name CurveOverride was not found on the selected data source."

Do I need to give all the column names of the filtered view before binding it to the gridview?

Thank you!!! :)

rss2

aspx.txt
code-behind.txt
0
 
Fernando SotoRetiredCommented:
Hi rss2;

First let me say that I am not a web developer and my knowledge in this area is limited and with that said:

To the questions, "Firstly, I don't want the header row to have the filter dropdowns. I want the dropdowns to either be above the header row or just below it. Could you tell me how to write that into the aspx code?"

I am not able to help with this.

To this question, "I'm using the RowFilter approach. I think I've inadvertently coded in a conflict of interest between my UpdateGrid() function and the BindGridView() function.",

UpdateGrid() does not call  BindGridView() and both functions create a new DataGridView object with its own filtering / Sorting parameters. The only issue I see with that is if the GridView was sorted after calling BindGridView() it will go back to the original sort order and new RowFilter. You may want to think about storing the DataView object In the view state or other place so that you use the same DataView and modify on the part that needs such as Sort or RowFilter.

To this statement, "The error is: A field or property with the name CurveOverride was not found on the selected data source."

Seeming that in BindGridView() you are using the field name Curve and NOT CurveOverride I think this may be generating some where else maybe by this statement in UpdateGrid():

string Sort = this.CurrentSortCol + " " + this.CurrentSortDir;
DataView dataView = new DataView(dt);

To the question, "Do I need to give all the column names of the filtered view before binding it to the gridview?"

You only need the names of the columns and there values of the columns you will be filtering on.

Fernando
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.