How to return the last id of inserted query?

Question

I'm trying to insert a record in the local database and after it I want to get the id 'cause I need to re-use this id for insert another record in a different table. Now this is my code:

 ...
 Using dbCon As MySqlConnection = establishConnection()

        Try

            dbCon.Open() 

            Dim MysqlCommand = New MySqlCommand("INSERT INTO users (first_name, last_name, email, 
                                phone_number, address, city, notes, id_roles, data) 
                                OUTPUT INSERTED.ID
                                VALUES(@first_namep, @last_namep, @emailp, @phone_numberp, 
                                @addressp, @cityp, @notesp, @id_rolesp, @data)", dbCon)

            MysqlCommand.Parameters.AddWithValue("@first_namep", name)
            MysqlCommand.Parameters.AddWithValue("@last_namep", last_name)
            MysqlCommand.Parameters.AddWithValue("@emailp", email)
            MysqlCommand.Parameters.AddWithValue("@phone_numberp", phone_number)
            MysqlCommand.Parameters.AddWithValue("@addressp", address)
            MysqlCommand.Parameters.AddWithValue("@cityp", city)
            MysqlCommand.Parameters.AddWithValue("@notesp", notes)
            MysqlCommand.Parameters.AddWithValue("@id_rolesp", customer_id)
            MysqlCommand.Parameters.AddWithValue("@data", 0)

            MysqlCommand.ExecuteNonQuery()  

            Dim id As Integer = MysqlCommand.ExecuteScalar   

            MysqlCommand.Parameters.Clear()


            MysqlCommand.CommandText = "INSERT INTO user_settings (id, username, password, salt)
                                       VALUES(@idp, @usernamep, @passwordp, @saltp)"

            MysqlCommand.Parameters.AddWithValue("@idp", id)
            MysqlCommand.Parameters.AddWithValue("@usernamep", username)
            MysqlCommand.Parameters.AddWithValue("@passwordp", password_hash)
            MysqlCommand.Parameters.AddWithValue("@saltp", password_salt)

            MysqlCommand.ExecuteNonQuery()

        Catch myerror As MySqlException

            MessageBox.Show(myerror.Message)

        Finally

            dbCon.Dispose()

        End Try
    End Using

End Sub

I open the connection with dbCon.Open() and after it I perform a parametized query passing all controls values. Now the id on the table is auto_increment so I've write in the query:

OUTPUT INSERTED.ID

but when this line is going to executed: MysqlCommand.ExecuteNonQuery()

I get this error:

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 'OUTPUT INSERTED.ID VALUES('Jason', 'Ruos', '' at line 3

What am I doing wrong? I hope also that my .Clear() is a good practice for re-use the MySqlCommand.

I think the OUTPUT keyword isn't supported by MYSQL (I could be wrong). Your query looks like a Microsoft SQL statement. See [Get the new record primary key ID from mysql insert query?](http://stackoverflow.com/q/17112852/719186) — LarsTech, Dec 10 '15 at 15:57
But SELECT LAST_INSERT_ID(); must require to execute in another query, isn't possible return the inserted id in the same query? — , Dec 10 '15 at 16:00

score 0 · Answer 1 · answered Dec 10 '15 at 16:02

0

I found the solution, just use:

Dim id As Integer = MysqlCommand.LastInsertedId

answered Dec 10 '15 at 16:02

score 0 · Answer 2 · answered Dec 10 '15 at 18:35

you are almost there. Instead of using MysqlCommand.ExecuteNonQuery() , use MysqlCommand.ExecuteScalar()

also I notice you add all your parameters etc , call MysqlCommand.ExecuteNonQuery() then you try to use the executescalar() again which is saying run this stored procedure again. get rid of the first MysqlCommand.ExecuteNonQuery() and just use the one you are assigning to an ID

How to return the last id of inserted query?

2 Answers2