rss2
asked on
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.GetStoredProcCom
_database.AddInParameter(d
ds = _database.ExecuteDataSet(d
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
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.GetStoredProcCom
_database.AddInParameter(d
ds = _database.ExecuteDataSet(d
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
Hi rss2;
Use the DataTable.Select method to return an array of DataRow that meet the Expression. For example
DataRow[ ] selectedRows = DataTableName.Select("Colu
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
Use the DataTable.Select method to return an array of DataRow that meet the Expression. For example
DataRow[ ] selectedRows = DataTableName.Select("Colu
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks!
rss2
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
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
ASKER
Could anyone give me a code example?
Post your code and what you want the outcome to be and we can modify your code.
ASKER
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
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
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
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
ASKER
How do I search the datatable?