Get the id of inserted row using C#

Question

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"} 

Answer 1

MySqlCommand comm = connect.CreateCommand(); comm.CommandText = insertStatement;  // Set the insert statement comm.ExecuteNonQuery();              // Execute the command long id = comm.LastInsertedId;       // Get the ID of the inserted item 

Answer 2

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