Changing SqlDataSource.SelectCommand at runtime pagination

Question

I have a GridView associated with 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 times when I have to dynamically change this request 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 fine, but when I click on pagination controls, the default result set returns to SelectCommand defined in SqlDataSource1 .

How to get around this?

Thanks Mark

Answer 1

The problem is that the SqlDataSource is restored when the page is loaded using the submission provided by the Pager links. This is not to say that it downloads what you installed dynamically. If you must use a stored procedure with parameters, then ASP will save the parameters to ViewState and re-run the selection in SqlDataSource when the page loads.

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

The easiest way to do this is to save the SQL in the ViewState when installing SelectCommand from SqlDataSource, and then return it again to the Page_Load event and set it back.

For example: suppose you have a TextBox for some criteria and a search button. When a user enters any text into a TextBox and then clicks the Search button, you want him to create some SQL (this, by the way, gives you a huge threat to SQL Injection attacks. Criteria). Then set the SqlDataSource SelectCommand property. This means that you want to save SQL. Then, in the Page_Load event, you want to get it and set the SelectCommand property to this value.

In Click of your button you can save 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 can get SQL and set the SelectCommand property:

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

Answer 2

1) Try to set the data source from the page preview, not dynamically. This is the clearest solution. If you cannot,

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