开发者

Updating records using stored procedure in sqldatasource

i have datasource which has an sp called for update and been binded to gridview. i want while updating it should use the sp and update table. Below is my design code.

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

and below is my SP

ALTER PROCEDURE [dbo].[sp_ModifyQuotes]
    @Title varchar(max),
    @Quote varchar(max),
    @QuoteLink varchar(max),
    @Published B开发者_运维知识库it,
    @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

please any one help me how should i update my records when i try to update i gets an error saying:

Procedure or function sp_ModifyQuotes has too many arguments specified.

but i guess i have passed all proper parameters.


The Modify parameter is in the INSERT/VALUES parts of your SP - basically they do not match.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜