I have a GridView (gvPart)
with a SqlDataSource (sdsParts)
as its data source. On gvPart
, I have the property AllowPaging="true"
. I also have a TextBox (txtPartSearch)
and a Button
that are used to enter and exectue a search through gvPart
. To do this, I have the following in code behind:
protected void partSearch(object sender, EventArgs e)
{
string query = txtPartSearch.Text;
string selectCmd = "SELECT ... WHERE partnum LIKE '" + query + "%' ... "; // I have cut out most of the statement for clarity
sdsParts.SelectCommand = selectCmd;
gvPart.DataBind();
}
The intent of this is to allow the user to enter a part number, and have gvPart
display only parts that match the query instead of the entire list.
The first page of gvPart
after the above method is as expected. However, if this select statement results in more than one page in gvPart
, clicking page 2 in the footer will show the second page, but the data will be from page 2 of the original data (that is, the data that was initially pulled before the search, with the default SelectCommand
in sdsParts
).
It seems like paging "resets" the SqlDataSource
and uses the SelectCommand
that is written in Default.aspx, regardless of any sdsParts.SelectCommand = selectCmd
statement.
I have tried leaving a SelectCommand
completely out, so that sdsParts
looks like this:
<asp:SqlDataSource ID="sdsParts" runat="server" ConnectionString="..." />
and then adding the default one in Page_Load
:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string selectCmd = "SELECT ... ";
sdsParts.SelectCommand = selectCmd;
gvPart.DataBind();
}
}
but then clicking on another page in gvPart
makes it blank, as if SelectCommand=""
.
Why does the SelectCommand
of sdsParts
"reset," and how can I fix/avoid this?
EDIT
I have solved my problem. For those of you coming here with the same problem, click here for an explanation and suggestion for a workaround.
EDIT Moved my above solution to an answer for more clarity
The SQLDataSource
does not maintain any new SelectCommand
s for security purposes. Thus, the SelectCommand
property will revert to its original value as coded in the .aspx
file. A possible solution is to use the Session
variables like so:
protected void partSearch(object sender, EventArgs e)
{
string query = txtPartSearch.Text;
string selectCmd = "SELECT ... WHERE partnum LIKE '" + 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 for a bit more info and to see the location of my solution.
NOTE: my method of generating the select statement above is not recommended, as it invites SQL injection attacks. It is recommended to use the SqlDataSource Parameters
.