We help IT Professionals succeed at work.

Require Primary Key ID from row just inserted into Table using Maria DB\MySQL

G F
G F asked
on
Hi!

I'm creating a Maria DB project (MySQL) using C#, and I have a need to get the primary key Id from a row I just insert into a table.

I've looked at several solutions on the internet and I'm not having any success.  They are either unreliable (documented errors) or do not work with MySql/Maria.

This is my last effort:


 MySqlCommand cmd = new MySqlCommand("INSERT INTO bbb.traveldocuments (CustomerID,TDNumber,TDDateCreated,TDMoveStart,TDMoveEnd,TDJobNumber,TDPermitType,TDNotes)" + "VALUES(@CustomerID,@TDNumber,@TDDateCreated,@TDMoveStart,@TDMoveEnd,@TDJobNumber,@TDPermitType,@TDNotes); SELECT SCOPE_IDENTITY() ", con);

.
.
.
cmd.Parameters.AddWithValue("@TDNotes", txtBox_TD_Notes.Text);

cmd.ExecuteNonQuery();
int insertedID = Convert.ToInt32(cmd.ExecuteScalar());


When I run the above code I get:

MySql.Data.MySqlClient.MySqlException: 'FUNCTION SCOPE_IDENTITY does not exist'

Are there any suggestions I can try?

Thank you in advance.

G
Comment
Watch Question

View Solution Only

WebDev
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hi,

SCOPE_IDENTITY() is for MS SQL, not for MySQL. For MySQL, you'd need to use the LAST_INSERT_ID function:

SELECT LAST_INSERT_ID();

Open in new window

G FGeneral Manager

Author

Commented:
Thank you for the quick reply! It worked great!

G
Chris StanyonWebDev
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You're welcome :)