SqlParameter not contained by this SqlParameterCollection

Question

I have a class that wraps some Sql functionality up and am having an issue with a return parameter that I'm trying to use. I've read a lot of the posts on this site with similar topics, but am not finding a solution that works for me. I've tried to use the method outlined in this related topic, but I still seem to have an issue.

Here is a snippet of the stored procedure (I've removed most of it for brevity):

INSERT INTO A_Table (col1, col2, col3)
VALUES (val1, val2, val3)
DECLARE @id int
SET @id = SCOPE_IDENTITY()
RETURN @id

This is a snippet of the C#:

SqlParameter rtnParam = new SqlParameter("@id", SqlDbType.Int);
rtnParam.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(rtnParam);
IAsyncResult result = command.BeginExecuteNonQuery();
command.EndExecuteNonQuery(result);

The command.BeginExecuteNonQuery() throws the following exception:

IndexOutOfRangeException: An SqlParameter with ParameterName 'id' is not contained by this SqlParameterCollection.

Is there something wrong with the way that I am adding the return parameter? Let me know if you need to see more code or any clarification. Thanks!

since you are trying to do many things at once, you will need semi-colon; after each command... the insert into / values ;, declare; set; return; — DRapp, Dec 30 '13 at 20:36

score 1 · Accepted Answer · answered Dec 30 '13 at 20:36

1

@id must be declared as one of the stored procedures parameters in your SQL. So something like this:

CREATE PROCEDURE sproc_name
.
.
   @id INT
.
.
AS
BEGIN
   INSERT INTO A_Table (col1, col2, col3)
   VALUES (val1, val2, val3)
   SET @id = SCOPE_IDENTITY()
   RETURN @id
END

answered Dec 30 '13 at 20:36

System Down

6,192
1
30
34

I see. Thanks for the quick feedback and clarification. The stored procedure is actually developed by the company development team and I will not be able to update it. It does indeed lack the return parameter declared at the beginning of the procedure. When executed from within ssms, it works fine. Is this a lost cause executing from C#? – jamesd7198 Dec 30 '13 at 20:55
@jamesd7198 I'm not 100% sure, but ADO.NET will probably recognize it as a scalar query. You should be able to run ExecuteScalar() the way it's done in Steve's answer below. – System Down Dec 30 '13 at 22:57
Yes, the whole point is that ExecuteScalar returns just one row and the first column of that row. And this is the case for it – Steve Dec 31 '13 at 18:08
Appreciate the help guys. I've found my problem, and it stemmed from a typo. In my stored procedure function, I tried to access the return parameter without the "@" in it. Added that to resolve the issue. This was in the code just after the command.EndExecuteNonQuery() which wasn't displayed. – jamesd7198 Dec 31 '13 at 22:16

score 0 · Answer 2 · answered Dec 30 '13 at 20:37

0

It looks to me like you are not setting your parameter up correctly in the stored procedure. You will need to add it before the proc begins

Here is a very sample proc that will work for your .net code

CREATE PROCEDURE testproc
    -- Add the parameters for the stored procedure here
    @id int
    AS
    BEGIN
        DECLARE @id1 int;
        SET @id1 = SCOPE_IDENTITY()
        RETURN @id1
    END
    GO

answered Dec 30 '13 at 20:37

HelloW

1,587
2
13
24

Thank you for your feedback! Indeed it is not declared in the stored procedure as mentioned above. – jamesd7198 Dec 30 '13 at 20:56

score 0 · Answer 3 · answered Dec 30 '13 at 20:37

0

Why do not use simply

INSERT INTO A_Table (col1, col2, col3)
VALUES (val1, val2, val3)
SELECT SCOPE_IDENTITY()

And on the C# side

object result = command.ExecuteScalar();
if(result != null)
{
   int lastID = Convert.ToInt32(result);
}

answered Dec 30 '13 at 20:37

Steve

213,761
22
232
286

SqlParameter not contained by this SqlParameterCollection

3 Answers3