ASP.NET Formview not correctly updating SQL Database

Question

I have an asp.net Formview connected to an SQL datasource. When I create/edit/delete a record the column data is erased. I'm sure it's some simple incorrect coding as everything I know about SQL/asp.net has been googled over the past couple 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 formview code

<asp:FormView ID="FormView1" runat="server" AllowPaging="True" 
        DataKeyNames="ProjectKey" 
        DataSourceID="SqlDataSource1" 
        Width="249px">
        <EditItemTemplate>
            Project Name:
            <asp:TextBox runat="server" 
                ID="ProjectName" 
                Text='<%# Bind("Project_Name") %>' />        
            <br />
            Business Category:
            <asp:TextBox runat="server" 
                ID="BusinessCategory" 
                Text='<%# Bind("Business_Category") %>' />
            <br />
            Project Description:
            <asp:TextBox runat="server" 
                ID="ProjectDescription" 
                Text='<%# Bind("Project_Description") %>' />
            <br />
            Operations Owner:
            <asp:TextBox runat="server" 
                ID="OwnerTextBox" 
                Text='<%# Bind("Operations_Owner") %>' />
            <br />
            <asp:LinkButton runat="server" 
                ID="UpdateButton" 
                CausesValidation="True" 
                CommandName="Update" 
                Text="Update" />
            &nbsp;<asp:LinkButton runat="server" 
                ID="UpdateCancelButton" 
                CausesValidation="False" 
                CommandName="Cancel" 
                Text="Cancel" />
        </EditItemTemplate>

        <InsertItemTemplate>
           Project Name:
            <asp:TextBox runat="server" 
                ID="ProjectName" 
                Text='<%# Bind("Project_Name") %>' />        
            <br />
            Business Category:
            <asp:TextBox runat="server" 
                ID="BusinessCategory"
                Text='<%# Bind("Business_Category") %>' />
            <br />
            Project Description:
            <asp:TextBox runat="server" 
                ID="Description" 
                Text='<%# Bind("Project_Description") %>' />
            <br />
            Operations Owner:
            <asp:TextBox runat="server" 
                ID="TitleTextBox" 
                Text='<%# Bind("Operations_Owner") %>' />
            <br />
            <asp:LinkButton runat="server" 
                ID="InsertButton" 
                CausesValidation="True" 
                CommandName="Insert" 
                Text="Insert" />
            &nbsp;<asp:LinkButton runat="server" 
                ID="InsertCancelButton" 
                CausesValidation="False" 
                CommandName="Cancel" 
                Text="Cancel" />
        </InsertItemTemplate>
        <ItemTemplate>
            Project Name:
            <asp:Label runat="server" 
                ID="ProjectNameLabel" 
                Text='<%# Bind("Project_Name") %>' />
            <br />
            Business Category:
            <asp:Label runat="server" 
                ID="BusinessCategoryLabel" 
                Text='<%# Bind("Business_Category") %>' />
            <br />
            Project Description:
            <asp:Label runat="server" 
                ID="ProjectDescriptionLabel" 
                Text='<%# Bind("Project_Description") %>' />
            <br />
            Operations Owner:
            <asp:Label runat="server" 
                ID="OwnerLabel" 
                Text='<%# Bind("Operations_Owner") %>' />
            <br />
            <asp:LinkButton runat="server" 
                ID="EditButton" 
                CausesValidation="False" 
                CommandName="Edit" 
                Text="Edit" />
            &nbsp;<asp:LinkButton runat="server" 
                ID="DeleteButton" 
                CausesValidation="False" 
                CommandName="Delete" Text="Delete" />
            &nbsp;<asp:LinkButton runat="server" 
                ID="NewButton" 
                CausesValidation="False" 
                CommandName="New" 
                Text="New" />

        </ItemTemplate>
    </asp:FormView>  

Thanks!

Answer 1

Your DeleteCommand and UpdateCommand aren't using the same key as what is declared in the DataKeyNames property of the 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 the automated UPDATEs and DELETEs done using the FormView, so this should be the primary key in your table, and it should match in all three places (UpdateCommand, DeleteCommand, and DataKeyNames). From MSDN:

Use the DataKeyNames property to specify a comma-separated list of field names that represent the primary key of the data source.

(In the code sample on that page, you can see that the parameter used in the WHERE clause of the UpdateCommand is the same as the value specified in the DataKeyNames of the FormView)

As a note, in the code you've shown, your UpdateCommand doesn't use a where clause at all. That means 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 really need the UpdateParameters set up the way you have them (since you're using two-way databinding on the controls in your templates for Edit / Insert). 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 only use the UpdateParameters, DeleteParameters, etc. when I am not using a FormView, or I need to filter them based on a control outside of the FormView. Let me know if this doesn't do it for you.