使用sqldatasource中的存储过程更新记录

Question

我有一个数据源，其中有一个调用更新的 sp 并已绑定到 gridview。我想在更新时应该使用 sp 和更新表。 下面是我的设计代码。

 < asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
        DataKeyNames="Title" DataSourceID="SqlDataSource1" EnableModelValidation="True"
        ForeColor="#333333" GridLines="None" ShowFooter="True" Width="1000" AllowPaging="true"
        PageSize="10" OnRowCommand="GridView1_RowCommand">  
        < AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
        < Columns>  
            < asp:TemplateField HeaerText="Title">  
                < ItemTemplate>  
                    < asp:Label ID="lblTitle" runat="server" Text='<%# Eval("Title") %>'></asp:Label>  
                < /ItemTemplate>  
            < /asp:TemplateField>  
            < asp:TemplateField HeaderText="Quote">  
                < ItemTemplate>  
                    <%# Eval("Quote") %>  
                < /ItemTemplate>  
                < EditItemTemplate>  
                    < asp:TextBox ID="txtEditQuote" runat="server" Text='<%# Bind("Quote") %>'></asp:TextBox>  
                < /EditItemTemplate>  
            < /asp:TemplateField>  
            < asp:TemplateField HeaderText="Quote Link">   
                < ItemTemplate>  
                    < %# Eval("QuoteLink") %>  
                < /ItemTemplate>  
                < EditItemTemplate>  
                     < asp:TextBox ID="txtEditQuoteLink" runat="server" Text='<%# Bind("QuoteLink") %>'>< /asp:TextBox>  
                < /EditItemTemplate>   
             < /asp:TemplateField>  
            < asp:TemplateField HeaderText="Published">  
                < ItemTemplate>  
                    < asp:CheckBox ID="chkBox" runat="server" Checked='<%# Convert.ToBoolean(Eval("Published")) %>' 
                        Enabled="false" />  
                < /ItemTemplate>  
                < EditItemTemplate>  
                    < asp:CheckBox ID="chkEditBox" runat="server" Checked='<%# Bind("Published") %>'  Enabled="true" />  
                < /EditItemTemplate>  
            < /asp:TemplateField>  
            < asp:TemplateField HeaderText="PublishedDate">  
                < ItemTemplate>  
                    < %# Convert.ToDateTime(DataBinder.Eval(Container.DataItem,"PublishedDate")).ToString("MM.dd.yyyy") %>  
                < /ItemTemplate>  
                < EditItemTemplate>  
                    < asp:TextBox ID="txtEditPublishedDate" runat="server" Text='<%# Bind("PublishedDate") %>'>< /asp:TextBox>  
                < /EditItemTemplate>  
            < /asp:TemplateField>  
            < asp:TemplateField HeaderText="Commands">  
                < ItemTemplate>  
                    < asp:Button runat="server" ID="Edit" Text="Edit" CommandName="Edit" />  
                    < asp:Button runat="server" ID="Delete" Text="Delete" CommandName="Delete" />  
                < /ItemTemplate>  
                < EditItemTemplate>  
                    < asp:Button runat="server" ID="Update" Text="Update" CommandName="Update" CommandArgument='<%# Container.DataItemIndex %>' />  
                    < asp:Button runat="server" ID="Cancel" Text="Cancel" CommandName="Cancel" />  
                < /EditItemTemplate>  
            < /asp:TemplateField>  
        < /Columns>  
        < EditRowStyle BackColor="#999999" />  
        < FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
        < HeaderStyle BackColor="#5D7B9D" Font-Bold="True" HorizontalAlign="Left" ForeColor="White" />  
        < PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />  
        < RowStyle BackColor="#F7F6F3" ForeColor="#333333" />  
        < SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />  
    < /asp:GridView>  
    < asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>" 
        SelectCommand= "SELECT [TITLE], [QUOTE], [QUOTELINK], [PUBLISHED], [PUBLISHEDDATE] FROM [DBO].[QUOTES]"
        UpdateCommand="sp_ModifyQuotes" UpdateCommandType="StoredProcedure">  
        < UpdateParameters>  
            < asp:Parameter Name="Title" Type="String" />  
            < asp:Parameter Name="Quote" Type="String" />  
            < asp:Parameter Name="QuoteLink" Type="String" />  
            < asp:Parameter Name="Published" Type="Boolean" />   
            < asp:Parameter Name="PublishDate" Type="DateTime" />  
            < asp:Parameter Name="Modify" Type="String" DefaultValue="Update" />  
        < /UpdateParameters>  
    < /asp:SqlDataSource>  

下面是我的 SP，

ALTER PROCEDURE [dbo].[sp_ModifyQuotes]
    @Title varchar(max),
    @Quote varchar(max),
    @QuoteLink varchar(max),
    @Published Bit,
    @PublishDate DateTime,
    @Modify varchar(10)

AS
BEGIN
    Declare @QuoteId int
    IF (@Modify = 'Add')
    BEGIN
        INSERT INTO dbo.QUOTES
        (
            Title,
            Quote,
            QuoteLink,
            Published,
            PublishedDate
        )
        values
        (
            @Title,
            @Quote,
            @QuoteLink,
            @Published,
            @PublishDate
        )
        SET @QuoteId = @@IDENTITY
        If @Published = 1
        BEGIN
            UPDATE dbo.Quotes SET Published = 0 WHERE Id <> @QuoteId AND Published = 1
        END
        --SELECT @QuoteId As ID
    END
    ELSE IF (@Modify = 'Update')
    BEGIN
        UPDATE dbo.Quotes SET
        Quote = @Quote,
        QuoteLink = @QuoteLink,
        Published = @Published,
        PublishedDate = @PublishDate

        WHERE Title = @Title
        --SELECT '1' As ID

    END
    ELSE IF (@Modify = 'Delete')
    BEGIN
        DELETE FROM dbo.Quotes
        WHERE Title = @Title

        --SELECT '1' As ID
    END
END

请有人帮助我，当我尝试更新时，我收到一条错误消息，我应该如何更新我的记录：

过程或函数 sp_ModifyQuotes 指定的参数太多。

但我想我已经传递了所有正确的参数。

Answer 1

修改参数位于 SP 的 INSERT/VALUES 部分 - 基本上它们不匹配。