Procedure expects parameter which was not supplied...?
-
I have this SP definition and below that the code to execute it from .Net. But I'm getting this error: Procedure 'GetNewId' expects parameter '@Key', which was not supplied.
Saw an solution on some site which said to set the value of the output param to NULL before calling it, but that didn't do anything.
Anyone have any ideas why this may be happening?? Thanks in advance.
CREATE PROC GetNewId(@TableName varchar(32), @key bigint OUTPUT) AS
Declare @NextKey bigint
BEGIN TRAN
SELECT @NextKey = NewID from ID where TableName = @TableName
IF (SELECT @NextKey)IS NULL
BEGIN
INSERT INTO ID(NewID, TableName) VALUES(0, @TableName)
END
UPDATE ID
SET NewID = NewID + 1 Where TableName = @TableName
SELECT @NextKey = NewID from ID where TableName = @TableName
SET @key=@NextKey
COMMIT TRAN
Return @key
GO
---------------------------------------------------------------------------------------------
_SQLCommand.CommandText = theCommStr;
_SQLCommand.CommandType = CommandType.StoredProcedure;
SqlParameter aInputTblPrm = new SqlParameter("@TableName", "Jack");
aInputTblPrm.Direction = ParameterDirection.Input;
SqlParameter aReturnKeyPrm = new SqlParameter("@Key", SqlDbType.BigInt);
aReturnKeyPrm.Direction = ParameterDirection.InputOutput;
_SQLCommand.Parameters.Add(aInputTblPrm);
_SQLCommand.Parameters.Add(aReturnKeyPrm);
_SQLCommand.ExecuteNonQuery();
-
UNTESTED!!!!!
But at first glance, isn't this the correct syntax?
aReturnKeyPrm.Direction = ParameterDirection.Output;
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
No, that's the right setting. I have tried the others.
-
I've done this plenty of times with .NET, but unfortunately at home so I don't have the code with me right now. Have you tried putting a non-Null value in for the second parameter, e.g. 0.
-
Is there any time where multiple users will be accessing this proc concurrently?
If so, this may cause problems with inaccurate ID's because there is a chance that another user can grab the last ID before the first user can update the table.
Can't you use an Identity field and return the identity field (Set @NewID = @@Identity) from the table you are using to post the results of this proc?
In other words, "Post" a new account or what every you are doing, and return the identity key then finish the process and update with the identity key as the filter. This will normally insure that info does not get overwritten.
-
it should be
SELECT @key=@NextKey
-
Got your problem solved yet? Sorry I'm posting so long after your OP, but I'm behind on my TiVo, too.
A couple of things:1. You're trying to return a bigint with RETURN, but BOL says that you can only use an int.
2. It might be that ADO.NET is getting confused because you are returning a value from the sproc with RETURN as well as an output parm, but your code does not specify a parameter with ParameterDirection.ReturnValue. Also, return value parms need to be specified first in the parameter list.
There is no "i" in team, but idiot has two. -
Yes actually, using SELECT instead of RETURN int he SP and setting the OUT param value to System.DbNull.Value in ADO when calling the SP fixed it.
I wish these error messages would be clearer though, this one says nothing about what's actually going on.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply