Using Placeholder TextBoxes- Error Message Procedure or function expects parameter which was not supplied

Question

I have a function that I would want to use to check if a seal number exists in the database but whenever I add the parameters it is telling to supply parameter when the parameter is there. If it exists it should throw at an error message to user. Based on the fact that I'm using a placeholder textbox; I'm not sure if I'm going about it the right way as it is giving this error message Procedure or function 'PP_CountSeal' expects parameter '@seal2', which was not supplied

Stored Procedure

ALTER PROCEDURE [dbo].[PP_CountSeal] 
-- Add the parameters for the stored procedure here
@seal1 nchar(15),
@seal2 nchar(15),
@seal3 nchar(15),
@seal4 nchar(15),
@seal5 nchar(15),
@seal6 nchar(15),
@seal7 nchar(15)

AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- nchar(10),erfering with SELECT statements. --SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT seal1,seal2,seal3,seal4,seal5,seal6,seal7  From TWCL_OPERATIONS.dbo.PP_SealNumber    
WHERE seal1 = @seal1   or seal1=@seal2 or seal1=@seal3 or seal1=@seal4 or seal1=@seal5 or seal1=@seal6 or seal1=@seal7
or seal2 = @seal1 or seal2=@seal2 or seal2=@seal3 or seal2=@seal4 or seal2=@seal5 or seal2=@seal6 or seal2=@seal7
or seal3 = @seal1 or seal3=@seal2 or seal3=@seal3 or seal3=@seal4 or seal3=@seal5 or seal3=@seal6 or seal3=@seal7
or seal4 = @seal1 or seal4=@seal2 or seal4=@seal3 or seal4=@seal4 or seal4=@seal5 or seal4=@seal6 or seal4=@seal7
or seal5 = @seal1 or seal5=@seal2 or seal5=@seal3 or seal5=@seal4 or seal5=@seal5 or seal5=@seal6 or seal5=@seal7
or seal6 = @seal1 or seal6=@seal2 or seal6=@seal3 or seal6=@seal4 or seal6=@seal5 or seal6=@seal6 or seal6=@seal7
or seal7 = @seal1 or seal7=@seal2 or seal7=@seal3 or seal7=@seal4 or seal7=@seal5 or seal7=@seal6 or seal7=@seal7

END GO //Check if Seal Number exists private bool SealCheck() { bool R = true; int a = 0;

        SqlConnection con = new SqlConnection(connection);
        con.Open();

    

foreach (Control cntrl in phSealNum.Controls)
        {

            SqlCommand C = new SqlCommand("PP_CountSeal", con);
            C.CommandType = CommandType.StoredProcedure;
            //Add Parameters
            TextBox txt = (TextBox)cntrl;
            string str = txt.Text.TrimEnd();

          

           string seal = string.Format("@seal{0}", a);
           C.Parameters.AddWithValue(seal, str);

           R = Convert.ToBoolean(C.ExecuteScalar());

           a++;
    }



 protected void Update_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(connection);
            
            #region Seal Data
           
            string previousseal1 = null;
            string previousseal2 = null;
            string previousseal3 = null;
            string previousseal4 = null;
            string previousseal5 = null;
            string previousseal6 = null;
            string previousseal7 = null;

            bool X = SealCheck();

            string name = HttpContext.Current.User.Identity.Name;

            if (X == false)
            {
                SqlCommand command = new SqlCommand("PP_SealRecord", con);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@loadSheetNum", dispatchSheetNo);


                SqlDataReader data = command.ExecuteReader();
                if (data.Read())
                {
                    previousseal1 = Convert.ToString(data["seal1"]);

                    previousseal2 = Convert.ToString(data["seal2"]);

                    previousseal3 = Convert.ToString(data["seal3"]);


                    previousseal4 = Convert.ToString(data["seal4"]);


                    previousseal5 = Convert.ToString(data["seal5"]);

                    previousseal6 = Convert.ToString(data["seal6"]);


                    previousseal7 = Convert.ToString(data["seal7"]);
                    
                    EditSeal(dispatchSheetNo, previousseal1, previousseal2, previousseal3, previousseal4, previousseal5, previousseal6, previousseal7, name);

                }
                else
                {
                    // INSERT STATEMENT
                    CreateSeal(loadsheet, CreatedBy);
                }
            }
            else
            {
                lblError.Text = "Seal Number Exists"; 
            }

            con.Close();
            #endregion

            }

            
        
        showData();
    }

manthan manthan · Accepted Answer · 2020-07-09T05:24:21

Your code:

 foreach (Control cntrl in phSealNum.Controls)
 {
    
     SqlCommand C = new SqlCommand("PP_CountSeal", con);
     C.CommandType = CommandType.StoredProcedure;
     //Add Parameters
     TextBox txt = (TextBox)cntrl;
     string str = txt.Text.TrimEnd();
    
              
     string seal = string.Format("@seal{0}", a);
     C.Parameters.AddWithValue(seal, str);

     R = Convert.ToBoolean(C.ExecuteScalar());
     a++;
 }

Your sp is asking for all parameters but you are iterating phSealNum.Controls collection, in the loop body you passing one parameter and executing sp and next missing parameter is @seal2 which is not passed causing error.

My code:

    SqlCommand C = new SqlCommand("PP_CountSeal", con);
    C.CommandType = CommandType.StoredProcedure;
    foreach (Control cntrl in phSealNum.Controls)
    {
        //Add Parameters
        TextBox txt = (TextBox)cntrl;
        string str = txt.Text.TrimEnd();
        string seal = string.Format("@seal{0}", a);
        a++;
        C.Parameters.AddWithValue(seal, str);
    }
  

    R = Convert.ToBoolean(C.ExecuteScalar());

Here I am adding all parameters in loop body and then executing query. hope it helps. Please let me know about the same.

Using Placeholder TextBoxes- Error Message Procedure or function expects parameter which was not supplied

1 Answers