存储过程或函数需要未提供的参数 [英] Stored procedure or function expects parameter which is not supplied

问题描述

我试图通过调用存储过程将数据插入到 SQL Server 数据库中，但出现错误

I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error

过程或函数SHOWuser"需要未提供的参数@userID".

Procedure or function 'SHOWuser' expects parameter '@userID', which was not supplied.

我的存储过程叫做SHOWuser.我已经彻底检查过了，没有遗漏任何参数.

My stored procedure is called SHOWuser. I have checked it thoroughly and no parameters is missing.

我的代码是:

public void SHOWuser(string userName, string password, string emailAddress, List<int> preferences)
{
        SqlConnection dbcon = new SqlConnection(conn);

        try
        { 
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = dbcon;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SHOWuser";

            cmd.Parameters.AddWithValue("@userName", userName);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@emailAddress", emailAddress);

            dbcon.Open();

            int i = Convert.ToInt32(cmd.ExecuteScalar());

            cmd.Parameters.Clear();
            cmd.CommandText = "tbl_pref";

            foreach (int preference in preferences)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(i));
                cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference));

                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            dbcon.Close();
        }

存储过程是:

ALTER PROCEDURE [dbo].[SHOWuser]
(
    @userName varchar(50),
    @password nvarchar(50),
    @emailAddress nvarchar(50)
)
AS
BEGIN
    INSERT INTO tbl_user(userName, password, emailAddress) 
    VALUES (@userName, @password, @emailAddress)

    SELECT
        tbl_user.userID, tbl_user.userName,
        tbl_user.password, tbl_user.emailAddress, 
        STUFF((SELECT ',' + preferenceName 
               FROM tbl_pref_master
               INNER JOIN tbl_preferences ON tbl_pref_master.preferenceID = tbl_preferences.preferenceID
               WHERE tbl_preferences.userID = tbl_user.userID
               FOR XML PATH ('')), 1, 1, ' ' ) AS Preferences
    FROM
        tbl_user

    SELECT SCOPE_IDENTITY();
END

这是在同一函数中使用的第二个存储过程 tbl_pref:

This is the second stored procedure tbl_pref which is used in the same function:

ALTER PROCEDURE [dbo].[tbl_pref]
    @userID int,
    @preferenceID int
AS
BEGIN
    INSERT INTO tbl_preferences(userID, preferenceID) 
    VALUES (@userID, @preferenceID)
END

推荐答案

您的存储过程需要 5 个参数作为输入

Your stored procedure expects 5 parameters as input

@userID int, 
@userName varchar(50), 
@password nvarchar(50), 
@emailAddress nvarchar(50), 
@preferenceName varchar(20) 

所以你应该将所有 5 个参数添加到这个 SP 调用中:

So you should add all 5 parameters to this SP call:

    cmd.CommandText = "SHOWuser";
    cmd.Parameters.AddWithValue("@userID",userID);
    cmd.Parameters.AddWithValue("@userName", userName);
    cmd.Parameters.AddWithValue("@password", password);
    cmd.Parameters.AddWithValue("@emailAddress", emailAddress);
    cmd.Parameters.AddWithValue("@preferenceName", preferences);
    dbcon.Open();

PS:不清楚这些参数的用途.您不在 SP 正文中使用这些参数，因此您的 SP 应如下所示:

PS: It's not clear what these parameter are for. You don't use these parameters in your SP body so your SP should looks like:

ALTER PROCEDURE [dbo].[SHOWuser] AS BEGIN ..... END

这篇关于存储过程或函数需要未提供的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！