troubleshooting Question

Trouble updating SELECT statement on Gridview

Avatar of P1ST0LPETE
P1ST0LPETEFlag for United States of America asked on
.NET ProgrammingASP.NET
6 Comments1 Solution430 ViewsLast Modified:
Hi All,

I have a Gridview which is populated via a SqlDataSource Control.  Attached is the actual code.  When the page is first loaded I want the Gridview to pull all the records and display them, which is what it is doing now, and working properly.  However, I want the user to be able to search the DB table and filter the records displayed, which means I need to change/alter the SelectCommand of the DataSource control.  This is where I am getting hung up.  I have the following in the code behind for the button to test if I can change the SelectCommand via button click:

DataSource_EC.SelectCommand = "SELECT * FROM [Contacts] WHERE [FirstName] = Pete";
gvEditContacts.DataBind();

This should filter the Gridview to only show the records where the firstname is Pete.  However, all the records are returned on postback.  I could be wrong, but I understand this is happening because the SelectCommand is in the DataSource control which is recreated on postback - however I don't know exactly how to alter this to make it work right.  I've tried taking the SelectCommand out, and placing it in the Page_Load() event, but when I do that the Gridview disappears when a "Edit" button is pressed on one of the rows.  Please Advise.
Button:
<asp:Button 
        ID="bt_EC_Search" 
        runat="server" 
        Text="Search" 
        OnClick="EditContactSearch" />
 
SqlDataSource Control and Gridview:
<asp:SqlDataSource 
    ID="DataSource_EC" runat="server" 
    ConnectionString="<%$ ConnectionStrings:conStr %>"
    SelectCommand="SELECT * FROM [Contacts]"
    UpdateCommand="UPDATE [Contacts] SET [PrivateID] = @PrivateID, [List] = @List, [FirstName] = @FirstName, [LastName] = @LastName WHERE [ContactID] = @ContactID">
    <UpdateParameters>
        <asp:Parameter Type="String" Name="PrivateID" />
        <asp:Parameter Type="String" Name="List" />
        <asp:Parameter Type="String" Name="FirstName" />
        <asp:Parameter Type="String" Name="LastName" />
        <asp:Parameter Type="String" Name="ContactID" />
    </UpdateParameters>
</asp:SqlDataSource>
<asp:Panel 
    ID="pnl_EC_Gridview" 
    runat="server" 
    HorizontalAlign="Center" 
    Height="207px">
<asp:GridView 
    ID="gvEditContacts" 
    runat="server"
    ShowHeader="true"
    ShowFooter="false"
    AutoGenerateColumns="false" 
    DataKeyNames="ContactID"
    Width="370px"
    DataSourceID="DataSource_EC"
    RowStyle-BackColor="White"
    AllowPaging="true" 
    PagerSettings-Mode="NumericFirstLast" 
    PageSize="6"
    AlternatingRowStyle-BackColor="#F0F0F0">
    <Columns>
        <asp:CommandField 
            ButtonType="Button" 
            ShowEditButton="True" 
            HeaderText="Edit" />
        <asp:BoundField
            DataField="ContactID" 
            Visible="false" />    
        <asp:BoundField
            DataField="PrivateID" 
            HeaderText="Owner ID"
            ControlStyle-Width="50px"
            ItemStyle-HorizontalAlign="Center" />
        <asp:BoundField 
            DataField="List" 
            HeaderText="List"
            ControlStyle-Width="50px"
            ItemStyle-HorizontalAlign="Center" />
        <asp:BoundField 
            DataField="FirstName" 
            HeaderText="First Name"
            ReadOnly="true"
            ItemStyle-HorizontalAlign="Center" />
        <asp:BoundField 
            DataField="LastName" 
            HeaderText="Last Name"
            ReadOnly="true"
            ItemStyle-HorizontalAlign="Center" />
    </Columns>
</asp:GridView>
</asp:Panel>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros