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

The issue here is that the SqlDataSource is getting re-created when the page loads upon the submit issued by the Pager links. There is nothing to tell it to load what you had set dynamically. If you were to use a stored procedure with parameters then ASP would save off the parameters to ViewState and re-run the select in the SqlDataSource when the page loaded.

So what you have to do is tell the the SqlDataSource what it had for SQL when it last loaded correctly.

The simplest way to do that is to store the SQL in ViewState when you set the SelectCommand of the SqlDataSource and then retrieve it again in the Page_Load event and set it back.

For instance: Let's say you have a TextBox for some criteria and a Search button. When the user enters some text into the TextBox and then clicks on the "Search" button, you want it to build up some SQL (This, by the way, leaves you with a huge exposure to SQL Injection attacks. Make sure you scrub your criteria well.) and then set the SqlDataSource's SelectCommand property. It is a t this point that you would want to save off the SQL. Then in the Page_Load event you would want to retrieve it and set the SelectCommand property to that value.

In the Click of your button you could store the SQL:

Dim sSQL as String

sSQL = "SELECT somefields FROM sometable WHERE somefield = '" & Me.txtCriteria.Text & "'"
SqlDataSource1.SelectCommand = sSQL
ViewState("MySQL") = sSQL

Then in the Page_Load event you could retrieve the SQL and set the SelectCommand property:

Dim sSQL as String

If Me.IsPostBack() Then
    sSQL = ViewState("MySQL")
    SqlDataSource1.SelectCommand = sSQL
End If