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.
精彩评论