Error - An SqlParameter with ParameterName '@id' is not contained by this SqlParameterCollection.

Question

User-1499457942 posted

Hi

  I have below code . I am getting above error on below line

string id = cmd.Parameters["@id"].Value.ToString();

using (cmd = new SqlCommand("Sp_ADDRec", con, tran))
                                {
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@CustomerNo", Request.QueryString["cu"].ToString());
                                    cmd.Parameters.AddWithValue("@CustomerName", ltlrname.Text);
                                    
                                    cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Direction = ParameterDirection.Output;

                                    cmd.ExecuteNonQuery();
                                }

                                tran.Commit();
                                string id = cmd.Parameters["@id"].Value.ToString();
                                txtTemp.Text = "ID = " + id;


Below is Stored procedure

ALTER PROCEDURE [dbo].[Sp_Addrec]
-- Add the parameters for the stored procedure here
@CustomerNo nvarchar(15),
@CustomerName nvarchar(50),
@id int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Insert into [tbl_Rec] ([CustomerNo],[CustomerName]) values (@CustomerNo,@CustomerName)
SET @id=SCOPE_IDENTITY()
RETURN @id
END

Thanks

Answer 1

User1992938117 posted

As recommended in previous thread, use command.ExecuteScalar() if you want to return identity column.

Updated SP:

CREATE PROCEDURE [dbo].[Sp_Addrec] 
-- Add the parameters for the stored procedure here
@CustomerNo nvarchar(15),
@CustomerName nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Id as Int;
-- Insert statements for procedure here
Insert into [tbl_Rec] ([CustomerNo],[CustomerName]) values (@CustomerNo,@CustomerName);
SELECT SCOPE_IDENTITY();
END
GO

Code:

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConStr"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[Sp_Addrec]", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CustomerNo", SqlDbType.VarChar).Value = "User Id";
                    cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar).Value = "User Name";
                    con.Open();
                    string id = cmd.ExecuteScalar().ToString();
                }
            }

Answer 2

User-1499457942 posted

Hi Rajneesh

  Why we are declaring string variable . Cant we declare int. 

string id = cmd.ExecuteScalar().ToString();

Thanks

Answer 3

User1992938117 posted
You can cast to int as below
int id = Convert.ToInt32(cmd.ExecuteScalar());