ExecuteScalar多个事务

Question

我正尝试在查询中使用ODBC连接到mysql来执行ExecuteScalar()。我的问题是：

string query = @"SET @MY_ID := (SELECT IFNULL(MAX(id) + 1, 1) FROM sc);" + Environment.NewLine + "INSERT INTO sc (id, name) values (@MY_ID, 'TESTE');"

我得到了：

{System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [MySQL][ODBC 5.1 Driver] [mysqld-5.0.83-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO

Answer 1

不能使用SET从结果集中初始化变量，需要实际执行查询

 SELECT @MY_ID := ...

Answer 2

试试这个：

string query = "Declare @MY_ID int;Select @MY_ID  = IFNULL(MAX(id) + 1, 1) from sc;INSERT INTO sc (id, name) values (@MY_ID, 'TESTE');"
OleDBCommand command=new OleDbCommand(query,connectionObj);
int status;

status=command.ExecuteNonQuery();

if(status>0)
{
//success
}