c#写mysql，带参数的sql语句，但是参数始终加不进去，麻烦大家帮我解决！

Question

源代码如下：                string source = "DSN=MySqlOperation";
                OdbcConnection conn = null;
                OdbcCommand cmd = null;                conn = new OdbcConnection(source);
                conn.Open();
                cmd = conn.CreateCommand();                foreach (Player p in players)
                {
                    string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES(@ChineseName,@EnglishName,@Team,@Birthday,@Height,@Weight,@ComeFrom,@Career)";                    OdbcParameter para_0 = new OdbcParameter("@EnglishName", OdbcType.VarChar, 50);
                    para_0.Value = p.EnglishName;
                    cmd.Parameters.Add(para_0); Console.WriteLine(p.EnglishName);                    OdbcParameter para_1 = new OdbcParameter("@ChineseName", OdbcType.VarChar, 50);
                    para_1.Value = p.ChineseName;
                    cmd.Parameters.Add(para_1); Console.WriteLine(p.ChineseName);                    OdbcParameter para_2 = new OdbcParameter("@Team", OdbcType.VarChar, 50);
                    para_2.Value = p.Team;
                    cmd.Parameters.Add(para_2); Console.WriteLine(p.Team);                    OdbcParameter para_3 = new OdbcParameter("@Birthday", OdbcType.VarChar, 50);
                    para_3.Value = p.Birthday;
                    cmd.Parameters.Add(para_3); Console.WriteLine(p.Birthday);                    OdbcParameter para_4 = new OdbcParameter("@Height", OdbcType.VarChar, 50);
                    para_4.Value = p.Height;
                    cmd.Parameters.Add(para_4); Console.WriteLine(p.Height);                    OdbcParameter para_5 = new OdbcParameter("@Weight", OdbcType.VarChar, 50);
                    para_5.Value = p.Weight;
                    cmd.Parameters.Add(para_5); Console.WriteLine(p.Weight);                    OdbcParameter para_6 = new OdbcParameter("@ComeFrom", OdbcType.VarChar, 50);
                    para_6.Value = p.ComeFrom;
                    cmd.Parameters.Add(para_6); Console.WriteLine(p.ComeFrom);                    OdbcParameter para_7 = new OdbcParameter("@Career", OdbcType.Int);
                    para_7.Value = p.Career;
                    cmd.Parameters.Add(para_7); Console.WriteLine(p.Career);                    cmd.CommandText = sqlStr;
                    cmd.ExecuteNonQuery();
                }
报错说“colomn ‘chineseName’ cannot be null”，意思是说@ChineseName没加进去？
请高手把把脉！
谢了！

Answer 1

p.ChineseName 现在是不是 NULL 值。 错误信息说  ChineseName 字段不能为NULL 值

Answer 2

chineseName字段的内容不能为空，你传递的参数是null，断点调试

Answer 3

首先是p.ChineseName是否有值？
如果有值，可以用cmd.Parameters.AddWithValue("@ChineseName", p.ChineseName);试一下......

Answer 4

                string source = "DSN=MySqlOperation";
                OdbcConnection conn = null;
                OdbcCommand cmd = null;                conn = new OdbcConnection(source);
                conn.Open();
                cmd = conn.CreateCommand();                foreach (Player p in players)
                {
                    string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES(@ChineseName,@EnglishName,@Team,@Birthday,@Height,@Weight,@ComeFrom,@Career)";                    OdbcParameter para_0 = new OdbcParameter("@EnglishName", OdbcType.VarChar, 50);
                    para_0.Value = p.EnglishName;
                    cmd.Parameters.Add(para_0); Console.WriteLine(p.EnglishName);                    //OdbcParameter para_1 = new OdbcParameter("@ChineseName", OdbcType.VarChar, 50);
                    //para_1.Value = p.ChineseName;
                    //cmd.Parameters.Add(para_1); Console.WriteLine(p.ChineseName);
                    cmd.Parameters.AddWithValue("@ChineseName", p.ChineseName);                      OdbcParameter para_2 = new OdbcParameter("@Team", OdbcType.VarChar, 50);
                    para_2.Value = p.Team;
                    cmd.Parameters.Add(para_2); Console.WriteLine(p.Team);                    OdbcParameter para_3 = new OdbcParameter("@Birthday", OdbcType.VarChar, 50);
                    para_3.Value = p.Birthday;
                    cmd.Parameters.Add(para_3); Console.WriteLine(p.Birthday);                    OdbcParameter para_4 = new OdbcParameter("@Height", OdbcType.VarChar, 50);
                    para_4.Value = p.Height;
                    cmd.Parameters.Add(para_4); Console.WriteLine(p.Height);                    OdbcParameter para_5 = new OdbcParameter("@Weight", OdbcType.VarChar, 50);
                    para_5.Value = p.Weight;
                    cmd.Parameters.Add(para_5); Console.WriteLine(p.Weight);                    OdbcParameter para_6 = new OdbcParameter("@ComeFrom", OdbcType.VarChar, 50);
                    para_6.Value = p.ComeFrom;
                    cmd.Parameters.Add(para_6); Console.WriteLine(p.ComeFrom);                    OdbcParameter para_7 = new OdbcParameter("@Career", OdbcType.Int);
                    para_7.Value = p.Career;
                    cmd.Parameters.Add(para_7); Console.WriteLine(p.Career);                    cmd.CommandText = sqlStr;
                    cmd.ExecuteNonQuery();
                }

Answer 5

不用写那么多的代码直接使用AddWithValue就行。string sqlStr = @"INSERT
                          INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                          VALUES(@ChineseName,@EnglishName,@Team,@Birthday,@Height,@Weight,@ComeFrom,@Career)";
cmd.Parameters.AddWithValue("@ChineseName", p.ChineseName);//断点到这里看看这里的p.ChineseName是不是null
cmd.Parameters.AddWithValue("@EnglishName", p.EnglishName);
cmd.Parameters.AddWithValue("@Team", p.Team);
cmd.Parameters.AddWithValue("@Birthday", p.Birthday);
cmd.Parameters.AddWithValue("@Height", p.Height);
cmd.Parameters.AddWithValue("@Weight", p.Weight);
cmd.Parameters.AddWithValue("@ComeFrom", p.ComeFrom);
cmd.Parameters.AddWithValue("@Career", p.Career);

Answer 6

谢谢ls几位，我换成“cmd.Parameters.AddWithValue("@ChineseName", p.ChineseName);”试过了，但是结果还是一样。
实例p里的属性没问题。输出结果可以看得到。前面输出的是p的属性，我后来添加的代码。和帖子里贴出的代码有区别的！错误请看：

Answer 7

问题依然没有解决啊，大家继续开动脑筋。goon

Answer 8

            string source = "DSN=MySqlOperation";
            OdbcConnection conn = null;
            OdbcCommand cmd = null;            conn = new OdbcConnection(source);
            conn.Open();
            cmd = conn.CreateCommand();            foreach (Player p in players)
            {
                string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}')";
                sqlStr = String.Format(sqlStr, p.EnglishName, p.ChineseName, p.Team, p.Birthday, p.Height, p.Weight,
                    p.ComeFrom, p.Career);                System.Console.WriteLine(sqlStr);  // 看下SQL                cmd.CommandText = sqlStr;
                cmd.ExecuteNonQuery();
            }

Answer 9

qiume,你这个方法可以的。我想知道我写的方法到底错在哪里？我以前一直这样写的。
谁能告诉我答案吗？
困惑中！

Answer 10

foreach (Player p in players)
                {
                    string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES(@ChineseName,@EnglishName,@Team,@Birthday,@Height,@Weight,@ComeFrom,@Career)";                    cmd.CommandText = sqlStr;                    OdbcParameter para_0 = cmd.Parameters.Add("@EnglishName", OdbcType.VarChar, 50);
                    para_0.Value = p.EnglishName;
                    cmd.Parameters.Add(para_0); Console.WriteLine(p.EnglishName);                    OdbcParameter para_1 = cmd.Parameters.Add("@ChineseName", OdbcType.VarChar, 50);
                    para_1.Value = p.ChineseName;
                    cmd.Parameters.Add(para_1); Console.WriteLine(p.ChineseName);                    OdbcParameter para_2 = cmd.Parameters.Add("@Team", OdbcType.VarChar, 50);
                    para_2.Value = p.Team;
                    cmd.Parameters.Add(para_2); Console.WriteLine(p.Team);                    OdbcParameter para_3 = cmd.Parameters.Add("@Birthday", OdbcType.VarChar, 50);
                    para_3.Value = p.Birthday;
                    cmd.Parameters.Add(para_3); Console.WriteLine(p.Birthday);                    OdbcParameter para_4 = cmd.Parameters.Add("@Height", OdbcType.VarChar, 50);
                    para_4.Value = p.Height;
                    cmd.Parameters.Add(para_4); Console.WriteLine(p.Height);                    OdbcParameter para_5 = cmd.Parameters.Add("@Weight", OdbcType.VarChar, 50);
                    para_5.Value = p.Weight;
                    cmd.Parameters.Add(para_5); Console.WriteLine(p.Weight);                    OdbcParameter para_6 = cmd.Parameters.Add("@ComeFrom", OdbcType.VarChar, 50);
                    para_6.Value = p.ComeFrom;
                    cmd.Parameters.Add(para_6); Console.WriteLine(p.ComeFrom);                    OdbcParameter para_7 = cmd.Parameters.Add("@Career", OdbcType.Int);
                    para_7.Value = p.Career;
                    cmd.Parameters.Add(para_7); Console.WriteLine(p.Career);                    cmd.ExecuteNonQuery();
                }

Answer 11

字符串先用这个东东包装一下：        public static string quotedString(string str)
        {
            return String.Format("'{0}'", str.Replace("'", "''"));
        }

Answer 12

            string source = "DSN=MySqlOperation";
            OdbcConnection conn = null;
            OdbcCommand cmd = null;            conn = new OdbcConnection(source);
            conn.Open();
            cmd = conn.CreateCommand();            foreach (Player p in players)
            {
                string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES({0},{1},{2},{3},{4},{5},{6},{7})";
                sqlStr = String.Format(sqlStr, quotedString(p.EnglishName), quotedString(p.ChineseName), quotedString(p.Team), quotedString(p.Birthday), p.Height, p.Weight,
                    quotedString(p.ComeFrom), quotedString(p.Career));                System.Console.WriteLine(sqlStr);  // 看下SQL                cmd.CommandText = sqlStr;
                cmd.ExecuteNonQuery();
            }

Answer 13

没有用过c# 操作MySql 你参考一下这个OdbcConnection Example - C#
另外你参考一下使用MySQL Connector/Net 1.0 

Answer 14

谢谢你的资料！我看到一段代码：using (OdbcCommand com = new OdbcCommand(
    "SELECT ColumnWord FROM OkieTable WHERE MagicKey = ?", con))
{
    com.Parameters.AddWithValue("@var", paramWord);    using (OdbcDataReader reader = com.ExecuteReader())
    {
        while (reader.Read())
        {
            string word = reader.GetString(0);
            // Word is from the database. Do something with it.
        }
    }
}
只有一个参数，用“？”，如果有两个参数的话，都用问号吗？然后参数按照先后次序对应加入command？

Answer 15

我在网络上帮你搜了下，的确有很多人出现这个问题，大概估计问题出在mysql的驱动上，建议用mysql for .net的驱动。
下面是别人解决问题的总结：问题解决了。总结一下：
1.mysql数据库驱动有两种：mysql-connector-odbc和mysql-connector-net,
  前者不支持参数，后者支持，这是我测试的结果；
2.参数变量使用跟SqlServer一样，只需把@变为？，
  如，mysql中写为：insert into test values (?id,?title,?type,now())";
3.使用mysql-connector-net驱动很简单，到网上下载mysql-connector-net-5.2.3-src，
  解压后，把mysql.data.dll添加到项目的bin文件夹即可使用；
4.连接本机mysql数据库方法例子如下：
  string connStr = "server=localhost;user id=root; password=yourPassword;database=yourDatabase";
  MySqlConnection conn = new MySqlConnection(connStr);下面附上测试通过的代码：
              string connStr = "server=localhost;user id=root; password=***; database=test";
            MySqlConnection conn = new MySqlConnection(connStr);
            if (conn != null)
                conn.Open();
            else
                return;                     string SQL_INSERT_TOPIC = "insert into test values (null,?title,?type,now())";
              string PARM_TITLE = "?title";
              string PARM_TYPE = "?type";           
              MySqlParameter[] parms = new MySqlParameter[]
              {
                new MySqlParameter(PARM_TITLE,MySqlDbType.VarChar,80),
                new MySqlParameter(PARM_TYPE,MySqlDbType.VarChar,1)           
                };
                parms[0].Value = "welcome to beijing";
                parms[1].Value = "C";                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_TOPIC;                foreach (MySqlParameter pram in parms)
                  cmd.Parameters.Add(pram);                cmd.ExecuteNonQuery();
                conn.Close();   

Answer 16

lz应该是用的不支持参数的odbc驱动，所以OdbcParameter的值总是为null。

Answer 17

下面是我修改过的代码，还是用的odbc，参数插入是成功了,但还是出现了别的问题。虽然foreach了list，但是写入数据库的值都是list里第一个实例，一共写了n遍。 
我唯一改动的地方，只是把values（）里都换成了“？”。 
ps：上面的代码外层还有一个foreach list，我没有贴出来。下面我贴出来了。                 foreach (Player p in players)
                {
                    string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES(?,?,?,?,?,?,?,?);";
                    cmd.CommandText = sqlStr;                    OdbcParameter para_0 = new OdbcParameter("@ChineseName", OdbcType.VarChar, 50);
                    para_0.Value = p.ChineseName;
                    cmd.Parameters.Add(para_0); Console.WriteLine(para_0.Value);                    OdbcParameter para_1 = new OdbcParameter("@EnglishName", OdbcType.VarChar, 50);
                    para_1.Value = p.EnglishName;
                    cmd.Parameters.Add(para_1); Console.WriteLine(p.EnglishName);                    OdbcParameter para_2 = new OdbcParameter("@Team", OdbcType.VarChar, 50);
                    para_2.Value = p.Team;
                    cmd.Parameters.Add(para_2); Console.WriteLine(p.Team);                    OdbcParameter para_3 = new OdbcParameter("@Birthday", OdbcType.VarChar, 50);
                    para_3.Value = p.Birthday;
                    cmd.Parameters.Add(para_3); Console.WriteLine(p.Birthday);                    OdbcParameter para_4 = new OdbcParameter("@Height", OdbcType.VarChar, 50);
                    para_4.Value = p.Height;
                    cmd.Parameters.Add(para_4); Console.WriteLine(p.Height);                    OdbcParameter para_5 = new OdbcParameter("@Weight", OdbcType.VarChar, 50);
                    para_5.Value = p.Weight;
                    cmd.Parameters.Add(para_5); Console.WriteLine(p.Weight);                    OdbcParameter para_6 = new OdbcParameter("@ComeFrom", OdbcType.VarChar, 50);
                    para_6.Value = p.ComeFrom;
                    cmd.Parameters.Add(para_6); Console.WriteLine(p.ComeFrom);                    OdbcParameter para_7 = new OdbcParameter("@Career", OdbcType.Int);
                    para_7.Value = p.Career;
                    cmd.Parameters.Add(para_7); Console.WriteLine(p.Career);
                    Console.WriteLine("----------------------------------------------------------");                    cmd.ExecuteNonQuery();
                }

Answer 18

            foreach (Player p in players)
            {
                string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES(?ChineseName,?EnglishName,?Team,?Birthday,?Height,?Weight,?ComeFrom,?Career);";
                cmd.CommandText = sqlStr;                OdbcParameter para_0 = new OdbcParameter("@ChineseName", OdbcType.VarChar, 50);
                para_0.Value = p.ChineseName;
                cmd.Parameters.Add(para_0); Console.WriteLine(para_0.Value);                OdbcParameter para_1 = new OdbcParameter("@EnglishName", OdbcType.VarChar, 50);
                para_1.Value = p.EnglishName;
                cmd.Parameters.Add(para_1); Console.WriteLine(p.EnglishName);                OdbcParameter para_2 = new OdbcParameter("@Team", OdbcType.VarChar, 50);
                para_2.Value = p.Team;
                cmd.Parameters.Add(para_2); Console.WriteLine(p.Team);                OdbcParameter para_3 = new OdbcParameter("@Birthday", OdbcType.VarChar, 50);
                para_3.Value = p.Birthday;
                cmd.Parameters.Add(para_3); Console.WriteLine(p.Birthday);                OdbcParameter para_4 = new OdbcParameter("@Height", OdbcType.VarChar, 50);
                para_4.Value = p.Height;
                cmd.Parameters.Add(para_4); Console.WriteLine(p.Height);                OdbcParameter para_5 = new OdbcParameter("@Weight", OdbcType.VarChar, 50);
                para_5.Value = p.Weight;
                cmd.Parameters.Add(para_5); Console.WriteLine(p.Weight);                OdbcParameter para_6 = new OdbcParameter("@ComeFrom", OdbcType.VarChar, 50);
                para_6.Value = p.ComeFrom;
                cmd.Parameters.Add(para_6); Console.WriteLine(p.ComeFrom);                OdbcParameter para_7 = new OdbcParameter("@Career", OdbcType.Int);
                para_7.Value = p.Career;
                cmd.Parameters.Add(para_7); Console.WriteLine(p.Career);
                Console.WriteLine("----------------------------------------------------------");                cmd.ExecuteNonQuery();
            }

Answer 19

不好意思，刚才看错了：            foreach (Player p in players)
            {
                string sqlStr = @"INSERT
                                      INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
                                      VALUES(?,?,?,?,?,?,?,?);";
                cmd.Parameters.Clear(); // 清除所有参数
                cmd.CommandText = sqlStr;                OdbcParameter para_0 = new OdbcParameter("@ChineseName", OdbcType.VarChar, 50);
                para_0.Value = p.ChineseName;
                cmd.Parameters.Add(para_0); Console.WriteLine(para_0.Value);                OdbcParameter para_1 = new OdbcParameter("@EnglishName", OdbcType.VarChar, 50);
                para_1.Value = p.EnglishName;
                cmd.Parameters.Add(para_1); Console.WriteLine(p.EnglishName);                OdbcParameter para_2 = new OdbcParameter("@Team", OdbcType.VarChar, 50);
                para_2.Value = p.Team;
                cmd.Parameters.Add(para_2); Console.WriteLine(p.Team);                OdbcParameter para_3 = new OdbcParameter("@Birthday", OdbcType.VarChar, 50);
                para_3.Value = p.Birthday;
                cmd.Parameters.Add(para_3); Console.WriteLine(p.Birthday);                OdbcParameter para_4 = new OdbcParameter("@Height", OdbcType.VarChar, 50);
                para_4.Value = p.Height;
                cmd.Parameters.Add(para_4); Console.WriteLine(p.Height);                OdbcParameter para_5 = new OdbcParameter("@Weight", OdbcType.VarChar, 50);
                para_5.Value = p.Weight;
                cmd.Parameters.Add(para_5); Console.WriteLine(p.Weight);                OdbcParameter para_6 = new OdbcParameter("@ComeFrom", OdbcType.VarChar, 50);
                para_6.Value = p.ComeFrom;
                cmd.Parameters.Add(para_6); Console.WriteLine(p.ComeFrom);                OdbcParameter para_7 = new OdbcParameter("@Career", OdbcType.Int);
                para_7.Value = p.Career;
                cmd.Parameters.Add(para_7); Console.WriteLine(p.Career);
                Console.WriteLine("----------------------------------------------------------");                cmd.ExecuteNonQuery();
            }

Answer 20

MySQL 没有试过，我以前在ORACLE里面遇到过因为在SQL语句中使用@参数名，而不能INSERT 的问题，你可以从  “@”这里找找问题。

Answer 21

哈哈 参数表示错了 Sql server中 用@ Mysql中用？ 就对了