Answered by:
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 procedureALTER 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
ENDThanks
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();
ThanksFriday, 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