c# - 插入查询的 SQL 格式错误

Question

我得到了一些插入国家/地区查询的代码，我需要将其更改为参数，但我收到此错误:请确保所有字段的格式正确(数字字段 [0-9]、日期 [dd/mm/yyyy]

我在创建国家/地区时输入了正确的格式，所以我不知道为什么会出现此错误。原始 sql 查询已被注释掉，以便您可以看到我所做的更改。

 public static int InsertCountry(int Company_ID, string CountryISO, string CountryName, int Currency, int IsActive, int CreatedByUser, string CountryCode, string Operator, string TransitTime, string Departures, string ImportOperator, string ImportTransitTime, string ImportDepartures, decimal? SECAAmt, decimal? SECAPer)
            {
                int ID = -1;
                string sql = "proc_InsertCoutnry";

                using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(DataUtils.ConnectionStrings["TAT"]))
                {
                    conn.Open();
                    using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                       /* cmd.CommandText = "INSERT INTO countries (Company_ID, CountryISO,CountryName,CurrencyID,IsActive,CreatedByUser,CreatedDate,ModifiedByUser,ModifiedDate,CountryCode,Operator,TransitTime,Departures,ImportOperator,ImportTransitTime,ImportDepartures, SECAAmt,  SECAPercent) " +
                                           "VALUES(" + Company_ID + ", '" + CountryISO.Replace("'", "''") + "', '" + CountryName.Replace("'", "''") + "'," + Currency + ", " + IsActive + "," + CreatedByUser + ",NOW()," + CreatedByUser + ",NOW(),'" + CountryCode.Replace("'", "''") + "','" + Operator.Replace("'", "''") + "','" + TransitTime.Replace("'", "''") + "','" + Departures.Replace("'", "''") + "','" + ImportOperator.Replace("'", "''") + "','" + ImportTransitTime.Replace("'", "''") + "','" + ImportDepartures.Replace("'", "''") + "', ?SECAAmt, ?SECAPer);SELECT LAST_INSERT_ID();"; */

                        cmd.CommandText = @"INSERT INTO countries (Company_ID, CountryISO,CountryName,CurrencyID,IsActive,CreatedByUser,CreatedDate,ModifiedByUser,ModifiedDate,CountryCode, Operator,TransitTime,Departures,ImportOperator,ImportTransitTime,ImportDepartures, SECAAmt,  SECAPercent) " +
                                           "VALUES(?company_ID, ?countryISO, ?countryName, ?currency, ?isActive, ?createdByUser,NOW(),?createdByUser,NOW(), ?countryCode, ?operator," +
                                            "?transitTime, ?departures, ?importOperator, ?importTransitTime, ?importDepartures, ?SECAAmt, ?SECAPer);SELECT LAST_INSERT_ID();";

                        cmd.Parameters.Add(new MySqlParameter("company_ID", Company_ID));
                        cmd.Parameters.Add(new MySqlParameter("countryISO", CountryISO));
                        cmd.Parameters.Add(new MySqlParameter("countryName", CountryName));
                        cmd.Parameters.Add(new MySqlParameter("currency", Currency));
                        cmd.Parameters.Add(new MySqlParameter("isActive", IsActive));
                        cmd.Parameters.Add(new MySqlParameter("createdByUser", CreatedByUser));
                        cmd.Parameters.Add(new MySqlParameter("operator", Operator));
                        cmd.Parameters.Add(new MySqlParameter("transitTime", TransitTime));
                        cmd.Parameters.Add(new MySqlParameter("departures", Departures));
                        cmd.Parameters.Add(new MySqlParameter("importOperator", ImportOperator));
                        cmd.Parameters.Add(new MySqlParameter("importTransitTime", ImportTransitTime));
                        cmd.Parameters.Add(new MySqlParameter("importDepartures", ImportDepartures));
                        cmd.Parameters.Add(new MySqlParameter("SECAAmt", SECAAmt));
                        cmd.Parameters.Add(new MySqlParameter("SECAPer", SECAPer));
                        ID = int.Parse(cmd.ExecuteScalar().ToString());               
                    }
                }
                return ID;
            }

Answer 1

尝试

 cmd.Parameters.Add("@someparam", SqlDbType.NVarChar).Value

向数据库添加数据时应声明类型