Output parameter error in sql server and C #

Question

Why, if I have this stored procedure created with an output parameter, I get the following error:

sp_DTS_InsertLSRBatch expects an @ErrorMsg parameter that was not supplied

Stored Procedure Code:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_DTS_InsertLSRBatch] @LSRNbr varchar(10), @BatchNbr varchar(10), @ErrorMsg varchar(20) output AS BEGIN SET NOCOUNT ON; if not exists(select * from tblDTS_LSRBatch (nolock) where LSRNbr=@LSRNbr and BatchNbr=@BatchNbr ) begin -- check if BatchNbr exists under another LSR -- if not add (LSR, BatchNbr) else error if not exists(select * from tblDTS_LSRBatch (nolock) where BatchNbr=@BatchNbr ) insert into tblDTS_LSRBatch (LSRNbr,BatchNbr) values (@LSRNbr, @BatchNbr) else set @ErrorMsg = 'Batch dif LSR' end END 

C # code:

 SqlConnection conn = new SqlConnection(ConnStr); try { conn.Open(); for (int i = 0; i <= lbxBatch.Items.Count - 1; i++) { SqlCommand cmd = new SqlCommand("sp_DTS_InsertLSRBatch", conn); cmd.Parameters.Add(new SqlParameter("@LSRNbr", txtLSR.Text)); cmd.Parameters.Add(new SqlParameter("@BatchNbr", lbxBatch.Items[i].ToString())); //Output parameter "ErrorMsg" SqlParameter pErrorMsg = new SqlParameter("@ErrorMsg", SqlDbType.VarChar, 20); pErrorMsg.Direction = ParameterDirection.Output; cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); <--- ERROR 

Answer 1

In your code, you have not added the pErrorMsg parameter. Add this line:

 cmd.Parameters.Add(pErrorMsg); 

Answer 2

In addition, in your stored procedure, you must set the @ErrorMsg sql output variable to the appropriate value, for example, an empty string or double quotation marks ("") in the parts of the if condition of your SP code, as a good coding practice.

Answer 3

You create the pErrorMsg parameter, but where do you add it to your command?