sql - GridView - 过程或函数指定的参数太多

Question

我有这个存储过程:

USE [Events2]
GO
/****** Object:  StoredProcedure [dbo].[spUpdateRegistrantPaper]    Script Date: 11/20/2015 7:02:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpdateRegistrantPaper]
    -- Add the parameters for the stored procedure here
        @RegistrantId int,
        @LocalNumber int,
        @LocalUnionEmail nvarchar(50),
        @DistrictId int,
        @CompletedByFirstName nvarchar(20),
        @CompletedByLastName nvarchar(25),
        @CompletedByPhone nvarchar(15),
        @FirstName nvarchar(20),
        @LastName nvarchar(25),
        @Position nvarchar(15),
        @Email nvarchar(50),
        @ShirtSize nvarchar(50),
        @Comments nvarchar(300)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE Registrant SET
        LocalNumberId = @LocalNumber,
        LocalUnionEmail = @LocalUnionEmail,
        DistrictId = @DistrictId,
        CompletedByFirstName = @CompletedByFirstName,
        CompletedByLastName = @CompletedByLastName,
        CompletedByPhone = @CompletedByPhone,
        FirstName = @FirstName,
        LastName = @LastName,
        Position = @Position,
        Email = @Email,
        ShirtSize = @ShirtSize,
        Comments = @Comments
        WHERE RegistrantId = @RegistrantId
END

我有一个带有编辑和更新按钮的 GridView。

  <UpdateParameters>
                <asp:Parameter Name="RegistrantId" Type="Int32" />
                <asp:Parameter Name="LocalNumber" Type="Int32" />
                <asp:Parameter Name="LocalUnionEmail" Type="String" />
                <asp:Parameter Name="DistrictId" Type="Int32" />
                <asp:Parameter Name="CompletedByFirstName" Type="String" />
                <asp:Parameter Name="CompletedByLastName" Type="String" />
                <asp:Parameter Name="CompletedByPhone" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Position" Type="String" />
                <asp:Parameter Name="Email" Type="String" />
                <asp:Parameter Name="ShirtSize" Type="String" />
                <asp:Parameter Name="Comments" Type="String" />
            </UpdateParameters>

但是我收到了这个错误:

Procedure or function spUpdateRegistrantPaper has too many arguments specified.

我不明白为什么...？？？我该怎么做才能解决这个问题？

编辑

这是完整的 asp.net 代码:

<%@ Page Title="" Language="C#" MasterPageFile="~/admin/admin.master" AutoEventWireup="true" CodeFile="viewregistrantpaper.aspx.cs" Inherits="viewreg" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h1>View Registrant</h1>
        <asp:GridView ID="GridView1"  runat="server" AllowPaging="True" 
            AllowSorting="True" AutoGenerateColumns="False" 
            DataSourceID="SqlDataSourceViewRegistrant"  
            CssClass="mGrid"  
    PagerStyle-CssClass="pgr"  
    AlternatingRowStyle-CssClass="alt" >
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                <asp:BoundField DataField="LocalNumberId" HeaderText="Local" 
                    SortExpression="LocalNumberId" />
                <asp:BoundField DataField="LocalUnionEmail" HeaderText="Local Email" 
                    SortExpression="LocalUnionEmail" />
                <asp:BoundField DataField="DistrictId" HeaderText="District" 
                    SortExpression="DistrictId" />
                <asp:BoundField DataField="CompletedByFirstName" HeaderText="Completed By First Name" 
                    SortExpression="CompletedByFirstName" />
                <asp:BoundField DataField="CompletedByLastName" HeaderText="Completed By Last Name" 
                    SortExpression="CompletedByLastName" />
                <asp:BoundField DataField="CompletedByPhone" HeaderText="Completed By Phone" 
                    SortExpression="CompletedByPhone" />
                <asp:BoundField DataField="FirstName" HeaderText="First Name" 
                    SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="Last Name" 
                    SortExpression="LastName" />
                <asp:BoundField DataField="Position" HeaderText="Position" 
                    SortExpression="Position" />
                <asp:BoundField DataField="Email" HeaderText="Email" 
                    SortExpression="Email" />
                <asp:BoundField DataField="ShirtSize" HeaderText="Shirt Size" 
                    SortExpression="ShirtSize" />
                <asp:BoundField DataField="Comments" HeaderText="Comments" 
                    SortExpression="Comments" />
            </Columns>

        </asp:GridView>
    <div style=" width:100%; overflow: hidden;">
        <asp:SqlDataSource ID="SqlDataSourceViewRegistrant" runat="server" 
            ConnectionString="<%$ ConnectionStrings:Events2 %>" 
            DeleteCommand="spDelRegistrant" DeleteCommandType="StoredProcedure" 
            InsertCommand="spInsRegistrantPaper" InsertCommandType="StoredProcedure" 
            SelectCommand="spGetRegistrantPaper" SelectCommandType="StoredProcedure" 
            UpdateCommand="spUpdateRegistrantPaper" 
            UpdateCommandType="StoredProcedure">
            <DeleteParameters>
                <asp:Parameter Name="RegistrantId" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="LocalNumber" Type="Int32" />
                <asp:Parameter Name="LocalUnionEmail" Type="String" />
                <asp:Parameter Name="DistrictId" Type="Int32" />
                <asp:Parameter Name="CompletedByFirstName" Type="String" />
                <asp:Parameter Name="CompletedByLastName" Type="String" />
                <asp:Parameter Name="CompletedByPhone" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Position" Type="String" />
                <asp:Parameter Name="Email" Type="String" />
                <asp:Parameter Name="ShirtSize" Type="String" />
                <asp:Parameter Name="Comments" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="RegistrantId" Type="Int32" />
                <asp:Parameter Name="LocalNumber" Type="Int32" />
                <asp:Parameter Name="LocalUnionEmail" Type="String" />
                <asp:Parameter Name="DistrictId" Type="Int32" />
                <asp:Parameter Name="CompletedByFirstName" Type="String" />
                <asp:Parameter Name="CompletedByLastName" Type="String" />
                <asp:Parameter Name="CompletedByPhone" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Position" Type="String" />
                <asp:Parameter Name="Email" Type="String" />
                <asp:Parameter Name="ShirtSize" Type="String" />
                <asp:Parameter Name="Comments" Type="String" />
            </UpdateParameters>
        </asp:SqlDataSource>
        </div>
    </p>

</asp:Content>

Answer 1

请注意，您在 GridView 中使用了很多 BoundField。关于这些字段需要了解的是它们的值会自动添加到更新/插入命令中。例如:

<asp:BoundField DataField="LocalNumberId" HeaderText="Local" 
    SortExpression="LocalNumberId" />

这将导致参数 LocalNumberId 被添加到存储过程的调用中。由于您已经将 LocalNumber 作为参数，因此 LocalNumberId 结果出乎意料。因此，您拥有在 UpdateParameters 标记中显式声明的所有参数以及 BoundField 中的其他参数。

我在这里看到的您的选项:

1. 确保 BoundFields 准确表示存储过程中所需的参数，根据需要重命名列或存储过程参数。

2. 摆脱绑定(bind)字段，改用 TemplateFields 和 Eval。简单示例:

   代替

   <asp:BoundField DataField="LocalNumberId" HeaderText="Local" 
               SortExpression="LocalNumberId" />
   

   你会

   <asp:TemplateField HeaderText="Local" SortExpression="LocalNumberId">
       <ItemTemplate>
           <%# Eval("LocalNumberId") %>
       </ItemTemplate>
   </asp:TemplateField>
   

3. 手动编写对存储过程的调用作为 UpdateCommand，仅使用您需要的参数