Search code examples
c#asp.netgridviewsqldatasource

asp:SqlDataSource SelectCommand property does not persist when paging


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


Solution

  • The SQLDataSource does not maintain any new SelectCommands 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.