Output parameter issue in sql server and c#

Question

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

sp_DTS_InsertLSRBatch expects parameter @ErrorMsg which 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

score 7 · Accepted Answer · answered Mar 08 '12 at 15:03

7

In your code you haven't added the pErrorMsg parameter. Add this line:

cmd.Parameters.Add(pErrorMsg);

answered Mar 08 '12 at 15:03

aaroncatlin

3,203
1
16
27

Yeap, you were right, I thought that it was not necessary since I supposed to get that value from the SP. humm, now I see, Thanks! – Somebody Mar 08 '12 at 15:11

score 1 · Answer 2 · answered Mar 08 '12 at 15:16

1

Moreover, in your Stored Procedure, you must set @ErrorMsg sql output variable to an appropriate value like an empty string or double double-quotes ("") in the if condition parts of your SP code,as a good coding practice.

answered Mar 08 '12 at 15:16

Uday0119

770
1
7
23

Yes, you right, but in the c# code, I have this: if (pErrorMsg.Value.Equals(System.DBNull.Value) != true) keepError = pErrorMsg.Value.ToString(); I look for a null value, so that's why I'm not initialized the @ErrorMsg with ''. However +1 because it's a good point :) – Somebody Mar 08 '12 at 15:21
Yes, and here it will be a DBNull and most people confuse here with null. So as a good coding practice, one must initialize all the variables even if some are used conditionally. At least, this ensure that you'll neven encounter NullReferenceException. – Uday0119 Mar 08 '12 at 15:36

score 0 · Answer 3 · answered Mar 08 '12 at 15:03

0

You're creating the pErrorMsg parameter, but where are you adding it to your command?

answered Mar 08 '12 at 15:03

Val Akkapeddi

1,173
10
17

Output parameter issue in sql server and c#

3 Answers3