MySQL和插入最后的ID遗骸问题 [英] MySql and inserting last ID problem remains
本文介绍了MySQL和插入最后的ID遗骸问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
经过大量的阅读，并试图好吧，我仍然不能似乎得到这个工作：
CMD的OdbcCommand =新的OdbcCommand（插入用户（邮箱）VALUES（'rusty@msn.com'）; SELECT LAST_INSERT_ID（）;，CN）;cmd.ExecuteNonQuery（）;
使用（OdbcDataReader读卡器= cmd.ExecuteReader（））
{ 字符串theUserId =的String.Format（{0}，reader.GetString（0））;
Label10.Text = theUserId;
表：
用户
--------
用户ID（自动递增，PK）
电子邮件
运行在调试模式下，我得到这个行错误，
使用（OdbcDataReader读卡器= cmd.ExecuteReader（））
和
cmd.ExecuteNonQuery（）;
MySQL是指其出现语法错误在此行 SELECT LAST_INSERT_ID（）;，CN）;但是从香港专业教育学院读这是合法的。
确切的错误：
ERROR [42000] [MySQL的] [ODBC驱动程序3.51]的[mysqld-5.5.9]你在你的SQL语法错误;检查对应于你的MySQL服务器版本的权利语法使用附近的手册SELECT LAST_INSERT_ID（）在1号线
编辑：Justins方式：
使用（OdbcConnection连接=新OdbcConnection（驱动程序= {MySQL的ODBC驱动程序3.51};服务器=本地主机;数据库= gymwebsite2;用户=根;密码=突击队;））
{
// ODBC命令和交易对象
的OdbcCommand命令=新的OdbcCommand（）;
OdbcTransaction交易= NULL; //告诉命令来使用我们的连接
command.Connection =连接; 尝试
{
//打开连接
connection.Open（）; //开始事务
交易= connection.BeginTransaction（）; //指定交易对象挂起的本地事务。
command.Connection =连接;
command.Transaction =交易; // TODO：建立一个SQL INSERT语句
的OdbcCommand CMD =新的OdbcCommand（插入用户（电子邮件，名字，SecondName，出生日期，地点，Aboutme，用户名，密码）VALUES（'+ TextBox1.Text +，+ TextBox2.Text +'，' + TextBox3.Text +，+ TextBox4.Text +，+ TextBox5.Text +，+ TextBox6.Text +，+ TextBox7.Text +'，' + TextBox8.Text +'），连接）; //使用非查询调用运行插件
command.CommandText = cmd.ToString（）;
command.ExecuteNonQuery（）; / *现在我们要作出MYSQL第二个电话来获取新的索引
看重它的主键创建。这是利用标量称为所以它将
返回SQL语句的值。我们将其转换为以后使用一个int。* /
command.CommandText =选择LAST_INSERT_ID（）;;
ID = Convert.ToInt32（command.ExecuteScalar（））; //名称标识犯规不是在目前的情况下存在 //提交事务。
器transaction.commit（）;
}
赶上（异常前）
{
Label10.Text =：+ ex.Message; 尝试
{
//试图回滚事务。
transaction.Rollback（）;
}
抓住
{
//什么也不做在这里;交易不活跃。
}
}
}
解决方案
澄清，MySQL的.NET ODBC驱动程序不允许多个命令像你所描述的运行。你必须让两个单独的呼叫，并在一个事务中包装它们。
//使用我在其他地方定义了默认的连接字符串打开一个新的连接
使用（OdbcConnection连接=新OdbcConnection（s_connectionString））
{
// ODBC命令和交易对象
的OdbcCommand命令=新的OdbcCommand（）;
OdbcTransaction交易= NULL; //告诉命令来使用我们的连接
command.Connection =连接; 尝试
{
//打开连接
connection.Open（）; //开始事务
交易= connection.BeginTransaction（）; //指定交易对象挂起的本地事务。
command.Connection =连接;
command.Transaction =交易; // TODO：建立一个SQL INSERT语句
StringBuilder的SQL =新的StringBuilder（）; //使用非查询调用运行插件
command.CommandText = SQL.ToString（）;
command.ExecuteNonQuery（）; / *现在我们要作出MYSQL第二个电话来获取新的索引
看重它的主键创建。这是利用标量称为所以它将
返回SQL语句的值。我们将其转换为以后使用一个int。* /
command.CommandText =选择LAST_INSERT_ID（）;;
ID = Convert.ToInt32（command.ExecuteScalar（））; //提交事务。
器transaction.commit（）;
}
赶上（异常前）
{
的Debug.WriteLine（ex.Message）; 尝试
{
//试图回滚事务。
transaction.Rollback（）;
}
抓住
{
//什么也不做在这里;交易不活跃。
}
}
}
Ok after much reading and trying I still cant seem to get this to work:
OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email) VALUES ('rusty@msn.com'); SELECT LAST_INSERT_ID();", cn);
cmd.ExecuteNonQuery();
using (OdbcDataReader reader = cmd.ExecuteReader())
{
string theUserId = String.Format("{0}", reader.GetString(0));
Label10.Text = theUserId;
Tables:
User
--------
UserID (auto increment, pk)
Email
Running in debug mode I get errors on this line,
using (OdbcDataReader reader = cmd.ExecuteReader())
and,
cmd.ExecuteNonQuery();
Mysql is saying its a syntax error on this line SELECT LAST_INSERT_ID();", cn); but from what ive read this is legal.
Exact Error:
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.5.9]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 'SELECT LAST_INSERT_ID()' at line 1
EDIT: Justins method:
using (OdbcConnection connection = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=root; Password=commando;"))
{
// ODBC command and transaction objects
OdbcCommand command = new OdbcCommand();
OdbcTransaction transaction = null;
// tell the command to use our connection
command.Connection = connection;
try
{
// open the connection
connection.Open();
// start the transaction
transaction = connection.BeginTransaction();
// Assign transaction object for a pending local transaction.
command.Connection = connection;
command.Transaction = transaction;
// TODO: Build a SQL INSERT statement
OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email, FirstName, SecondName, DOB, Location, Aboutme, username, password) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "', '" + TextBox8.Text + "')", connection);
// run the insert using a non query call
command.CommandText = cmd.ToString();
command.ExecuteNonQuery();
/* now we want to make a second call to MYSQL to get the new index
value it created for the primary key. This is called using scalar so it will
return the value of the SQL statement. We convert that to an int for later use.*/
command.CommandText = "select last_insert_id();";
id = Convert.ToInt32(command.ExecuteScalar());
// the name id doesnt not exist in the current context
// Commit the transaction.
transaction.Commit();
}
catch (Exception ex)
{
Label10.Text = ": " + ex.Message;
try
{
// Attempt to roll back the transaction.
transaction.Rollback();
}
catch
{
// Do nothing here; transaction is not active.
}
}
}
解决方案
Clarification, the mySQL .net ODBC driver doesn't allow multiple commands to be run like you are describing. You have to make two separate calls and wrap them in a transaction.
// open a new connection using a default connection string I have defined elsewhere
using( OdbcConnection connection = new OdbcConnection( s_connectionString ) )
{
// ODBC command and transaction objects
OdbcCommand command = new OdbcCommand();
OdbcTransaction transaction = null;
// tell the command to use our connection
command.Connection = connection;
try
{
// open the connection
connection.Open();
// start the transaction
transaction = connection.BeginTransaction();
// Assign transaction object for a pending local transaction.
command.Connection = connection;
command.Transaction = transaction;
// TODO: Build a SQL INSERT statement
StringBuilder SQL = new StringBuilder();
// run the insert using a non query call
command.CommandText = SQL.ToString();
command.ExecuteNonQuery();
/* now we want to make a second call to MYSQL to get the new index
value it created for the primary key. This is called using scalar so it will
return the value of the SQL statement. We convert that to an int for later use.*/
command.CommandText = "select last_insert_id();";
id = Convert.ToInt32( command.ExecuteScalar() );
// Commit the transaction.
transaction.Commit();
}
catch( Exception ex )
{
Debug.WriteLine( ex.Message );
try
{
// Attempt to roll back the transaction.
transaction.Rollback();
}
catch
{
// Do nothing here; transaction is not active.
}
}
}
这篇关于MySQL和插入最后的ID遗骸问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
