asked on

Trouble updating SELECT statement on Gridview

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>

Open in new window

arhame

I think there is an easier way to do this. The way that I do it is by using the LIKE clause and wildcards - and set the default value of the clause to a wildcard (%) as well. Take a look at this datasource where I have a textbox1 people can enter in a last name, and on the textbox1.textchanged event I just redatabind.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mepcoConnectionString %>"
SelectCommand="SELECT EmployeeNumber, EmployeeFirstName + ' ' + EmployeeLastName AS FullName, EmployeeId FROM Employee WHERE (EmployeeLastName LIKE '%' + @EmployeeLastName + '%') ORDER BY EmployeeLastName"
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" DefaultValue="%" Name="EmployeeLastName"
PropertyName="Text" />
</SelectParameters>

arhame

Using the default value="%" allows for a full gridview when the page is loaded, but if somethign is typed into textbox1 and then the textbox1.textchanged event is triggered (nothing more than redatabind the gridview) it'll take whatever is typed into the box into account.

P1ST0LPETE

ASKER

Ok, attached is how my SqlDataSource looks now. Before I added the selectparameters, the edit funtionality on the gridview was working correctly with no problems. Now I have added the selectparameters and have search filter working. However, when I go to edit a row, and click the update button, it deletes all the fields that are set to readonly in the Gridview. What the heck is going on?

<asp:SqlDataSource 
    ID="DataSource_EC" runat="server" 
    ConnectionString="<%$ ConnectionStrings:conStr %>"
    SelectCommand="SELECT [ContactID], [PrivateID], [List], [FirstName], [LastName] FROM [Contacts] WHERE ([PrivateID] LIKE '%' + @PrivateID) AND ([FirstName] LIKE @FirstName + '%') AND ([LastName] LIKE @LastName + '%')"
    UpdateCommand="UPDATE [Contacts] SET [PrivateID] = @PrivateID, [List] = @List, [FirstName] = @FirstName, [LastName] = @LastName WHERE [ContactID] = @ContactID">
    <selectparameters>
         <asp:ControlParameter 
            Name="PrivateID"
            DefaultValue="%" 
            ControlID="ddl_EC_SearchPrivateID" 
            PropertyName="SelectedValue" />
        <asp:ControlParameter 
            ControlID="tb_EC_FirstName" 
            DefaultValue="%" 
            Name="FirstName" 
            PropertyName="Text" />
        <asp:ControlParameter 
            ControlID="tb_EC_LastName" 
            DefaultValue="%" 
            Name="LastName" 
            PropertyName="Text" />
    </selectparameters>
    <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>

Open in new window

arhame

That is odd... changing the selectCommand and the SelectParameters shouldn't affect your update at all. Are the fields that are "read only" the privateID, list, firstname, lastname, or contactID? If so just take those out of the update command and updateparameters. Only have the ones that you want to update there.

ASKER CERTIFIED SOLUTION

arhame

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

P1ST0LPETE

ASKER

Thanks for all the help. Sorry for the late rewarding of points - been out of town for Memorial Day. :-)