Avatar of P1ST0LPETE
P1ST0LPETEFlag for United States of America 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

.NET ProgrammingASP.NET

Avatar of undefined
Last Comment
P1ST0LPETE

8/22/2022 - Mon
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.
ASKER
P1ST0LPETE

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
P1ST0LPETE

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