Changing SqlDataSource.SelectCommand at runtime breaks pagination

Question

I have a GridView bound to a SqlDataSource with a default SelectCommand defined as such:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"
      ConnectionString="<%$ ConnectionStrings:MyConn %>" 
      ProviderName="MySql.Data.MySqlClient" 
      SelectCommand="select * from blah blah" />

There are cases where I have to change this query dynamically at runtime, so I do the following:

SqlDataSource1.SelectCommand = sql; // 'sql' is the new query
GridView1.PageIndex = 0;
GridView1.EditIndex = -1;
GridView1.SelectedIndex = -1;
GridView1.DataBind();
updatePanel.Update();

This works just fine actually, but when I click the pagination controls, the result set defaults back to the SelectCommand defined in the SqlDataSource1.

Any way around this?

Thanks, Mark

Answer 1

1) Try set datasource from the page preview, not dynamically. This is the most clear solution If you can't,

2) Try to generate the list before page_load. I think placing code under page_init might do the trick. I can't remember exactly at the moment, but there is a method before page_load.