ASP.NET Formview does not update SQL database correctly

Question

I have an asp.net Formview associated with an SQL data source. When I create / edit / delete a record, the column data is erased. I'm sure this is some simple incorrect encoding, since everything I know about SQL / asp.net has been processed in the last couple of weeks.

Here is the SQLDataSource code.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:hulc01ConnectionString %>" SelectCommand="SELECT * FROM [Common]" InsertCommand="INSERT INTO [Common] ([Project_Name], [Business_Category], [Project_Description], [Operations_Owner]) VALUES (@ProjectName, @BusinessCategory, @ProjectDescription, @OperationsOwner)" DeleteCommand="DELETE FROM [Common] WHERE [ProjectKey] = @ProjectKey" UpdateCommand="UPDATE [Common] SET [Project_Name] = @ProjectName, [Business_Category] = @BusinessCategory, [Project_Description] = @ProjectDescription, [Operations_Owner] = @OperationsOwner WHERE ProjectKey=@ProjectKey " > <DeleteParameters> <asp:Parameter Name="ProjectKey" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ProjectKey" Type="Int32" /> <asp:Parameter Name="ProjectName" Type="String" /> <asp:Parameter Name="BusinessCategory" Type="String" /> <asp:Parameter Name="ProjectDescription" Type="String" /> <asp:Parameter Name="OperationsOwner" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ProjectName" Type="String" /> <asp:Parameter Name="BusinessCategory" Type="String" /> <asp:Parameter Name="ProjectDescription" Type="String" /> <asp:Parameter Name="OperationsOwner" Type="String" /> </InsertParameters> </asp:SqlDataSource> 

And here is the form code

 <asp:FormView ID="FormView1" runat="server" AllowPaging="True" DataKeyNames="ProjectKey" DataSourceID="SqlDataSource1" Width="249px"> <EditItemTemplate> Project Name: <asp:TextBox ID="ProjectName" runat="server" Text='<%# Bind("Project_Name") %>' /> <br /> Business Category: <asp:TextBox ID="BusinessCategory" runat="server" Text='<%# Bind("Business_Category") %>' /> <br /> Project Description: <asp:TextBox ID="ProjectDescription" runat="server" Text='<%# Bind("Project_Description") %>' /> <br /> Operations Owner: <asp:TextBox ID="OwnerTextBox" runat="server" Text='<%# Bind("Operations_Owner") %>' /> <br /> <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update" Text="Update" /> &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" /> </EditItemTemplate> <InsertItemTemplate> Project Name: <asp:TextBox ID="ProjectName" runat="server" Text='<%# Bind("Project_Name") %>' /> <br /> Business Category: <asp:TextBox ID="BusinessCategory" runat="server" Text='<%# Bind("Business_Category") %>' /> <br /> Project Description: <asp:TextBox ID="Description" runat="server" Text='<%# Bind("Project_Description") %>' /> <br /> Operations Owner: <asp:TextBox ID="TitleTextBox" runat="server" Text='<%# Bind("Operations_Owner") %>' /> <br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" /> &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" /> </InsertItemTemplate> <ItemTemplate> Project Name: <asp:Label ID="ProjectNameLabel" runat="server" Text='<%# Bind("Project_Name") %>' /> <br /> Business Category: <asp:Label ID="BusinessCategoryLabel" runat="server" Text='<%# Bind("Business_Category") %>' /> <br /> Project Description: <asp:Label ID="ProjectDescriptionLabel" runat="server" Text='<%# Bind("Project_Description") %>' /> <br /> Operations Owner: <asp:Label ID="OwnerLabel" runat="server" Text='<%# Bind("Operations_Owner") %>' /> <br /> <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" /> &nbsp;<asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" /> &nbsp;<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New" /> </ItemTemplate> </asp:FormView> 

Thanks!

Answer 1

Your DeleteCommand and UpdateCommand commands do not use the same key as the DataKeyNames FormView . They should be something like this:

 DeleteCommand="DELETE FROM [Common] WHERE [Project_Key] = @ProjectKey" UpdateCommand="UPDATE [Common] " + "SET [Project_Name] = @ProjectName, [Business_Category] = @BusinessCategory, [Project_Description] = @ProjectDescription, [Operations_Owner] = @OperationsOwner" + "WHERE Project_Key=@ProjectKey >" 

The DataKeyNames property is used to control automatic UPDATE and DELETE performed using FormView , so it must be the primary key in your table, and it must match in all three places (UpdateCommand, DeleteCommand, and DataKeyNames). From MSDN :

Use the DataKeyNames property to specify a list of field names, separated by commas, that represent the primary key of the data source.

(In the sample code on this page, you will see that the parameter used in the WHERE clause for UpdateCommand matches the value specified in the DataKeyNames FormView )

As a side note, in the code you provided, your UpdateCommand does not use the where clause at all. This means that it will update every record in your table with the same set of values. FYI =)

Edit: When you use a FormView like this, you don't need the UpdateParameters settings the way you use them (since you use two-way data binding on the controls in your templates for editing / pasting). Try this for your SQLDataSource

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:hulc01ConnectionString %>" SelectCommand="SELECT * FROM [Common]" InsertCommand="INSERT INTO [Common] ([Project_Name], [Business_Category], [Project_Description], [Operations_Owner]) VALUES (@Project_Name, @Business_Category, @Project_Description, @Operations_Owner)" DeleteCommand="DELETE FROM [Common] WHERE [ProjectKey] = @ProjectKey" UpdateCommand="UPDATE [Common] SET [Project_Name] = @Project_Name, [Business_Category] = @Business_Category, [Project_Description] = @Project_Description, [Operations_Owner] = @Operations_Owner WHERE ProjectKey=@ProjectKey " > </asp:SqlDataSource> 

I usually use UpdateParameters, DeleteParameters, etc. when I do not use FormView , or I need to filter them based on the control outside of FormView . Let me know if this does not do it for you.