c# - asp:SqlDataSource SelectCommand property does not persist when paging -

i have gridview (gvpart) sqldatasource (sdsparts) data source. on gvpart, have property allowpaging="true". have textbox (txtpartsearch) , button used enter , exectue search through gvpart. this, have following in code behind:

protected void partsearch(object sender, eventargs e) {     string query = txtpartsearch.text;     string selectcmd = "select ... partnum '" + query + "%' ... "; // have cut out of statement clarity     sdsparts.selectcommand = selectcmd;     gvpart.databind(); } 

the intent of allow user enter part number, , have gvpart display parts match query instead of entire list.

the first page of gvpart after above method expected. however, if select statement results in more 1 page in gvpart, clicking page 2 in footer show second page, data page 2 of original data (that is, data pulled before search, default selectcommand in sdsparts).

it seems paging "resets" sqldatasource , uses selectcommand written in default.aspx, regardless of sdsparts.selectcommand = selectcmd statement.

i have tried leaving selectcommand out, sdsparts looks this:

<asp:sqldatasource id="sdsparts" runat="server" connectionstring="..." /> 

and adding default 1 in page_load:

protected void page_load(object sender, eventargs e) {     if (!ispostback)     {         string selectcmd = "select ... ";         sdsparts.selectcommand = selectcmd;         gvpart.databind();     } } 

but clicking on page in gvpart makes blank, if selectcommand="".

why selectcommand of sdsparts "reset," , how can fix/avoid this?

edit

i have solved problem. of coming here same problem, click here explanation , suggestion workaround.

edit moved above solution answer more clarity

the sqldatasource not maintain new selectcommands security purposes. thus, selectcommand property revert original value coded in .aspx file. possible solution use session variables so:

protected void partsearch(object sender, eventargs e) {     string query = txtpartsearch.text;     string selectcmd = "select ... partnum '" + query + "%' ... ";     sdsparts.selectcommand = selectcmd;     session["select"] = selectcmd; } 

and in page_load:

protected void page_load(object sender, eventargs e) {     if (session["select"] != null)         sdsparts.selectcommand = selectcmd;     else         sdsparts.selectcommand = "select ... "; //some default command } 

click here bit more info , see location of solution.

note: method of generating select statement above not recommended, invites sql injection attacks. recommended use sqldatasource parameters.