ASP.NET Formview 未正确更新 SQL 数据库

Question

我有一个连接到 SQL 数据源的 asp.net Formview。当我创建/编辑/删除记录时，列数据将被删除。我确信这是一些简单的错误编码，因为我所知道的有关 SQL/asp.net 的所有信息都在过去几周内通过 google 搜索过。

这是 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 (@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>

这是表单 View 代码

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

谢谢!

Answer 1

您的DeleteCommand 和UpdateCommand 未使用与FormView 的DataKeyNames 属性中声明的键相同的键。它们应该是这样的:

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

DataKeyNames 属性用于控制使用 FormView 完成的自动更新和删除，因此这应该是表中的主键，并且它应该在所有三个位置匹配(UpdateCommand、DeleteCommand)和数据键名称)。来自 MSDN :

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

(在该页面的代码示例中，您可以看到 UpdateCommand 的 WHERE 子句中使用的参数与 FormView 的 DataKeyNames 中指定的值相同)

请注意，在您显示的代码中，您的 UpdateCommand 根本不使用 where 子句。这意味着它将使用同一组值更新表中的每条记录。仅供引用 =)

编辑:当您使用这样的FormView时，您实际上并不需要按照您拥有它们的方式设置UpdateParameters(因为您使用的是两个-在模板中的控件上进行数据绑定(bind)以进行编辑/插入)。为您的 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>

当我不使用 FormView 时，或者我需要根据 FormView 外部的控件来过滤它们时，我通常只使用 UpdateParameters、DeleteParameters 等。如果这不适合您，请告诉我。