How to get Insert ID after inserting data to MySQL

Question

I have a Mysql data table with the following details:

ID (int format) [AUTO_INCREMENT] 
date (varchar) 
ip (varchar)
activate(varchar) 
cdkey(varchar)

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Using SQLConnection As New MySqlConnection(ConnectionString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "INSERT INTO software (`date`, `ip`, `activate`, `cdkey`) values (@date,@ip,@activate,@cdkey)"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@ip", Label1.Text)
                    .Parameters.AddWithValue("@date", TextBox2.Text)
                    .Parameters.AddWithValue("@activate", "Yes")
                    .Parameters.AddWithValue("@cdkey", textbox4.Text)



                End With
                Try
                    SQLConnection.Open()
                    sqlCommand.ExecuteNonQuery()
                    iReturn = True
                    MsgBox("Display Last Insert ID here")

                Catch ex As MySqlException
                    MsgBox(ex.Message.ToString)
                    iReturn = False
                Catch ex As Exception
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using
        Return
    End Sub

it's working except I'm trying to get the id number from the database after this data is inserted. How do I do that?

Those questions are confusing and the answers aren't clear. This here is clear question and an an amazing answer. — Rick Cresci, Aug 09 '18 at 15:01

score 5 · Accepted Answer · answered Aug 08 '18 at 19:35

5

You should change your query to

  .CommandText = "INSERT INTO software (`date`, `ip`, `activate`, `cdkey`) 
                  values (@date,@ip,@activate,@cdkey);
                  SELECT LAST_INSERT_ID();"

Then instead of ExecuteNonQuery you call ExecuteScalar and get back the value from the LAST_INSERT_ID()

   Dim newID As Integer = sqlCommand.ExecuteScalar()

answered Aug 08 '18 at 19:35

Steve

213,761
22
232
286

I used two execuescalar , one to insdert and one for the query. Removing that fixed the issue. Thank you so much for your help – Rick Cresci Aug 08 '18 at 20:10
How can I mark this as answer? – Rick Cresci Aug 08 '18 at 20:11

How to get Insert ID after inserting data to MySQL

1 Answers1