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
– DRapp, Commented Dec 30, 2013 at 20:36

System Down · Accepted Answer · 2013-12-30 20:36:13Z

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, 2013 at 20:36

System Down

6,2711 gold badge33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jamesd7198 Over a year ago

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#?

System Down Over a year ago

@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.

Steve Over a year ago

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

jamesd7198 Over a year ago

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.

HelloW · Accepted Answer · 2013-12-30 20:37:04Z

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, 2013 at 20:37

HelloW

1,6172 gold badges14 silver badges24 bronze badges

1 Comment

jamesd7198 Over a year ago

Thank you for your feedback! Indeed it is not declared in the stored procedure as mentioned above.

Steve · Accepted Answer · 2013-12-30 20:37:41Z

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, 2013 at 20:37

Steve

217k22 gold badges242 silver badges296 bronze badges

Collectives™ on Stack Overflow

SqlParameter not contained by this SqlParameterCollection

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related