C# Stored procedure or function expects parameter which is not supplied

Question

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.

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

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

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!

Answer 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

cmd.CommandType = CommandType.StoredProcedure;

Or you can change your CommandText to:

EXECUTE SP_getName @username;

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

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

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 
        }
    }
}

Answer 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):

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

in my case my parameter Type was DateTime :

@StopEndDate datetime

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

@StopEndDate datetime=null