"Procedure or function expects parameter which was not supplied."

0
votes

I'm trying to execute a stored procedure and print the output, but when I run the below code I'm getting error like "Procedure or function 'SPInsertLocal' expects parameter '@RES', which was not supplied."

private void InsertPdtLocal(string code, string PON,string Qty)
        {
            string str = Properties.Settings.Default.conLocal;
            SqlConnection con = new SqlConnection(str);
            SqlCommand cmd = new SqlCommand("Execute SPInsertLocal @PON,@TCode,@Qty,@Type", con);
            try
            {
                con.Open();
                cmd.CommandTimeout = 150;
                cmd.Parameters.AddWithValue("@PON", PON);
                cmd.Parameters.AddWithValue("@Qty", Qty);
                cmd.Parameters.AddWithValue("@TCode", code);
                cmd.Parameters.AddWithValue("@Type", Globals.s_type);
                SqlParameter output = new SqlParameter("@RES", SqlDbType.Int);
                output.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(output);
                cmd.ExecuteNonQuery();
                con.Close();
                int id = Convert.ToInt32(output.Value);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

What I'm doing wrong here?

2
c#stored-procedures
Your SQLCommand doesn't contain @RES. Also you could just use new SqlCommand("SPInsertLocal", con) and then specify CommandType = CommandType.StoredProcedure; - C. Knight
You should probably use using statements as well as SQLCommand implements IDisposible - C. Knight

2 Answers

1
votes
SqlCommand cmd = new SqlCommand("Execute SPInsertLocal @PON,@TCode,@Qty,@Type,@RES", con);

I was not passing the parameter , fixed the issue

0
votes

You can refactor the code as follows where the using statement is used for the auto management of connection closing and avoid hardcoding Execute statement in c# code which is a bad practice

private void InsertPdtLocal(string code, string PON,string Qty)
        {
            string str = Properties.Settings.Default.conLocal;
            try
            {

            using (SqlConnection con = new SqlConnection(str))
            {
                using (SqlCommand cmd =  con.CreateCommand())
                {
                     cmd.Parameters.AddWithValue("@PON", PON);
                     cmd.Parameters.AddWithValue("@Qty", Qty);
                     cmd.Parameters.AddWithValue("@TCode", code);
                     cmd.Parameters.AddWithValue("@Type", Globals.s_type);
                     var output = cmd.Parameters.Add("@RES" , SqlDbType.Int);
                     output.Direction  = ParameterDirection.Output;
                     cmd.ExecuteNonQuery();
                     int id = Convert.ToInt32(output.Value);
                }
            }

            }
             catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }