Change select command of sqldatasource at runtime

Question

HTML

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
   <Columns>
       <asp:BoundField DataField="id" HeaderText="id"  />
       <asp:BoundField DataField="name" HeaderText="name" />
   </Columns>
 </asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:database1ConnectionString %>"
SelectCommand="SELECT * from tblCourse"></asp:SqlDataSource>

Code

 SqlDataSource1.SelectCommand =
        "SELECT  * from tblCourse where name='"+textbox1.text+"'";
  SqlDataSource1.DataBind();

But Gridview does not change based on the new select command, even when I'm using DataBind()

How can we change grid view base on select command of sql data source?

Answer 1

This is happening because of GridView's viewstate.

When postback happens, gridview stores its data from ViewState. So, You can either turn off view state for GridView ( a good practice ?? ) OR you call GridView.DataBind() in addition to SqlDataSource.Databind();

METHOD 1: Calling GridView.DataBind();

protected void Page_Load(object sender, EventArgs e)
 {
     if (this.IsPostBack)
     {
        string command = SqlDataSource1.SelectCommand; // added just for debug purpose
        SqlDataSource1.SelectCommand = "SELECT  * from tblCourse where 
                                        name='"+textbox1.text+"'";
        SqlDataSource1.DataBind();
        gridview1.DataBind();
      }

  }

METHOD 2: Turn off View State for GridView ( Is this a good Practice? ). When you set this false, there is NO need to call GridView.DataBind() in your page_Load as seen in above METHOD 1.

<asp:GridView runat="server" ID="gridview1" EnableViewState="false" ...  />

Now the part comes that should must be taken care of::

Make sure the <asp:BoundField> or in general any fields declared and bound to GridView markup are also present in your new query else an error will be thrown saying something similar as below:

A field or property with the name 'ID' was not found on the selected data source