C＃存储过程或函数需要未提供的参数

I am fairly new to C# and I'm trying to set up call to a stored procedure in my database which takes one parameter.

我是C#的新手,我正在尝试调用我的数据库中的存储过程,该存储过程需要一个参数。

I get the error "Procedure or function 'SP_getName' expects parameter '@username', which was not supplied. "

我得到错误“过程或函数'SP_getName'期望参数'@username',这是未提供的。”

My Stored procedure works ok when I supply it with the parameter and I run it via SQL management studio.

当我提供参数时,我的存储过程正常工作,我通过SQL管理工作室运行它。

GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[SP_getName]
    @username = 'bob101'

SELECT  'Return Value' = @return_value

GO

However when I try and call it the error is with how I'm passing the parameter in, but I can't spot what the issue is.

但是,当我尝试调用它时,错误是我如何传递参数,但我无法发现问题所在。

           //create a sql command object to hold the results of the query
            SqlCommand cmd = new SqlCommand();

            //and a reader to process the results
            SqlDataReader reader;

            //Instantiate return string
            string returnValue = null;

            //execute the stored procedure to return the results
            cmd.CommandText = "SP_getName";

            //set up the parameters for the stored procedure
            cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = "bob101";

            cmd.CommandType = CommandType.Text;
            cmd.Connection = this.Connection;

            // then call the reader to process the results
            reader = cmd.ExecuteReader();

Any help in spotting my error would be greatly appreciated!

任何帮助发现我的错误将不胜感激!

I've also tried looking at these two posts, but I haven't had any luck:

我也试过看这两个帖子,但我没有运气:

Stored procedure or function expects parameter which is not supplied

存储过程或函数需要未提供的参数

Procedure or function expects parameter, which was not supplied

过程或函数需要参数,该参数未提供

Thanks!

3 个解决方案

#1

You have stated:

你说过了:

cmd.CommandType = CommandType.Text;

Therefore you are simply executing:

因此,您只是执行:

SP_getName

Which works because it is the first statement in the batch, so you can call the procedure without EXECUTE, but you aren't actually including the parameter. Change it to

这是有效的,因为它是批处理中的第一个语句,因此您可以在没有EXECUTE的情况下调用该过程,但实际上并未包含该参数。将其更改为

cmd.CommandType = CommandType.StoredProcedure;

Or you can change your CommandText to:

或者您可以将CommandText更改为:

EXECUTE SP_getName @username;

As a side note you should Avoid using the prefix 'sp_' for your stored procedures

作为旁注,您应该避免为存储过程使用前缀“sp_”

And a further side note would be to use using with IDisposable objects to ensure they are disposed of correctly:

另外一个注意事项是使用IDisposable对象来确保它们被正确处理:

using (var connection = new SqlConnection("ConnectionString"))
using (var cmd = new new SqlCommand("SP_getName", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = "bob101";
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Do something 
        }
    }
}

#2

I had this problem, but it wasn't about parameter name of Command Type. My problem was that when C# calls SP, for each parameter that has no value passes 'default' keyword (i found it in SQL Profiler):

我有这个问题,但它不是关于命令类型的参数名称。我的问题是当C#调用SP时,对于没有值的每个参数都传递'default'关键字(我在SQL Profiler中找到它):

... @IsStop=0,@StopEndDate=default,@Satellite=0, ...

in my case my parameter Type was DateTime :

在我的情况下,我的参数Type是DateTime:

@StopEndDate datetime

. I Solved my problem by seting default value to this parameter in Stored Procedure :

。我通过在存储过程中为此参数设置默认值来解决我的问题:

@StopEndDate datetime=null

#3

Try remove @:

尝试删除@:

cmd.Parameters.Add("username", SqlDbType.NVarChar).Value = "bob101";

#1