Can't Update a GridView after filtering

[arnyinc]

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:


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:


<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>

[nikkiH]

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.

[arnyinc]

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.

[arnyinc]

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.

[nikkiH]

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.

[arnyinc]

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.

[nikkiH]

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.

[arnyinc]

That would be great. I really appreciate the help. I have to drive home now but I shall check and see later.

Thanks again.

[nikkiH]

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# :D


<%@ 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>



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;
}
}

[arnyinc]

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!

[nikkiH]

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.


<%@ 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>



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();

}
}