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

  • 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

    Friday, June 29, 2018 11:47 AM

Answers

  • 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();
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 29, 2018 12:04 PM
  • User1992938117 posted
    You can cast to int as below
    int id = Convert.ToInt32(cmd.ExecuteScalar());
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 29, 2018 5:00 PM

All replies

  • 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();
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 29, 2018 12:04 PM
  • User-1499457942 posted

    Hi Rajneesh

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

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

    Thanks
    Friday, June 29, 2018 4:41 PM
  • User1992938117 posted
    You can cast to int as below
    int id = Convert.ToInt32(cmd.ExecuteScalar());
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 29, 2018 5:00 PM