Why parameterized query doesn't work although the procedure works fine

Question

I use the following method to calculate data:

  public static int PrepareData(int year, int month, int calcYear)
        {
            using (IfxConnection con = new IfxConnection(ConfigurationManager.ConnectionStrings["testable"].ToString()))
            {
                int res = 0;
                StringBuilder cmdTxt = new StringBuilder();
                cmdTxt.Append(" hk_calc_data ");
                using (var myIfxCmd = new IfxCommand(cmdTxt.ToString(), con))
                {
                    myIfxCmd.CommandType = CommandType.StoredProcedure;

                    myIfxCmd.Parameters.Add("p_year", IfxType.Integer);
                    myIfxCmd.Parameters.Add("p_month", IfxType.Integer);
                    myIfxCmd.Parameters.Add("p_calc_year", IfxType.Integer);

                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    myIfxCmd.Parameters[0].Value = year;
                    myIfxCmd.Parameters[1].Value = month;
                    myIfxCmd.Parameters[2].Value = calcYear;
                    myIfxCmd.CommandTimeout = 1000;
                    res = myIfxCmd.ExecuteNonQuery();
                }
                con.Close();
                con.Dispose();
                return res;
            }
        }

---

It takes no time and always returns -1! Although when I ran it in SQL editor with the same parameters, it takes about 4 minutes to complete and return 1 as result!

Answer 1

Maybe try clearing the params first, and also adding value with the Add.

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                myIfxCmd.Parameters.Clear();
                myIfxCmd.Parameters.Add("p_year", IfxType.Integer, year);
                myIfxCmd.Parameters.Add("p_month", IfxType.Integer, month);
                myIfxCmd.Parameters.Add("p_calc_year", IfxType.Integer, calcYear);