同时插入两表，为啥只执行一个？

enasp 2009-01-05 11:36:18

我的webservice，本来准备用触发器的，结果因为权限操作问题，不能用触发器，所以只能用两个SQL语句





string result = "";

     

            string sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "')";

   



            string sql1 = "insert into t2(newid,title) values ('" + newid + "','" + title + "')";





            MySqlConnection DefSqlCon = new MySqlConnection("server=192.168.0.1;user id=nettest;password=net123;database=test");





            DefSqlCon.Open();



            MySqlCommand sqlcmd = new MySqlCommand(sql, DefSqlCon);

            MySqlCommand sqlcmd1 = new MySqlCommand(sql1, DefSqlCon);



            sqlcmd.ExecuteNonQuery();

            sqlcmd1.ExecuteNonQuery();



            result = "1";

            return result;

            DefSqlCon.Close();

...全文

260 31 打赏收藏转发到动态举报

写回复

用AI写文章

31 条回复

切换为时间正序

请发表友善的回复…

发表回复

dqlfjy 2009-01-08

打赏
举报

先加个try..catch看看
不行的话，把操作写成一个存储过程拿到sql里调试.

enasp 2009-01-08

打赏
举报

[Quote=引用 21 楼 liu2008hz 的回复:]
引用 6 楼 enasp 的回复:
事务怎么弄？

不要触发器

C# code
你先建议一个数据库连接，比如是SQL的连接
SqlConnection sqlcon=new SqlConnection("连接字符串");
sqlcon.open();
SqlTransaction tran = sqlcon.BeginTransaction();
SqlCommand sqlcmd1 = new SqlCommand();
SqlCommand sqlcmd2 = new SqlCommand();

string str1="delete from table1 where id=1";
string str2="delete from table2 where i…
[/Quote]

错误 1 “System.Data.SqlClient.SqlTransaction”不包含“commit”的定义，并且找不到可接受类型为“System.Data.SqlClient.SqlTransaction”的第一个参数的扩展方法“commit”(是否缺少 using 指令或程序集引用?)

zhangzhicong 2009-01-05

打赏
举报

干脆用储存过程。
传如三参数。
create pro...

insert into t1(newid title content) values (@newid ,@title ,@content)

insert into t2(newid,title) values (@newid ,@title )

hz890 2009-01-05

打赏
举报

string result = "";

string sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "')";

string sql1 = "insert into t2(newid,title) values ('" + newid + "','" + title + "')";

MySqlConnection DefSqlCon = new MySqlConnection("server=192.168.0.1;user id=nettest;password=net123;database=test");

DefSqlCon.Open();
MySqlCommand sqlcmd = new MySqlCommand(sql, DefSqlCon);
sqlcmd.ExecuteNonQuery();
DefSqlCon.Close();

DefSqlCon.Open();
MySqlCommand sqlcmd1 = new MySqlCommand(sql1, DefSqlCon);
sqlcmd1.ExecuteNonQuery();

DefSqlCon.Close();

result = "1";
return result;

royrandy 2009-01-05

打赏
举报

使用事务吧！

hz890 2009-01-05

打赏
举报

[Quote=引用 15 楼 hz890 的回复:]
晕，到现在似乎看明白了：
string result = "";

string sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "')";

string sql1 = "insert into t2(newid,title) values ('" + newid + "','" + title + "')";

MySqlConnection DefSqlCon = new MySqlConnection("server=192.168.0.1;user id=nettest;password=net…
[/Quote]

MySqlCommand sqlcmd = new MySqlCommand(sql, DefSqlCon);
sqlcmd.ExecuteNonQuery();
DefSqlCon.Close();

DefSqlCon.Open();

MySqlCommand sqlcmd1 = new MySqlCommand(sql1, DefSqlCon);
sqlcmd1.ExecuteNonQuery();
DefSqlCon.Close();

hz890 2009-01-05

打赏
举报

晕，到现在似乎看明白了：
string result = "";

string sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "')";

string sql1 = "insert into t2(newid,title) values ('" + newid + "','" + title + "')";

MySqlConnection DefSqlCon = new MySqlConnection("server=192.168.0.1;user id=nettest;password=net123;database=test");

DefSqlCon.Open();

MySqlCommand sqlcmd = new MySqlCommand(sql, DefSqlCon);
sqlcmd.ExecuteNonQuery();
DefSqlCon.Close();

MySqlCommand sqlcmd1 = new MySqlCommand(sql1, DefSqlCon);
sqlcmd1.ExecuteNonQuery();

DefSqlCon.Close();

result = "1";
return result;

duduly 2009-01-05

打赏
举报

可以执行多条语句

hz890 2009-01-05

打赏
举报

string result = "";

string sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "')";

string sql1 = "insert into t2(newid,title) values ('" + newid + "','" + title + "')";

MySqlConnection DefSqlCon = new MySqlConnection("server=192.168.0.1;user id=nettest;password=net123;database=test");

DefSqlCon.Open();

MySqlCommand sqlcmd = new MySqlCommand(sql, DefSqlCon);
sqlcmd.ExecuteNonQuery();

MySqlCommand sqlcmd1 = new MySqlCommand(sql1, DefSqlCon);
sqlcmd1.ExecuteNonQuery();

DefSqlCon.Close();

result = "1";
return result;

hz890 2009-01-05

打赏
举报

[Quote=引用 10 楼 enasp 的回复:]
引用 8 楼 kissqi 的回复:
引用 1 楼 loworth 的回复:
Mysql的不懂
应该可以写在一起吧

C# codestring sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "');insert into t2(newid,title) values ('" + newid + "','" + title + "')";

楼主没必要分成两个SQL语句。放在一起执行，更能提高性能

这两个表，我想用一条SQL语句，可以吗？这样写法不对，…
[/Quote]

string result = "";

string sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "')";

string sql1 = "insert into t2(newid,title) values ('" + newid + "','" + title + "')";

MySqlConnection DefSqlCon = new MySqlConnection("server=192.168.0.1;user id=nettest;password=net123;database=test");

DefSqlCon.Open();

MySqlCommand sqlcmd = new MySqlCommand(sql, DefSqlCon);
sqlcmd.ExecuteNonQuery();

MySqlCommand sqlcmd1 = new MySqlCommand(sql1, DefSqlCon);
sqlcmd1.ExecuteNonQuery();

result = "1";
return result;
DefSqlCon.Close();

设备管理系统 2009-01-05

打赏
举报

sql server支持一次执行多条sql语句
而oracle和ACCESS不支持

LZ的sql不好啊，容易sql注入，传参吧

enasp 2009-01-05

打赏
举报

[Quote=引用 8 楼 kissqi 的回复:]
引用 1 楼 loworth 的回复:
Mysql的不懂
应该可以写在一起吧

C# codestring sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "');insert into t2(newid,title) values ('" + newid + "','" + title + "')";

楼主没必要分成两个SQL语句。放在一起执行，更能提高性能
[/Quote]

这两个表，我想用一条SQL语句，可以吗？这样写法不对，第二个表没有插入数据

enasp 2009-01-05

打赏
举报

[Quote=引用 1 楼 loworth 的回复:]
Mysql的不懂
应该可以写在一起吧

C# codestring sql = "insert into t1(newid title content) values ('" + newid + "','" + title + "','" + content + "');insert into t2(newid,title) values ('" + newid + "','" + title + "')";
[/Quote]

呵呵，试了一下，不行