The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Run the INSERT and retrieve LAST_INSERT_ID() immediately on the same open MySQL connection. In Connector/NET, the usual pattern is to append SELECT LAST_INSERT_ID() and read the single value with ExecuteScalarAsync(). The connection matters because MySQL stores the generated key per client session.
Recommended Connector/NET pattern
This example inserts a parent row and converts the returned value to a 64-bit integer:
As an Amazon Associate I earn from qualifying purchases.
using var connection = new MySqlConnection(connectionString);
await connection.OpenAsync();
using var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO parent (name) VALUES (@name);
SELECT LAST_INSERT_ID();";
command.Parameters.AddWithValue("@name", name);
var id = Convert.ToInt64(await command.ExecuteScalarAsync());id is the generated value from the AUTO_INCREMENT column of the successful insert. Use that variable when creating related rows.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallPassing the parent ID to a second INSERT
Keep both operations on the same connection. A transaction makes the pair atomic, so a failure in the child insert can roll back the parent insert.
#1 Best Overall
await using var connection = new MySqlConnection(connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
try
{
await using var parent = connection.CreateCommand();
parent.Transaction = transaction;
parent.CommandText = @"
INSERT INTO parent (name) VALUES (@name);
SELECT LAST_INSERT_ID();";
parent.Parameters.AddWithValue("@name", name);
var parentId = Convert.ToInt64(await parent.ExecuteScalarAsync());
await using var child = connection.CreateCommand();
child.Transaction = transaction;
child.CommandText = @"
INSERT INTO child (parent_id, description)
VALUES (@parentId, @description);";
child.Parameters.AddWithValue("@parentId", parentId);
child.Parameters.AddWithValue("@description", description);
await child.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}Read the ID before issuing unrelated statements on that connection. Do not close the connection and open another one to call LAST_INSERT_ID(); the value is session-specific.
When multiple statements are disabled
Some Connector/NET configurations or providers do not permit semicolon-separated statements in one command. Execute two commands instead, still using the same open connection:
- Execute
INSERT INTO parent ...withExecuteNonQueryAsync(). - Immediately execute
SELECT LAST_INSERT_ID()and read it withExecuteScalarAsync().
Use the same transaction when the following inserts must succeed or fail together. Provider behavior is configuration-specific; a two-command sequence is the practical fallback.
What LAST_INSERT_ID() returns
| Situation | Result |
|---|---|
One successful insert into an AUTO_INCREMENT column |
The generated key for that insert |
| Successful multi-row insert | The first automatically generated key, not a list of every key |
| Insert fails | No newly generated key; handle the exception |
| Insert affects no rows | No newly generated key is produced; check the command outcome rather than treating the returned value as a new row ID |
No AUTO_INCREMENT value is generated |
The MySQL C API documents a return value of zero for that case |
The value is affected only by statements on the current client connection. This is why retrieving it immediately is safer than relying on a later command.
Checking errors and affected rows
- Catch database exceptions from the insert and do not use an ID from a failed operation.
- If your SQL can legitimately insert zero rows, inspect the affected-row result and branch accordingly.
- Use parameters, as in the examples, rather than concatenating names or descriptions into SQL.
- Convert the scalar to the type that matches your key, commonly
longfor a MySQL integer key.
Common mistakes
Opening a second connection
LAST_INSERT_ID() on another connection refers to that other session, not the insert that just ran.
Waiting until after other work
Retrieve the value directly after the insert, before unrelated statements or error-handling paths can alter the session state.
Rank #4
Expecting all IDs from a bulk insert
The function returns only the first generated ID for a multi-row insert. If every generated key is required, insert rows individually or use an application design that records the keys explicitly.
Recommended Free Tools
Assuming every provider accepts combined SQL
Connector and ODBC settings differ. Separate the insert and select when a combined command is rejected, and keep both commands on the same connection.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Short answer
Open one MySQL connection, execute the AUTO_INCREMENT insert, immediately run SELECT LAST_INSERT_ID(), and read the scalar result. Store it in a variable and pass it as a parameter to subsequent inserts; use a transaction when those operations are one unit of work.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
