[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-07
9
Medium Priority
?
343 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:rss2
  • 4
  • 4
9 Comments
 

Author Comment

by:rss2
ID: 22903686
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 22903832
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
 
LVL 8

Accepted Solution

by:
mac-will earned 2000 total points
ID: 22905590
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:rss2
ID: 22929692
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 22930388
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
 

Author Comment

by:rss2
ID: 22930721
Could anyone give me a code example?
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 22931319
Post your code and what you want the outcome to be and we can modify your code.
0
 

Author Comment

by:rss2
ID: 22931965
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 22933789
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question