c#操作mysql数据库，要用带参数的sql语句，怎么写？

fihuang 2009-01-15 03:47:05

c#操作mysql数据库，要用带参数的sql语句，怎么写？
谁写个例子看看。我写的怎么改都出错！
谢谢大家！

...全文

742 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

fihuang 2009-01-15

打赏
举报

[Quote=引用 7 楼 feiyun0112 的回复:]
cmd.CommandText = sqlStr;
cmd.Parameters.Clear();
[/Quote]高手来了搞定

qiume 2009-01-15

打赏
举报

不好意思，刚才看错了：



            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();

            }

zhantianyou 2009-01-15

打赏
举报

mysql

select * from tableName where user_id=?id

mysqlcmd.params.addWithvalue("?id",value);

feiyun0112 2009-01-15

打赏
举报

值里面有单引号，替换成2个单引号

feiyun0112 2009-01-15

打赏
举报

cmd.CommandText = sqlStr;
cmd.Parameters.Clear();

fihuang 2009-01-15

打赏
举报

改好以后还是错误，截图请看：

qiume 2009-01-15

打赏
举报



            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();

            }

feiyun0112 2009-01-15

打赏
举报

string sqlStr = @"INSERT
INTO minfoerp.nbaPlayers(chineseName,englishName,team,birthday,height,weight,comeFrom,career)
VALUES(？,？,？,？,？,？,？,？)";

*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)

http://feiyun0112.cnblogs.com/

fihuang 2009-01-15

打赏
举报

下面是我修改过的代码，还是用的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();

                }

fihuang 2009-01-15

打赏
举报

代码来了，2红星高手请过目：



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();

                }

执行结果报错就是参数为null。没加进去。有人说odbc不支持参数，所以都是null。

feiyun0112 2009-01-15

打赏
举报

你的代码？

*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)

http://feiyun0112.cnblogs.com/