A character to numeric conversion process failed

Question

I face the following exception when i try to use parametrized query with informix .

A character to numeric conversion process failed

---

                    StringBuilder sQuery = new StringBuilder();
                    Dictionary<string, string> paramList = new Dictionary<string, string>();
                    cmdTxt.Append(" UPDATE userwidgetto SET widget_color = ?, widget_column = ?, widget_order = ?,widget_state = ?,widget_type = ? ");
                    cmdTxt.Append(" WHERE process_id = ? AND emp_num = ? ");

                    paramList.Add("process_id", Process_id.ToString());
                    paramList.Add("emp_num", Emp_num.ToString());
                    paramList.Add("widget_color", Widget_color.TrimEnd());
                    paramList.Add("widget_column", Widget_column.ToString().TrimEnd());
                    paramList.Add("widget_order", Widget_order.ToString());
                    paramList.Add("widget_state", Widget_state.ToString());
                    paramList.Add("widget_type", Widget_type.ToString());
                    affectedRow = DAL_Helper.Execute_NonQuery(cmdTxt.ToString(), CommandType.Text, paramList);

---

public int Execute_NonQuery(string cmdText, CommandType cmdType, Dictionary<string, string> Param_arr)
            {
                Open_Connection();
                int return_val = -1;
                command.CommandText = cmdText;
                command.CommandType = cmdType;
                command.Transaction = current_trans;
                command.Parameters.Clear();
                if (Param_arr != null)
                {
                    foreach (KeyValuePair<string, string> parameter in Param_arr)
                    {
                        param = command.CreateParameter();
                        param.ParameterName = parameter.Key.ToString();
                        if (parameter.Value.ToString() == "Null" || string.IsNullOrEmpty(parameter.Value))
                            param.Value = DBNull.Value;
                        else
                            param.Value = parameter.Value.ToString();
                        command.Parameters.Add(param);
                    }
                }
                try
                {
                    return_val = command.ExecuteNonQuery();//means no error message //OK                
                }
                catch (IfxException ifxEx)// Handle IBM.data.informix : mostly catched
                {
                    return_val = ifxEx.Errors[0].NativeError;
                }
                catch (Exception Ex)
                {
                    ErrMapping.WriteLog("\r\n" + Ex.Message);
                    return_val = ExCodeConst;
                }
                finally
                {
                    Close_Connection();
                }
                return return_val;
            }

Answer 1

I suppose you are using an OleDb client. In that case, the parameters should be inserted in the same exact position order of you command string placeholders (?)

cmdTxt.Append(" UPDATE userwidgetto SET widget_color = ?, widget_column = ?, widget_order = ?,widget_state = ?,widget_type = ? ");
cmdTxt.Append(" WHERE process_id = ? AND emp_num = ? ");
paramList.Add("widget_color", Widget_color.TrimEnd());
paramList.Add("widget_column", Widget_column.ToString().TrimEnd());
paramList.Add("widget_order", Widget_order.ToString());
paramList.Add("widget_state", Widget_state.ToString());
paramList.Add("widget_type", Widget_type.ToString()); 
paramList.Add("process_id", Process_id.ToString());
paramList.Add("emp_num", Emp_num.ToString());
affectedRow = DAL_Helper.Execute_NonQuery(cmdTxt.ToString(), CommandType.Text, paramList); 

Also the Dictionary<string, string> paramList forces every value passed to the DAL_Helper to be of string type.
And, I think, this is not the case from the parameters names given (process_id is not a string in the database table right?).
As someone as already suggested in a comment, you should change the paramList to be a Dictionary<string, object> and then add the parameters with a string conversion only where is appropriate (meaning, the datatype of the table column is a string type).