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?

score 3 · Answer 1 · answered Sep 01 '13 at 04:12

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

score 1 · Answer 2 · answered Mar 24 '12 at 18:31

string strSql= "SELECT  * from tblCourse where name='abc'";
ViewState["SQL"]=strSql;
SqlDataSource1.SelectCommand =strSql;        
SqlDataSource1.DataBind();

Now in the Page_Load

if(IsPostback)
     SqlDataSource1.SelectCommand=ViewState["SQL"].ToString();

score 0 · Answer 3 · edited Oct 21 '12 at 12:06

0

Set autogenerate to true and be sure to remove all the fields in the edit fields wizard. If you change the select command it causes the conflict.

edited Oct 21 '12 at 12:06

Uwe Keim

39,551
56
175
291

answered Jun 06 '12 at 10:05

Jacque

1

score 0 · Answer 4 · edited Mar 06 '13 at 16:01

0

Try adding the following :

 SqlDataSource.select(DataSourceSelectArguments.Empty);

before the line SqlDataSource.DataBind();

edited Mar 06 '13 at 16:01

Hugo Dozois

8,147
12
54
58

answered Mar 06 '13 at 15:40

geo

1

score 0 · Answer 5 · answered Mar 24 '12 at 18:28

0

You can either edit your BoundField's or change the property AutoGenerateColumns to true.

answered Mar 24 '12 at 18:28

shriek

5,157
2
36
42

Change select command of sqldatasource at runtime

5 Answers5