Link to records with a specific letter in a GridView

I have an ASP.NET GridView in a Web Form. This GridView using a SqlDataSource and utilizes paging and searching. For the sake of reference, here is the trimmed down version of it:

<asp:SqlDataSource ID="myDataSource" runat="server"
  ConnectionString="<%$ ConnectionStrings:DB %>"
  SelectCommand="SELECT p.[ID], p.[Name], p.[Email], l.[State], l.[City] FROM [Person] p, [Location] l WHERE p.[LocationID]=l.[ID] ORDER开发者_运维百科 BY p.[Name]"
  UpdateCommand="UPDATE [Person] SET [Email] = @Email WHERE [ID] = @ID"
/>

<asp:GridView ID="myGridView" runat="server" DataSourceID="myDataSource" 
  AllowPaging="true" AllowSorting="true" PageSize="25" AutoGenerateEditButton="true"   
  DataKeyNames="ID">
  <Columns>
    <asp:BoundField DataField="ID" Visible="false" />
    <asp:TemplateField HeaderText="Person" SortExpression="Name">                                        
      <ItemTemplate>
        <asp:Literal ID="nameLit" runat="server" Text='<%# Bind("Name") %>' />
      </ItemTemplate>                                    
    </asp:TemplateField>
    <asp:BoundField DataField="Email" SortExpression="Email" HeaderText="Email Address" />
    <asp:TemplateField HeaderText="Location" SortExpression="City">
      <ItemTemplate>
        <asp:Literal ID="cityLit" runat="server" Text='<%# Bind("City") %>' />
        <asp:Literal ID="stateLit" runat="server" Text='<%# Bind("State") %>' />
      </ItemTemplate>
    </asp:TemplateField>     
  </Columns>
</asp:GridView>

The data source has a lot of records. At least 25,000. Because of this, I want to allow the user to filter by state. I want to provide a drop down list with a list of states. When a user changes the drop down list, the results in the grid view will be filtered by the selected state. However, I do not know how to do this with a SqlDataSource. Can someone please explain to me how to accomplish this?

Thank you!

First of all, let's add the DropDownList, and a SqlDataSource to populate it:

<asp:dropdownlist runat="server" id="StateDropDownList" DataSourceId="StateDataSource" DataTextField="State" DataValueField="State" />
<asp:sqldatasource runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" SelectCommand="SELECT DISTINCT State FROM Location" />

Then we change your existing data source to use a State parameter and read the parameter's value from the DropDownList:

<asp:SqlDataSource ID="myDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" 

SelectCommand="SELECT p.[ID], p.[Name], p.[Email], l.[State], l.[City] FROM 
[Person] p INNER JOIN [Location] l ON p.[LocationID]=l.[ID] 
WHERE (l.State = @State) ORDER BY p.[Name]"

UpdateCommand="UPDATE [Person] SET [Email] = @Email WHERE [ID] = @ID"
<SelectParameters>
   <asp:ControlParameter ControlID="StateDropDownList" Name="State" 
        PropertyName="SelectedValue" Type="String" />
</SelectParameters>
/>

That should be all you need.

BTW, top tip: I changed your query slightly to use INNER JOIN syntax rather than doing the table join in a WHERE clause. There's some great info here on INNER JOIN.
Top tip #2: Paging in the GridView is a bit naive - every time you change pages, the GridView is reading all 25 000 records in your table, then throwing away the ones it doesn't need. There's a great article here (and plenty of questions on SO!) on doing custom paging with a Gridview.