Return Last ID (IDENTITY) On Insert row VB.NET MySQL

Question

Dim insert_coupon_query As String = ("INSERT INTO qa_discountcoupons (id, status_code) VALUES (AUTO_INCREMENT_ID, 5)")
                Dim cmd_query As New MySqlCommand(insert_coupon_query, objConn)
                Dim cmd_result As Integer = CInt(cmd_query.ExecuteScalar())

I want to return the AUTO_INCREMENT value of the current insert, and show in a msgbox.

Are you actually passing an ID as the query suggests? Or is it really an auto-increment? In MySQL you can select `LAST_INSERT_ID` to get the last auto-incremented ID in a transaction. — David, Mar 20 '12 at 16:50
No, THe id is generated by MySQL, i only pass the status code — John Nuñez, Mar 20 '12 at 16:57

score 14 · Accepted Answer · answered Mar 20 '12 at 17:07

14

You can use an double Query and use the function LAST_INSERT_ID() After run your first query to get the last current query:

Dim insert_coupon_query As String = ("INSERT INTO qa_discountcoupons (status_code) VALUES (5); SELECT LAST_INSERT_ID()")
                Dim cmd_query As New MySqlCommand(insert_coupon_query, objConn)
                Dim cmd_result As Integer = CInt(cmd_query.ExecuteScalar())

                MsgBox(cmd_result)

answered Mar 20 '12 at 17:07

blackriderws

833
2
9
14

Never thought we could use a double query in vb.net sql statement. It helps to get the auto-increment number from ID column. Thank you. – surpavan Nov 30 '16 at 18:27

score 2 · Answer 2 · answered Oct 02 '19 at 08:32

2

Bit late to the party but after

cmd_query.ExecuteScalar()
MsgBox(cmd_query.LastInsertedId)

Produces the last insert id.

answered Oct 02 '19 at 08:32

Ian Halligan

21
1

score 1 · Answer 3 · edited May 23 '17 at 11:46

1

You can fetch the AUTO_INCREMENT value for a table through a MySQL query and then show that in your MsgBox

edited May 23 '17 at 11:46

Community

1
1

answered Mar 20 '12 at 16:50

sikander

2,286
16
23

Return Last ID (IDENTITY) On Insert row VB.NET MySQL

3 Answers3

Linked

Related