包含多个insert SQL和ASP.Net的存储过程

I'm new to writing stored procedures. I want to write a stored procedure in SQL Server for multiple inserts from a table in ASP.net.

我对编写存储过程很陌生。我想在SQL Server中编写一个存储过程，用于在ASP.net中从表中进行多次插入。

This is what I have so far:

这是我目前所拥有的:

Stored procedure:

存储过程:

ALTER procedure [dbo].[spUser]
    @userName VARCHAR(50),
    @membershipnr INT,
    @email VARCHAR(50)
as
begin
    DECLARE @returnvalue INT

    IF (SELECT COUNT(*) FROM User WHERE Membershipnr = @membershipnr) > 0
    BEGIN
        SET @returnvalue = -1
    END
    ELSE
    BEGIN
        SET @returnvalue = 1

        INSERT INTO User(Name, Membershipnr, Email) 
        VALUES(@userName, @membershipnr, @email)
    END

    SELECT @returnvalue
end

C# code:

c#代码:

SqlCommand cmd = new SqlCommand("spUser", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlCommand com = new SqlCommand("spTeam", conn);
com.CommandType = CommandType.StoredProcedure;

if (ddl1.SelectedValue == "2" && rb.SelectedValue == "M" && ddl2.SelectedValue == "1")
{
    cmd.Parameters.AddWithValue("@userName", txtName.Text);
    cmd.Parameters.AddWithValue("@userName", txtName2.Text);
    cmd.Parameters.AddWithValue("@membershipnr", txtMembershipnr.Text);
    cmd.Parameters.AddWithValue("@membershipnr", txtMembershipnr2.Text);
    cmd.Parameters.AddWithValue("@email", txtEmail.Text);
    cmd.Parameters.AddWithValue("@email", txtEmail2.Text);
    com.Parameters.AddWithValue("@clubID", "1");
    com.Parameters.AddWithValue("@class", "2xM");
    com.Parameters.AddWithValue("@teamName", txtTeam.Text);

    conn.Open();
    int answer = (int)cmd.ExecuteNonQuery();

}

I get an error when I try to insert 2 columns. The error I get is:

当我插入2列时，会有一个错误。我得到的错误是:

Procedure or function spUser HAS too many arguments specified.

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

I think the problem is my stored procedure. But I don't know how I can specify more inserts in the procedure. I searched the internet but I couldn't find the answer.

我认为问题是我的存储过程。但是我不知道如何在过程中指定更多的插入。我上网搜索了一下，但是没有找到答案。

Does somebody know how I can fix this so the error goes away and my insert will work?

有人知道我如何修正这个错误，让插入成功吗?

Thanks in advance!

提前谢谢!

UPDATE:

更新:

I'm using a 3 table with the texboxes.

我用的是3表格和texbox。

One of the tables:

的一个表:

<asp:Table ID="tblTwo" runat="server" class="table">
    <asp:TableHeaderRow>
        <asp:TableHeaderCell>Name</asp:TableHeaderCell>
        <asp:TableHeaderCell>Email</asp:TableHeaderCell>
        <asp:TableHeaderCell>Membershipnr</asp:TableHeaderCell>
    </asp:TableHeaderRow>

    <asp:TableRow>
        <asp:TableCell>
            <asp:TextBox ID="txtName" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtEmail" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtMembershipnr" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
    </asp:TableRow>
    <asp:TableRow>
        <asp:TableCell>
            <asp:TextBox ID="txtName2" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtEmail2" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtMembershipnr2" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
    </asp:TableRow>
</asp:Table>

Also I have a second stored procedure for CulbID, Class and Team because this has to be filled into another SQL Server table.

另外，我还有另一个CulbID、Class和Team的存储过程，因为它必须填充到另一个SQL Server表中。

Code of that stored procedure:

存储过程的代码:

ALTER procedure [dbo].[spTeam]
    @teamName VARCHAR (50)
as
BEGIN
    INSERT INTO Ploeg(Name, RegistrationDate)
    VALUES(@teamName, GETDATE())
END

3 个解决方案

#1

Like others have mentioned, you've got too many parameters in you C# code. Your Stored Procedure takes only 3 parameters

正如其他人提到的，您在c#代码中有太多的参数。存储过程只接受3个参数

@userName VARCHAR(50),
@membershipnr INT,
@email VARCHAR(50)

As such, when you are adding the parameters in C#, they need to match (the number of parameters, name and type) i.e.

因此，当您在c#中添加参数时，它们需要匹配(参数的数量、名称和类型)。

cmd.Parameters.AddWithValue("@userName", txtName.Text);
cmd.Parameters.AddWithValue("@membershipnr", txtMembershipnr.Text);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);

Personally, I like to pass the type as well using the following

就我个人而言，我也喜欢使用以下的类型。

cmd.Parameters.Add("userName", SqlDbType.VarChar).Value = txtName.Text;
cmd.Parameters.Add("membershipnr", SqlDbType.Int).Value = txtMembershipnr.Text;
cmd.Parameters.Add("email", SqlDbType.VarChar).Value = txtEmail.Text;

#2

Your stored procedure has less number of input variables then what you are sending from your C# code. You need to define them in your stored procedure.

存储过程中输入变量的数量比从c#代码中发送的要少。您需要在存储过程中定义它们。

Try to add all the input variables which you are sending from your code like this:

尝试添加您从代码中发送的所有输入变量:

ALTER procedure [dbo].[spUser]
@userName VARCHAR(50),
@membershipnr INT,
@email VARCHAR(50)
@clubID int,
@class varchar(5),
@teamname varchar(15),
.............

#3

You're specifying too many parameters in your code (some missing, some duplicated). The way you've written the procedure would require the stored proc to be executed once for each row of data you want to insert.

您在代码中指定了太多的参数(一些缺失，一些重复)。您编写该过程的方式将要求对要插入的每一行数据执行一次存储proc。

Usually this would be achieved with a loop in your C#.

通常在c#中使用循环就可以实现这一点。

#1