Can figure out error - The number of parameters does not match number of values for stored procedure

_{Monday, December 27, 2010 6:09 PM}

I've altered a stored procedure and added the @price parameter, shown below.  The code that calls this procedure is shown at the bottom.

It works fine from my machine, but now on my test server I keep getting the error shown in the title.

I've restarted the web server and sql server.   The error message is the same even if I delete the stored procedure.  Any ideas?

ALTER PROCEDURE [dbo].[AN_Part_Update]

@partId INT,

@partNumber NVARCHAR(50),

@alternatePartNumber NVARCHAR(50),

@shortDescription NVARCHAR(500) = NULL,

@condition NVARCHAR(50) = NULL,

    @quantity INT,

    @price MONEY,

@enabled BIT = 1

....

Code that calls the procedure:

            Database db = DBController.CreateDatabase();

            DbCommand cmd = db.GetStoredProcCommand("AN_Part_Update", partId, partNumber, alternatePartName, description,

                condition, quantity, price, enabled);

            db.ExecuteNonQuery(cmd);

_{Wednesday, December 29, 2010 4:13 AM ✅Answered}

I let a day go by and then it worked.  Somehow the existing signature of the procedure was cached, and I couldn't figure out how to refresh it.

_{Monday, December 27, 2010 6:40 PM}

Your .NET call is a bit strange. What does the GetStoredProcCommand method do?

See also this very recent blog 

Procedure expects parameter error from SqlCommand Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Monday, December 27, 2010 7:00 PM}

GetStoredProcCommand is from the Microsoft Enterprise Library

_{Monday, December 27, 2010 7:17 PM}

In the Help it is said that content is outdated. Microsoft.Practices.EnterpriseLibrary.Data Namespace

Can you use direct ADO.NET calls instead for now?

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Monday, December 27, 2010 7:30 PM}

No, I don't want to make any change in the application code.  I have a large data layer with this same syntax, and everything is working.  I believe that the information on the stored procedure is cached, but I haven't been able to disrupt that.

If I delete the procedure on my development machine, I get an accurate message ("Could not find stored procedure 'AN_Part_Update'").

But if I delete the procedure on the server, I still get the same message about the parameters.

_{Monday, December 27, 2010 7:39 PM}

It sounds like your code is accessing some other database (not the one you think it does) where this SP is the original one.

Can you run SQL Profiler on the Production Server and run your application to capture the exact command being send from your application? Make sure that it uses the database you think  it should be using.

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Wednesday, December 29, 2010 5:16 AM}

Hi,

From my point of view, if one stored procedure is modified, its execution plan will be recompiled. You can also run this stored procedure with WITH RECOMPILE caluse. If you want to remove a specified plan cache, you can use DBCC FREEPROCCACHE command to remove cached plan(s).

For more information, you can refer to http://msdn.microsoft.com/en-us/library/ms174283.aspx and http://msdn.microsoft.com/en-us/library/ms181055.aspx.

Hope this helps.

Thanks,
Chunsong

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

_{Saturday, February 24, 2018 1:37 PM}

Hello, 
        From couple of weeks, I am facing same problem.   When I alter the SQL Server procedure (increase the parameters) and do respective changes in .cs page and upload the changes on hosting server, the error message I got is as follows:

The number of parameter does not match the number of values for stored procedure

If I restart the website on IIS or I wait for some hours then error goes automatically and it works fine.

looking for solution

_{Saturday, February 24, 2018 2:26 PM}

Try this the next time:

USE YourDBname;  
GO  
EXEC sp_recompile N'schemaName.YourSPname';  

;)

_{Saturday, February 24, 2018 6:21 PM}

Thanks for reply, but this have not helped. continue with same error.