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 lt int gt  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

User · Answer

In my case  It was returning one output parameter and was not Returning any value  So changed it to   param Direction   ParameterDirection Output  command ExecuteScalar      and then it was throwing size error  so had to set the size as well  SqlParameter param   new SqlParameter   Name  SqlDbType NVarChar   param Size   10

User · Answer

In my case I got the error on output parameter even though I was setting it correctly on C  side I figured out I forgot to give a default value to output parameter on the stored procedure  ALTER PROCEDURE  dbo   test                     UserID int                   ReturnValue int   0 output --Previously I had  ReturnValue int output

User · Answer

Your stored procedure expects 5 parameters as input   userID int    userName varchar 50     password nvarchar 50     emailAddress nvarchar 50     preferenceName varchar 20     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  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

User · Answer

in my case  I was passing all the parameters but one of the parameter my code was passing a null value for string  Eg  cmd Parameters AddWithValue  quot  userName quot   userName   in the above case  if the data type of userName is string  I was passing userName as null

User · Answer

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

User · Answer

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

[sql] Stored procedure or function expects parameter which is not supplied

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures