Exception "Procedure or function expects parameter, which was not supplied." thrown even after supplying the parameter

Question

I am trying insert some entries in the database using a stored procedure which is called by a C# method as shown below:

public int addProfile(UserProfile NewUserPro)
{
    DataSet ds = ExecuteStoredProcedure("AddNewPro", new SqlParameter[]{
                new SqlParameter("@UserID", NewUserPro.UsrId),
                new SqlParameter("@Type", NewUserPro.Type),
                new SqlParameter("@Phone", NewUserPro.Phone),
                new SqlParameter("@Name", NewUserPro.Name),
                new SqlParameter("@FatherName", NewUserPro.FatherName),
                new SqlParameter("@GroupID", NewUserPro.GroupID),
                new SqlParameter("@IsPublic", 0)});
            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
}

ExecuteStoredProcedure() is a C# method created by me. It executes specified stored procedure and returns the output as a DataSet. This is the definition of that function:

public DataSet ExecuteStoredProcedure(string ProcedureName, SqlParameter[] Parameters)
{
    try
    {
         using (SqlCommand com = new SqlCommand("[dbo]." + ProcedureName))
         {
              com.CommandType = CommandType.StoredProcedure;
              foreach (SqlParameter sp in Parameters)
                   com.Parameters.Add(sp);
              return SelectDataSet(com);
         }
    }
    catch (Exception e)
    {
        throw e;
    }
}

As you can see in addProfile() method I am already providing @IsPublic parameter, but it's still throwing following exception:

Procedure or function 'AddNewPro' expects parameter '@IsPublic', which was not supplied.

@IsPublic parameter is an int parameter that saves value to [IsPublic] field which is an int field in a table with null allowed.

UPDATE 1

I am including the input parameters of the stored procedure. I think that will be enough? If you want I can include complete stored procedure but it's a lengthy code and due to some confidentiality reasons I have to cut some lines.

ALTER PROCEDURE [dbo].[AddNewPro]
    @UserID int,
    @Name NVARCHAR(MAX),
    @Type int,
    @Phone VARCHAR(MAX),
    @GroupID int,
    @FatherName VARCHAR(MAX),
    @IsPublic int
AS

UPDATE 2

Ok I found a little hint to the solution. When I put the breakpoint on the first line of ExecuteStoredProcedure, I found that its getting the value of @IsPublic as null. But I provided the value while adding the parameters. Why this is happening?

Unrelated to your question, I'd suggest you have a bit of a think about your `try catch` in `ExecuteStoredProcedure`. Not only does it server no real purpose (it catches and throws all exceptions) but it doesn't rethrow them properly. You'd be better off removing it. — David Arno, Jun 08 '15 at 15:13
@maku I included the input parameter declaration part. Hope it will be enough? — Aishwarya Shiva, Jun 08 '15 at 15:20
try to use com.Parameters.AddRange(parameters) so no need for you to loop `And another thing, put a breakpoint in your code it must be the best way to find a solution to your problem.` — Anonymous Duck, Jun 08 '15 at 15:27
If you can, trace it via SQL Server Profile to see: what .net is passing to SQL, if the db is correct. Just to check: do you have multiple schema? Can be problem related to wrong chema issue? — user_0, Jun 08 '15 at 15:43
@maku Ok I found a little hint to the solution. When I put the breakpoint on the first line of `ExecuteStoredProcedure`, I found that its getting the value of `@IsPublic` as `null`. But I provided the value while adding the parameters. Why this is happening? — Aishwarya Shiva, Jun 08 '15 at 15:58
Be careful to used zeroes, read this post http://stackoverflow.com/questions/8352260/why-does-the-sqlparameter-name-value-constructor-treat-0-as-null this should be `new SqlParameter("@IsPublic", Convert.ToInt32(0));` — Anonymous Duck, Jun 08 '15 at 16:01
@maku wow that was some new concept I didn't knew about. I always used `AddWithValue()` it didn't required this conversion. May be its doing it inside. Thanks a lot it worked. Add this as your answer, I will mark it. :) — Aishwarya Shiva, Jun 08 '15 at 16:08

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

sqlparameter reads zero as an object.

Be careful to used zeroes, read this post link

Must convert zero to integral type first . Your code should look like this.

new SqlParameter("@IsPublic", Convert.ToInt32(0));

edited May 23 '17 at 12:05

Community

1
1

answered Jun 08 '15 at 16:11

Anonymous Duck

2,942
1
12
35

Golf clap! I guessed it was grabbing the wrong constructor overload, but you got there first. – 15ee8f99-57ff-4f92-890c-b56153 Jun 08 '15 at 16:12
@EdPlunkett what you mean? – Anonymous Duck Jun 08 '15 at 16:13
I was complimenting you on answering correctly before I did. – 15ee8f99-57ff-4f92-890c-b56153 Jun 08 '15 at 16:31
No problem! I guessed that might be the case. – 15ee8f99-57ff-4f92-890c-b56153 Jun 08 '15 at 17:08

Exception "Procedure or function expects parameter, which was not supplied." thrown even after supplying the parameter

1 Answers1