Stored procedure or function expects parameter which is not supplied

Question

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

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

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

My code is:

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();         } 

and the stored procedure is:

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 

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 

Answer 1

In my case I received this exception even when all parameter values were correctly supplied but the type of command was not specified :

cmd.CommandType = System.Data.CommandType.StoredProcedure; 

This is obviously not the case in the question above, but exception description is not very clear in this case, so I decided to specify that.

Answer 2

I came across this issue yesterday, but none of the solutions here worked exactly, however they did point me in the right direction.

Our application is a workflow tool written in C# and, overly simplified, has several stored procedures on the database, as well as a table of metadata about each parameter used by each stored procedure (name, order, data type, size, etc), allowing us to create as many new stored procedures as we need without having to change the C#.

Analysis of the problem showed that our code was setting all the correct parameters on the SqlCommand object, however once it was executed, it threw the same error as the OP got.

Further analysis revealed that some parameters had a value of null. I therefore must draw the conclusion that SqlCommand objects ignore any SqlParameter object in their .Parameters collection with a value of null.

There are two solutions to this problem that I found.

1. In our stored procedures, give a default value to each parameter, so from @Parameter int to @Parameter int = NULL (or some other default value as required).

2. In our code that generates the individual SqlParameter objects, assigning DBNull.Value instead of null where the intended value is a SQL NULL does the trick.

The original coder has moved on and the code was originally written with Solution 1 in mind, and having weighed up the benefits of both, I think I'll stick with Solution 1. It's much easier to specify a default value for a specific stored procedure when writing it, rather than it always being NULL as defined in the code.

Hope that helps someone.