Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts

    Can't Update a GridView after filtering

    I have a GridView that I want to filter and be sortable. The problem is when I set the filterexpression for my datasource I have to do a databind. After I do that, I can no longer do an inline update of the GridView.

    Here's my codebehind:

    Code:
        Protected Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
            If IsPostBack Then
                setSelectCommand()
            End If
    
        End Sub
    
        Protected Sub setSelectCommand()
            Dim MyFilter As String
    
            MyFilter = "1=1"
    
            If StatusFilter.SelectedValue <> "" Then
                MyFilter += " and vsid = '" & StatusFilter.SelectedValue & "'"
            End If
    
            If VnumberFilter.Text <> "" Then
                MyFilter += " and vnumber LIKE '%" & VnumberFilter.Text & "%'"
            End If
    
            If DepartmentFilter.SelectedValue <> "" Then
                MyFilter += " and dpid='" & DepartmentFilter.SelectedValue & "'"
            End If
    
            If UnitFilter.SelectedValue <> "" Then
                MyFilter += " and uid='" & UnitFilter.SelectedValue & "'"
            End If
    
            SqlDataSource1.FilterExpression = MyFilter
    
            GridView1.DataBind() 'Commenting out this line lets me update, but then I can't filter.
        End Sub
    Here's the gridview:

    Code:
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" EnableTheming="True" ForeColor="#333333" GridLines="None" PageSize="20" Width="752px" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" DataKeyNames="id" OnRowUpdating="GridView1_RowUpdating" DataMember="DefaultView">
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:BoundField DataField="vnumber" HeaderText="Vehicle Number" SortExpression="vnumber" />
                <asp:BoundField DataField="id" HeaderText="id" SortExpression="id" Visible="False" />
                    <asp:TemplateField HeaderText="Status" SortExpression="vehiclestatus">
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="vehiclestatus" DataValueField="id" SelectedValue='<%# Bind("vsid") %>' AppendDataBoundItems="True">
                                <asp:ListItem Selected="True"></asp:ListItem>
                            </asp:DropDownList>
                            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:VehicleDashboard.My.MySettings.vehicledashboardCS %>"
                                SelectCommand="SELECT [id], [vehiclestatus] FROM [vehiclestatus] ORDER BY [vehiclestatus]">
                            </asp:SqlDataSource>
                            </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# Bind("vehiclestatus") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                <asp:TemplateField HeaderText="Department" SortExpression="department">
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource3"
                            DataTextField="department" DataValueField="id" SelectedValue='<%# Bind("dpid") %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True"></asp:ListItem>
                        </asp:DropDownList>
                        <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:VehicleDashboard.My.MySettings.vehicledashboardCS %>"
                            SelectCommand="SELECT [id], [department] FROM [department] ORDER BY [department]">
                        </asp:SqlDataSource>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("department") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Unit" SortExpression="unit">
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("unit") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="SqlDataSource4" DataTextField="unit" DataValueField="id" SelectedValue='<%# Bind("uid") %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True"></asp:ListItem>
                        </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:VehicleDashboard.My.MySettings.vehicledashboardCS %>"
                            SelectCommand="SELECT [id], [unit] FROM [unit] ORDER BY [unit]"></asp:SqlDataSource>
                    </EditItemTemplate>
                </asp:TemplateField>
    
                <asp:HyperLinkField DataNavigateUrlFields="id,vnumber" DataNavigateUrlFormatString="detail.aspx?id={0}&amp;vnumber={1}" InsertVisible="False" Text="View Defect Log" />
                
     
              
            </Columns>
            
            
            <RowStyle BackColor="#EFF3FB" />
            <EditRowStyle BackColor="#2461BF" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:VehicleDashboard.My.MySettings.vehicledashboardCS %>"
            SelectCommand="exec sp_SelectVehicle"
            DeleteCommand="exec sp_DeleteVehicle @original_id"
            UpdateCommand="exec sp_UpdateVehicle @original_id, @dept_id, @vehiclestatus_id, @unit_id, @vnumber" 
            InsertCommand="exec sp_InsertVehicle @dept_id, @vehiclestatus_id, @unit_id, @vnumber" 
            OldValuesParameterFormatString="original_{0}"
            >
            <UpdateParameters>
                <asp:Parameter Name="id" Type="Int32" />
                <asp:Parameter Name="vnumber" Type="String" />
            </UpdateParameters>
            <DeleteParameters>
                <asp:Parameter Name="vid" Type="Int32" />
            </DeleteParameters>
         </asp:SqlDataSource>

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Can you clarify "can't"? What happens? Do you get an error?
    You may want to set datakeynames so if all else fails, just hang on to the edit item index and reset it after databind.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Yes, when I click Update it just shows my GridView with the original values (i.e. it ignores my changes). I don't get an error message, but clearly I am doing something wrong since it doesn't Update the database.

    Here's a quick step-through
    Open page and you see a GridView
    Click Edit and it opens the inline editor
    Change values in the inline editor form
    Click Update
    Page flashes (from postback?)
    You are effectively at the first screen you saw with the GridView and the same values.

  • #4
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    I think the problem is that the DataBind is clearing out my id variable. So when I update it is doing something like "update table set something='blah' where id=''"

    I don't know how to fix this though. Grr.

  • #5
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Well, I do wonder why you're resetting the filter on every postback instead of on particular events. Pretty much every event, including the update, will set off postback. That can't be a good thing...

    Hang on a few, I'll set up a little mock up using Northwind and see if I can use a gridview to update as well as the dynamic filter expression thing you're doing there.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #6
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    I may not need to do that. I need the filter to be reset every time someone changes one of the fields that are "filter-able". And when they click a Header to sort, the filtering needs to be maintained.

    I found a hacky way that seems to be working so far. I removed the databind from the setSelectCommand() sub which effectively removes it from Page_Load. I added an onclick for the filter form that just does the databind. So the filterexpression keeps getting updated but it only gets applied when someone clicks the button.

    I don't know.

  • #7
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    You're using client-side sorting by using the filter. Not sure if you realized that.
    I saw why you did that in page load when I started playing.
    I think I have a better solution; give me a few more minutes.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #8
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    That would be great. I really appreciate the help. I have to drive home now but I shall check and see later.

    Thanks again.

  • #9
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    This certainly doesn't solve all your issues, but the main thing you needed was to hold onto the datakey being updated. You'll have to play with how the behaviors work together and whatnot.
    But here's a little sample, using Northwind, for how to get that edit item index.
    Sorry, it's in C#

    Code:
    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default2.aspx.cs" Inherits="_Default2"   %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head>
        <title>Untitled Page</title>
    
    </head>
    <body>
    <form runat="server" id="Form1">
        <div>
            <asp:SqlDataSource ID="sda_customers" runat="server" ConnectionString='<%$ ConnectionStrings:NORTHWIND %> ' 
            SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM [Northwind].[dbo].[Customers]" 
            UpdateCommand="UPDATE [Northwind].[dbo].[Customers] SET [CompanyName]=@CompanyName, [ContactName]=@ContactName, [ContactTitle]=@ContactTitle, [Address]=@Address,[City]=@City, [Region]=@Region,[PostalCode]=@PostalCode,[Country]=@Country, [Phone]=@Phone, [Fax]=@Fax WHERE [CustomerID]=@CustomerID"
            ProviderName="System.Data.SqlClient" OldValuesParameterFormatString="original_{0}">
            </asp:SqlDataSource>
            <asp:Label ID="Label1" runat="server"></asp:Label><br />
            <br />
            Filter:
            <asp:TextBox ID="txtFilterExpression" runat="server"></asp:TextBox>
            &nbsp;
            <asp:Button ID="btnFilter" runat="server" OnClick="btnFilter_Click" Text="Button" /><br />
            &nbsp;<asp:GridView ID="gvCustomers" runat="server" AllowPaging="True" AllowSorting="True"
                DataSourceID="sda_customers" AutoGenerateColumns="False" DataKeyNames="CustomerID" OnRowCommand="gvCustomers_RowCommand" OnRowUpdated="gvCustomers_RowUpdated">
                <Columns>
                    <asp:CommandField ShowEditButton="True" />
                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />
                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
                    <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" />
                    <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                    <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
                    <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode" />
                    <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                    <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
                    <asp:BoundField DataField="Fax" HeaderText="Fax" SortExpression="Fax" />
                </Columns>
            </asp:GridView>
            </div>
    
        </form>
    </body>
    </html>
    Code:
    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    
    public partial class _Default2 : System.Web.UI.Page
    {
    
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                sda_customers.FilterExpression = txtFilterExpression.Text;
                gvCustomers.DataBind();
            }
    
        }
        protected void btnFilter_Click(object sender, EventArgs e)
        {
            
        }
       
        protected void gvCustomers_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Edit") ViewState["editing"] = gvCustomers.DataKeys[Convert.ToInt32(e.CommandArgument)].Value;
            
            Label1.Text = "key: " + (string)ViewState["editing"];
    
        }
        protected void gvCustomers_RowUpdated(object sender, GridViewUpdatedEventArgs e)
        {
            ViewState["editing"] = null;
        }
    }

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #10
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    That makes a lot more sense than the hack job I came up with. Definitely something I'll have to remember for the future.

    Awesome, thank you!

  • #11
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Just because I discovered more interesting things while playing with this, I thought I'd post code.
    The update works in full; if the user pages and the index is out of range, de-selects edit row. Same for filter. If user filters while editing, retains index IF it's on the page. Otherwise, de-selects.

    Code:
    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default2.aspx.cs" Inherits="_Default2"   %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head>
        <title>Untitled Page</title>
    
    </head>
    <body>
    <form runat="server" id="Form1">
        <div>
            <asp:SqlDataSource ID="sda_customers" runat="server" ConnectionString='<%$ ConnectionStrings:NORTHWIND %> ' 
            SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM [Northwind].[dbo].[Customers]" 
            UpdateCommand="UPDATE [Northwind].[dbo].[Customers] SET [CompanyName]=@CompanyName, [ContactName]=@ContactName, [ContactTitle]=@ContactTitle, [Address]=@Address,[City]=@City, [Region]=@Region,[PostalCode]=@PostalCode,[Country]=@Country, [Phone]=@Phone, [Fax]=@Fax WHERE [CustomerID]=@CustomerID"
            ProviderName="System.Data.SqlClient"
            >
            </asp:SqlDataSource>
            
            <asp:Label ID="Label1" runat="server"></asp:Label><br />
            <br />
            Filter:
            <asp:TextBox ID="txtFilterExpression" runat="server"></asp:TextBox>
            &nbsp;
            <asp:Button ID="btnFilter" runat="server" OnClick="btnFilter_Click" Text="Button" /><br />
            &nbsp;
            <asp:GridView ID="gvCustomers" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="true"
                DataSourceID="sda_customers" DataKeyNames="CustomerID" OnRowCommand="gvCustomers_RowCommand" OnRowUpdated="gvCustomers_RowUpdated"  AutoGenerateEditButton="True" OnRowUpdating="gvCustomers_RowUpdating">
            </asp:GridView>
            </div>
    
        </form>
    </body>
    </html>
    Code:
    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    
    public partial class _Default2 : System.Web.UI.Page
    {
    
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                sda_customers.FilterExpression = txtFilterExpression.Text;
                if (ViewState["updating"] == null ||
                    ViewState["updating"].ToString() == "false")
                    gvCustomers.DataBind();
    
                string key = (string)ViewState["editing"];
                int selectedindex = 0;
                bool found = false;
                if (key != null)
                {
                    foreach (DataKey dataKey in gvCustomers.DataKeys)
                    {
                        if (dataKey.Value.ToString() == key)
                        {
                            gvCustomers.EditIndex = selectedindex;
                            found = true;
                            break;
                        }
                        else selectedindex += 1;
                    }
                    if (!found) gvCustomers.EditIndex = -1;
                }
            }
    
        }
        protected void btnFilter_Click(object sender, EventArgs e)
        {
    
        }
    
        protected void gvCustomers_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Edit") ViewState["editing"] = gvCustomers.DataKeys[Convert.ToInt32(e.CommandArgument)].Value;
            if (e.CommandName == "Cancel") ViewState["editing"] = null;
            
            if (e.CommandName == "Update") ViewState["updating"] = "true";
            else ViewState["updating"] = "false";
    
            Label1.Text = "key: " + (string)ViewState["editing"];
            Label1.Text += " is updating: " + (string)ViewState["updating"];
    
        }
        protected void gvCustomers_RowUpdated(object sender, GridViewUpdatedEventArgs e)
        {
            ViewState["editing"] = null;
            ViewState["updating"] = "false";
            Label1.Text = "key: " + (string)ViewState["editing"];
            Label1.Text += " is updating: " + (string)ViewState["updating"];
        }
    
        protected void gvCustomers_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            string s = e.Keys[0].ToString();
            s = e.NewValues[0].ToString();
    
        }
    }

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •