Item 333 of 340 Previous | Next

4
Vote

The number of parameters does not match number of values for stored procedure.

description

When calling Database.ExecuteNonQuery(stringStoredProcedureName, params object[] parameterValues) (and perhaps other methods as well; I haven't tested to see) Enterprise Library incorrectly returns an InvalidOperationException with the messasge "The number of parameters does not match number of values for stored procedure." when the number of parameters are correct.

In one case, against SQL Server 2005, the problem was that the sql user account was not dbo but the stored procedure being called was in the dbo schema. The sql user account did have permission to execute the stored procedure. The storedProcedureName provided to the method did not include the schema name; it was in the form of "MyProcedure". Changing the storedProcedureName to "dbo.MyProcedure" worked around the issue but there was absolutely no relationship in this issue to the number of parameters. In fact, SQL Server does not object to leaving the schema out. This requirement appears to be another programming flaw in Enterprise Library.

The appropriate behavior, as is the behavior in other areas within the DAAB, is to simply pass on any SqlException that occurs. Substituting an InvalidOperationException is inappropriate to the condition I experienced and, in fact, is inappropriate for an invalid number of parameters. SQL Server and ADO.Net raise a perfectly appropriate exception when there are missing parameters in the form of "Procedure or Function 'MyProcedure' expects parameter '@ParameterName', which was not supplied."

When calling Database.ExecuteNonQuery(DbCommand command) with parameters created using AddInParameter(), and the number of parameters is incorrect, the library passes the SqlException from SQL Server as it should. This is the behavior that should occur when calling all forms of ExecuteNonQuery.

Non-matching parameter lists - whether by type or by number - are design-time issues that, once an application is in production, should rarely if ever occur. It is not necessary, is of little value, and in this case is even harmful, for Enterprise Library to try to handle the exception in code.

Thanks,


Dale

No files are attached

comments

aliixx wrote Jun 9 2009 at 1:28 PM

very late reply but if someone else comes across this, EL caches sproc definition so need to restart the app.

mitchellSTM wrote Aug 2 2007 at 7:48 PM

Found my problem was not the library - changed the code to pass only the Command wrapper and insured that the GetStoredProcCommandWrapper is used to create the cmd wrapper.
Used the AddInParameter to build the contents and this now works. Hope it helps others.

mitchellSTM wrote Aug 2 2007 at 3:55 PM

That would be in
Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlCommandWrapper
SameNumberOfParametersAndValues

mitchellSTM wrote Aug 2 2007 at 2:27 PM

It appears that the 1.1 library line 320 has
this.parameterValues.Length;
Which is not correct --- it should be
this.command._parameters.Count

mitchellSTM wrote Aug 2 2007 at 2:16 PM

I was hitting this same error - and had stepped through the code to see what was happening -- Number of parameters is being checked - but not correctly -- and then throws an exception based on parameter count. The above description fits -- I found this in the 1.1 libary. Will have to revisit this when upgrading to the 3.1 libraray. I tried putting the DBO in there it did not fix it. I do agree that the exception should be thrown in the SQL and ADO Layer, not in the validators. I used the AddInParameters and referenced the CMD object as the argument in the call -- it is seeing this as one argument - not the eleven that should be there. - I will research more but this does seem to be a critical bug.

dalepres wrote May 23 2007 at 4:12 PM

This continues to be a problem with EnterpriseLibrary. I keep running across new situations where EL reports the wrong number of parameters even though the number is correct. Any help or feedback would be greatly appreciated.

bmains wrote Mar 25 2007 at 6:33 PM

I am also getting this with ExecuteDataSet method as well; any time I try to select, and I do reference the dbo. for my stored procedure, I get "The number of parameters does not match number of values for stored procedure" error.

Sign in to add a comment or to set email notifications