Change selectcommand of accessdatasource in code and sorting or paging

[mlfblom]

Hi,

I know I am not the only one struggling with the following, but so far
I have not seen a solution.

Problem:
I have an asp.net 2.0 web page with a gridview which is bound to an
accessdatasource. In my code-behind I want to be able to set the
selectcommand of the datasource to a specific SQL string based on a
user's input to retrieve only certain records.

This works fine when I hit the search button. BUT, as soon as I start
paging or sorting (gridview's default), the accessdatasource seems to
revert back to the initial select command "SELECT * FROM tblProduct"
and all records are shown again.

I know that I could maintain state for the selectcommand using session
variables, but I would like to see a cleaner solution.

My code in aspx:
<asp:AccessDataSource ID="AccessDataSource1"
DataFile="~/App_Data/product.mdb" SelectCommand="SELECT * FROM
tblProduct" runat="server" DataSourceMode=DataSet />

My code in code-behind:
AccessDataSource1.SelectCommand = "SELECT * FROM tblProduct WHERE
[brand]='" & txtBrandCustom.Text & "'"

Hopefully, anyone out there will have a solution for me!

Regards,

Mark