使用C＃获取插入行的id [英] Get the id of inserted row using C#

问题描述

我有一个查询中插入行到一个表，其中有一个叫做ID字段，这是使用该列的AUTO_INCREMENT填充。我需要获得功能下位这个值，但是当我运行以下，它总是返回0即使实际值不为0：

 的MySqlCommand COMM = connect.CreateCommand（）;
comm.CommandText = insertInvoice;
comm.CommandText + =\\'+ invoiceDate.ToString（YYYY：MM：DD HH：MM：SS）+\\'+ bookFee +，+ adminFee +，+ totalFee +， +的customerID +）;
INT ID = Convert.ToInt32（comm.ExecuteScalar（））;
 

据我的理解，这应该返回ID列，但它只是每一次返回0。任何想法？

编辑：

当我运行：

 INSERT INTO发票（INVOICE_DATE，BOOK_FEE，ADMIN_FEE，TOTAL_FEE，CUSTOMER_ID）VALUES（'2009：01：01十点21分12秒，50，7，57，2134） ; LAST_INSERT_ID（）;
 

我得到：

  {你在你的SQL语法错误;检查对应于您的MySQL服务器版本使用附近的'LAST_INSERT_ID（）在1号线正确的语法手册}
 

解决方案

怎么样跑选择LAST_INSERT_ID（）; ？你插入后

 的MySqlCommand COMM = connect.CreateCommand（）;
comm.CommandText = insertInvoice;
comm.CommandText + =\\'+ invoiceDate.ToString（YYYY：MM：DD HH：MM：SS）+\\'
    + bookFee +，+ adminFee +，+ totalFee +，+的customerID +）;;
    +选择LAST_INSERT_ID（）;INT ID = Convert.ToInt32（comm.ExecuteScalar（））;
 

---

编辑：正如duffymo提到的，你真的会得到很好的使用参数化查询服务的这样的。

---

编辑：直到你切换到一个参数化的版本，你可能会找到的String.Format和平：

  comm.CommandText =的String.Format（{0}{1}，{2}，{3}，{4}，{5}）;选择LAST_INSERT_ID（ ）;，
  insertInvoice，invoiceDate.ToString（...），bookFee，adminFee，totalFee，的customerID）;
 

I have a query to insert a row into a table, which has a field called ID, which is populated using an AUTO_INCREMENT on the column. I need to get this value for the next bit of functionality, but when I run the following, it always returns 0 even though the actual value is not 0:

MySqlCommand comm = connect.CreateCommand();
comm.CommandText = insertInvoice;
comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', " + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ")";
int id = Convert.ToInt32(comm.ExecuteScalar());

According to my understanding, this should return the ID column, but it just returns 0 every time. Any ideas?

EDIT:

When I run:

"INSERT INTO INVOICE (INVOICE_DATE, BOOK_FEE, ADMIN_FEE, TOTAL_FEE, CUSTOMER_ID) VALUES ('2009:01:01 10:21:12', 50, 7, 57, 2134);last_insert_id();"

I get:

{"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 'last_insert_id()' at line 1"}

解决方案

[Edit: added "select" before references to last_insert_id()]

What about running "select last_insert_id();" after your insert?

MySqlCommand comm = connect.CreateCommand();
comm.CommandText = insertInvoice;
comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', "  
    + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ");";
    + "select last_insert_id();"

int id = Convert.ToInt32(comm.ExecuteScalar());

---

Edit: As duffymo mentioned, you really would be well served using parameterized queries like this.

---

Edit: Until you switch over to a parameterized version, you might find peace with string.Format:

comm.CommandText = string.Format("{0} '{1}', {2}, {3}, {4}, {5}); select last_insert_id();",
  insertInvoice, invoiceDate.ToString(...), bookFee, adminFee, totalFee, customerID);

这篇关于使用C＃获取插入行的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！