27

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"}
Jim Fell
  • 13,750
  • 36
  • 127
  • 202
Elie
  • 13,693
  • 23
  • 74
  • 128

5 Answers5

47
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
petra
  • 2,642
  • 2
  • 20
  • 12
  • 6
    *LastInsertedId is not thread safe.* If another thread is inserting things, the LastInsertedId will return the last inserted id on the connection to the db. So if several threads do it (or even separate processes with the same user to the db), it will be faulty. – Ted Sep 17 '13 at 13:32
  • Awesome.. I even did not think about that MysqlData would be providing it. great. – Sami Mar 11 '14 at 10:29
  • 1
    It might be that cmd.LastInsertedId is not thread safe.....but I assume the ID is retrieved in the same one ExecuteNonQuery call, not two query calls as you have to do with SELECT LAST_INSERT_ID(). Is it really possible for another call from another thread to get "in between" the inserted row and the retrieving of the ID. I guess the server do this task and therefore do immediately. Is LAST_INSERT_ID thread safe? – MrCalvin Jan 27 '16 at 22:18
  • 1
    The LastInsertedId is returned immediately with the cmd.executequery, so I really don't think another thread can get in-between this. But I would also like to know to be 100% sure. But at least we know that it is safe from other connections insertions – humudu Jul 23 '18 at 13:44
  • 1
    Seems like it is safe. See: https://stackoverflow.com/a/30959730/711863 – mikeDOTexe Dec 18 '19 at 04:32
24

[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);
Michael Haren
  • 105,752
  • 40
  • 168
  • 205
  • I'd try the query without it. Are the records actually being inserted? – Michael Haren Jan 02 '09 at 03:10
  • OK, how about "SELECT last_insert_id();" at the end? – Michael Haren Jan 02 '09 at 03:26
  • Michael: last_insert_id() is not thread safe. Is another thread is inserting things, the last_insert_id will return the last inserted id on the connection to the db. So if several threads do it (or even separate processes with the same user to the db), it will be faulty. – Ted Sep 17 '13 at 13:31
  • 3
    Just wanted to note that Ted is correct if the individual threads are using a shared connection. If each thread is establishing it's own connection it should function just fine, since mysql handles it on a per-connection basis. [last_insert_id() documentation](http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id) – Pizano Oct 10 '13 at 00:19
3

Use LastInsertedId.

View my suggestion with example here: http://livshitz.wordpress.com/2011/10/28/returning-last-inserted-id-in-c-using-mysql-db-provider/

Elya Livshitz
  • 404
  • 1
  • 6
  • 9
0

It bothers me to see anybody taking a Date and storing it in a database as a String. Why not have the column type reflect reality?

I'm also surprised to see a SQL query being built up using string concatenation. I'm a Java developer, and I don't know C# at all, but I'd wonder if there wasn't a binding mechanism along the lines of java.sql.PreparedStatement somewhere in the library? It's recommended for guarding against SQL injection attacks. Another benefit is possible performance benefits, because the SQL can be parsed, verified, cached once, and reused.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • OP might be using a date column in the DB--we can't see that part. I agree that you should use parameterized queries, though. Thankfully it doesn't look like you are inserting any text so strictly speaking you're probably safe on this one (though performance could be better). – Michael Haren Jan 02 '09 at 02:57
  • column is actually a date, but if I try inserting the date object directly, I had issues with it resetting the date to a set of 0s. I'm new to C#, but I agree with you, there likely is a version of PreparedStatement for C#, and I will change that. – Elie Jan 02 '09 at 02:59
  • If the column is indeed a date, what's that "toString" call with the formatting pattern doing in the code? – duffymo Jan 02 '09 at 13:52
  • Also regarding the binding: Odbc has binding mechanisms for mysql see https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-programming-net-csharp.html and http://stackoverflow.com/questions/18082840/how-to-bind-parameters-via-odbc-c – sdjuan Oct 26 '16 at 19:37
0

Actually, the ExecuteScalar method returns the first column of the first row of the DataSet being returned. In your case, you're only doing an Insert, you're not actually querying any data. You need to query the scope_identity() after you're insert (that's the syntax for SQL Server) and then you'll have your answer. See here:

Linkage

EDIT: As Michael Haren pointed out, you mentioned in your tag you're using MySql, use last_insert_id(); instead of scope_identity();

BFree
  • 102,548
  • 21
  • 159
  • 201