Too many arguments specified error in stored proc

no idea if this will help but be sure to CLEAR the parameter collection at the end of the executing statement otherwise it will keep adding parameters and causing this type of error.

so at the end of the for loop or after you execute the command, clear the SQL Parameters collection

this is what it looks like to me.

use

.Parameters.Clear();

ahmedilyas at 2007-8-30 >

OK, that last bit worked ... once.

This is where things get really odd. I went ahead and changed the ParameterDirection to ReturnVal and the proc ran flawlessly. (or at least for the single iteration of the for loop I let it go through on that run).

The next time I ran the proc, I got:

"Procedure 'tbl_Citations_AddNewRow' expects parameter '@Identity', which was not supplied."

I double-checked everything and nothing in the stored proc had changed. I tried changing the ParameterDirection to InputOutput to see what would happen. It ran once (unfortunately I only let the for loop go one iteration again so I don't know what happens on multiple calls in a singel program instance)

Guess what - the next time I ran the program, I started getting the 'too many arguments error' again.

I've tried running several times but both ReturnVal and InputOutput now fail to work. ReturnVal now always gives the '@Identity' error and InputOutput always give the 'too many params' error. I've checked my tables and stored procs to see if anything has changed but everything is unhanged. I've tested the stored proc in SQL Query Analyzer and it works fine and returns @Identity just fine.

Any suggestions?

DanHeidel at 2007-8-30 >

Hoooookay... I've figured out the problem. Wait, no, that's wording it too strongly. I've gotten rid of the error. I still have no idea of how I did it, though.

So, I'm thrashing around with the stored proc since it looks like the error is originating from there. One interesting thing that I notice is that the stored proc runs just fine from SQL Query Analyzer and returns the autogenerated row number like it's supposed to. However, when run from within Visual Studio, I get the same @Identity error and the stored proc neither adds a table row nor returns a row number.

I borrow a copy of Professional SQL Server 2000 Programming by Robert Vieira. Good 'ol Roberto has the following to say on page 367:

"You must use the OUTPUT keyword when you call the sproc, much as you did when you declared the sproc. This gives SQL Server advance warning about the special handling that parameter will require. Be aware, however, that forgetting to include the OUTPUT keyword won't create a runtime error, but the value for the output parameter won't be moved into your variable (you'll just wind up with what was already there - most likely a NULL value). ..."

Since this sounded a lot like the @Identity error I was getting, I took a closer look at the @Identity definition in the stored proc on the assumption that something is failing to let SQL Server know in advance that the stored proc has an OUTPUT return value. The variable initialization dialog box that pops up when you run the stored proc has a dropdown box for initializing the value of @Identity which seems wierd because it's an OUTPUT variable. The two options are <DEFAULT> (the default setting in the dropdown) and <NULL>.

More as a result of being out of ideas than any rational thought process, I change <DEFAULT> to <NULL> in the dialog box and run the stored proc.

It runs flawlessly.

So now I spend some time thrashing around trying to figure out how to get my C# code to tell the stored proc that @Identity needs to be <NULL> be default. What finally ends up working - I kid you not - is this:

@Identity decimal OUTPUT

gets changed to this:

@Identity decimal = NULL OUTPUT

and everything lives happily ever after.

Except me since I still don't know why I have to explicitly define an OUTPUT variable as being NULL. I'd be a lot happier if my solution didn't involve the programming equivalent to swinging a dead cat around my head and making a ward against the evil eye.

But, in the end, it works so I can't complain too much. Thanks ahmedilyas and DMan1, your help is greatly appreciated even if it didn't directly lead to the (rather bizzare) solution.

Dan

DanHeidel at 2007-8-30 >

Wait, it gets better!

I noticed this morning that the stored proc in my code above runs fine but the return value in my code *always* returns 0. However, my other 3 stored procs work just fine.(I'm working with nested data - the code I posted above is only a small part of it - there are 3 other tables chained in one-to-many relationships to the first table that I am populating at the same time in a similar way. All of the other tables have the same sort of stored proc, etc and the return value comes back exactly how one would expect.)

To try and troubleshoot, I add RETURN @Identity to the end of the problematic stored proc, intending to see if I can grab the return value and have better luck with that. Of course, I forgot to change my C# code to refer to the RETURN value rather than the returned @Identity.

I notice this just as I trace to the line where I get the return value and expect to get a 0 again. But no, now @Identity returns its value just fine. Because there's a RETURN statement after it.

Of course, why didn't I think of that...

Then I finally realize why this one table is acting differently than the other 3 - while messing with the ParameterDirection settings for the @Identity variables, the code that handles the problematic table is set to ReturnValue while the other 3 are set to InputOutput. As soon as I change my code to InputOutput for all the stored proc @Identity variables, it works fine.

So, to sum up:

• Assign a default value of NULL to your return variable or else you get SQL exceptions complaining that your return variable isn't returning a value or that you have the wrong number of variables. (this still is true even with the latest developments - I tested)
• Use ParameterDirection.InputOutput in your code to get the value of that return variable
• If you use ParameterDirection.ReturnValue, you will always get 0 for that value unless you include a superfluous RETURN statement that returns the return variable a second time in the stored proc

Makes perfect sense to me. No if you'll excuse me, I have to go stock up on shrunken heads and voodoo dolls.

:P

DanHeidel at 2007-8-30 >