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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
mac-willCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.