ASP.NET Web应用中Update与Delete操作报错问题求助

问题描述 #

在ASP.NET Web应用中使用GridView展示SQL Server数据，通过SqlDataSource调用存储过程执行更新和删除时，触发以下错误：

Server Error in '/' Application.
Procedure or function UpdateNarudzbaTable/DeleteNarudzba has too many arguments specified.

需要明确报错原因、解决方法，以及分析操作未正常工作的问题。

前端代码（Narudzbe.aspx） #

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Narudzbe.aspx.vb" Inherits="Mišković_OMIS_projekt.Narudzbe" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Narudžbe</title>
    <style>
        <!-- Styles -->
    </style>
</head>
<body>
<div class="search">
        <input type="text" id="txtSearch" placeholder="Pretraži po broju narudžbe"/>
        <input type="button" id="btnSearch" value="Pretraži" onclick="btnSearch_Click"/>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID narudžbe" DataSourceID="SqlDataSource1" Height="282px" Width="1124px" style="margin-top: 20px" AllowPaging="True" AllowSorting="True">
            <Columns>
                <asp:BoundField DataField="ID narudžbe" HeaderText="Broj narudžbe" ReadOnly="True" SortExpression="ID narudžbe" />
                <asp:BoundField DataField="Ime" HeaderText="Kupac" ReadOnly="True" SortExpression="Ime" />
                <asp:BoundField DataField="Datum primitka" DataFormatString="{0:d}" HeaderText="Datum primitka" ReadOnly="True" SortExpression="Datum primitka" />
                <asp:BoundField DataField="Datum isporuke" DataFormatString="{0:d}" HeaderText="Datum isporuke" SortExpression="Datum isporuke" />
                <asp:BoundField DataField="Naziv statusa" HeaderText="Status" SortExpression="Naziv statusa" />
                <asp:CommandField ShowDeleteButton="True" DeleteText="Obriši"  ShowEditButton="True" CancelText="Poništi" EditText="Uredi" UpdateText="Spremi"/>
            </Columns>
        </asp:GridView>
            <asp:HyperLink ID="HyperLink1" runat="server" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="20px" CssClass="glavni izbornik" NavigateUrl="~/LandingPage.aspx">Povratak na glavni izbornik</asp:HyperLink>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDatabaseConnection %>" SelectCommand="PodaciZaTablicuNarudzbe" SelectCommandType="StoredProcedure"
                UpdateCommand="UpdateNarudzbaTable" UpdateCommandType="StoredProcedure"
                DeleteCommand="DeleteNarudzba" DeleteCommandType="StoredProcedure">
                <UpdateParameters>
                    <asp:Parameter Name="BrojNarudzbe" Type="Int32" />
                    <asp:Parameter Name="DatumPrimitka" Type="DateTime" />
                    <asp:Parameter Name="DatumIsporuke" Type="DateTime" />
                    <asp:Parameter Name="Status" Type="String" />
                </UpdateParameters>
                <DeleteParameters>
                    <asp:Parameter Name="BrojNarudzbe" Type="Int32" />
                </DeleteParameters>
            </asp:SqlDataSource>
    </div>
    <!-- Form and other elements -->
</body>
</html>

删除操作存储过程（DeleteNarudzba） #

ALTER PROCEDURE [dbo].[DeleteNarudzba]
    @BrojNarudzbe INT
AS
BEGIN
    -- 检查订单是否存在
    IF EXISTS (SELECT 1 FROM Narudžba WHERE [ID narudžbe] = @BrojNarudzbe)
    BEGIN
        -- 删除订单
        DELETE FROM Narudžba WHERE [ID narudžbe] = @BrojNarudzbe
    END
    ELSE
    BEGIN
        -- 订单不存在时抛出错误
        RAISERROR('Narudžba sa Broj narudžbe = %d ne postoji.', 16, 1, @BrojNarudzbe)
    END
END

更新操作存储过程（UpdateNarudzbaTable） #

ALTER PROCEDURE [dbo].[UpdateNarudzbaTable]
    @BrojNarudzbe INT,
    @DatumPrimitka DATE,
    @DatumIsporuke DATE,
    @Status VARCHAR(50)
AS
BEGIN
    -- 检查状态是否存在于Status表中
    IF EXISTS (SELECT 1 FROM [Status] WHERE [Naziv statusa] = @Status)
    BEGIN
        -- 获取状态ID
        DECLARE @StatusID INT
        SELECT @StatusID = [ID statusa] FROM [Status] WHERE [Naziv statusa] = @Status

        -- 更新Narudžba表
        UPDATE Narudžba
        SET [Datum isporuke] = @DatumIsporuke,
            [ID statusa] = @StatusID
        WHERE [ID narudžbe] = @BrojNarudzbe
    END
    ELSE
    BEGIN
        -- 状态不存在时抛出错误
        THROW 50000, 'Status ne postoji', 1
    END

    -- 检查交货日期是否不早于接收日期
    IF @DatumIsporuke < @DatumPrimitka
    BEGIN
        THROW 50001, 'Datum isporuke mora biti veći ili jednak datumu primitka', 1
    END
END

后台代码补充（已尝试设置参数默认值） #

Protected Sub BindGridView()
        ' 将GridView绑定到SqlDataSource
        GridView1.DataSource = SqlDataSource1
        GridView1.DataBind()
    End Sub

    Protected Sub GridView1_RowEditing(sender As Object, e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        BindGridView() ' 重新绑定以显示编辑行
    End Sub

    Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs)
        ' 获取GridView编辑行的更新值
        Dim updatedValues As Dictionary(Of String, Object) = New Dictionary(Of String, Object)()

        For i As Integer = 0 To Math.Min(e.NewValues.Count - 1, e.Keys.Count - 1)
            Dim field As String = e.Keys(i).ToString()
            Dim value As Object = e.NewValues(i)
            updatedValues.Add(field, value)
        Next

        ' 设置参数默认值
        SqlDataSource1.UpdateParameters("@BrojNarudzbe").DefaultValue = updatedValues("BrojNarudzbe").ToString()
        SqlDataSource1.UpdateParameters("@DatumPrimitka").DefaultValue = DateTime.Parse(updatedValues("DatumPrimitka").ToString())
        SqlDataSource1.UpdateParameters("@DatumIsporuke").DefaultValue = DateTime.Parse(updatedValues("DatumIsporuke").ToString())
        SqlDataSource1.UpdateParameters("@Status").DefaultValue = updatedValues("Status").ToString()

        ' 执行更新操作
        GridView1.UpdateRow(e.RowIndex, False)
        GridView1.EditIndex = -1
    End Sub


    Protected Sub GridView1_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs)
        GridView1.EditIndex = -1
        BindGridView() ' 重新绑定以退出编辑模式
    End Sub

    Protected Sub GridView1_RowDeleting(sender As Object, e As GridViewDeleteEventArgs)
        ' 获取GridView删除行的值
        Dim deletedValues As Dictionary(Of String, Object) = New Dictionary(Of String, Object)()
        For i As Integer = 0 To e.Keys.Count - 1
            Dim field As String = e.Keys(i).ToString()
            Dim value As Object = e.Keys(i)
            deletedValues.Add(field, value)
        Next

        ' 设置参数默认值
        SqlDataSource1.DeleteParameters("BrojNarudzbe").DefaultValue = deletedValues("BrojNarudzbe").ToString()
        SqlDataSource1.DeleteParameters("DatumPrimitka").DefaultValue = deletedValues("DatumPrimitka").ToString()
        SqlDataSource1.DeleteParameters("DatumIsporuke").DefaultValue = deletedValues("DatumIsporuke").ToString()
        SqlDataSource1.DeleteParameters("Status").DefaultValue = deletedValues("Status").ToString()

        ' 执行删除操作
        GridView1.DeleteRow(e.RowIndex)
        BindGridView() ' 重新绑定以显示删除后的数据
    End Sub

报错原因与解决方法 #

1. 核心报错原因 #

• 删除操作：后台GridView1_RowDeleting中给SqlDataSource1.DeleteParameters添加了多余参数（DatumPrimitka、DatumIsporuke、Status），但存储过程DeleteNarudzba只需要@BrojNarudzbe一个参数，导致传递参数数量超出存储过程要求。
• 更新操作：后台GridView1_RowUpdating存在两个问题：参数名错误（多了@符号）、值获取逻辑错误（把e.Keys和e.NewValues按索引对应，导致字段名与值不匹配）。

2. 修复步骤 #

修复删除操作

修改GridView1_RowDeleting方法，仅保留必要参数：

Protected Sub GridView1_RowDeleting(sender As Object, e As GridViewDeleteEventArgs)
    ' 从DataKeys直接获取订单ID（GridView已指定DataKeyNames为ID narudžbe）
    Dim brojNarudzbe As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Value)
    
    ' 仅设置存储过程需要的参数
    SqlDataSource1.DeleteParameters("BrojNarudzbe").DefaultValue = brojNarudzbe.ToString()

    GridView1.DeleteRow(e.RowIndex)
    BindGridView()
End Sub

修复更新操作

修改GridView1_RowUpdating方法，正确获取参数值并修正参数名：

Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs)
    ' 获取订单ID（从DataKeys）
    Dim brojNarudzbe As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Value)
    ' 获取编辑后的可编辑字段值
    Dim datumIsporuke As DateTime = DateTime.Parse(e.NewValues("Datum isporuke").ToString())
    Dim status As String = e.NewValues("Naziv statusa").ToString()
    ' 接收日期是只读字段，从原始值获取
    Dim datumPrimitka As DateTime = DateTime.Parse(e.OldValues("Datum primitka").ToString())

    ' 设置参数（注意参数名不要带@）
    SqlDataSource1.UpdateParameters("BrojNarudzbe").DefaultValue = brojNarudzbe.ToString()
    SqlDataSource1.UpdateParameters("DatumPrimitka").DefaultValue = datumPrimitka.ToString()
    SqlDataSource1.UpdateParameters("DatumIsporuke").DefaultValue = datumIsporuke.ToString()
    SqlDataSource1.UpdateParameters("Status").DefaultValue = status

    GridView1.UpdateRow(e.RowIndex, False)
    GridView1.EditIndex = -1
    BindGridView()
End Sub

额外优化建议

• 移除手动的BindGridView方法：GridView已通过DataSourceID绑定到SqlDataSource，手动调用DataBind()会干扰控件的自动数据生命周期管理。
• 确保SqlDataSource参数名与存储过程参数名完全一致（SQL Server默认不区分大小写，但保持一致可避免潜在问题）。

内容的提问来源于stack exchange，提问作者meraki_1