GridView - Changing FilterExpression of SQLDataSource and Paging

[Henry Habermacher [MVP Access]]

Environment: VS2005, ASP.Net 2.0, SQLServer 2000, VB.Net, IIS6

I use a GridView Control which is based on a SQLDataSource. The datasource
is based on a Select statement and is filtered by the FilterExpression. The
GridView also has a pager element (below, displaying first, previous, next,
last icons), paging is enabled, page contains 10 rows.

The user now skips some pages by clicking a view times on the next icon.
Now he changes the filter by entering a text in a text box.
This .Text property is thenafter used to set the
SQLDataSource.FilterExpression.

The displayed rows are correctly filtered and displayed, and paging works
fine. The only thing I don't understand is, why the pager doesn't reset to
page 1 if I change the FilterExpression. It just displays a page within the
resultset, not the first one. I even didn't find what page it displays, must
be an index or something it uses as it's not the same pagenumber as I had
before.
I also didn't find the method to request the first page after I've changed
the FilterExpression else I would use the textbox's TextChanged event to
reset the pager control to start at page 1.

Any idea how to do this? Or is it a bug? Do I have to do a workaround or
even stop using the FilterExpression and instead change the SQLCommand of
the SQLDataSource and manipulate the WHERE clause inside? I expect this
would result in disposing the cache and re-reading all records, what I don't
like for performance reasons.

Thanks for any hints on how to display the first page of a GridView control
programmatically after the FilterExpression of the SQLDataSource has
changed!

Greetings and TIA from Phuket, Thailand

Henry

 

[Henry Habermacher [MVP Access]]

Got the answer:
<GridName>.PageIndex = 0
works fine. Must have been blind!

Henry

quoting Henry Habermacher [MVP Access]:

 

[kmarchiony]

vb syntax to programmatically set filter expression?

vb.net 2.0

Hi there,
I have been struggling for some time to programmatically set my filter expression in my vb code. I'm able to get one parameter working;

VERSION 1 (works)

SqlDataSource2.FilterParameters.Add("ITEM_TYPE", TypeCode.String, ddItemType.SelectedValue.ToString())

SqlDataSource2.FilterExpression = "ITEM_TYPE='{0}'"

but my grid populates with no data once I add more than one item to my expression:

VERSION 2 (doesn't work)

SqlDataSource2.FilterParameters.Add("ORGCODE", TypeCode.String, ddOrg.SelectedValue.ToString())
SqlDataSource2.FilterParameters.Add("ITEM_TYPE", TypeCode.String, ddItemType.SelectedValue.ToString())
SqlDataSource2.FilterParameters.Add("INVITMSTAT", TypeCode.String, ddItemStatus.SelectedValue.ToString())
SqlDataSource2.FilterParameters.Add("PLNRCDE", TypeCode.String, ddPlannerCode.SelectedValue.ToString())

SqlDataSource2.FilterExpression = "ORGCODE ='{0}' AND ITEM_TYPE = '{1}' AND INVITMSTAT = '{2}' AND PLNRCDE = '{3}'"

You said you were able to get yours working just fine. Do you mind if I asked the format of your filter expression?