How to Get the Auto-Increment Values after Insert Statement i n vb.net 2010

Question

I am new in vb.net and MySQL database, i am to far from what i am practicing but suddenly i'm stack in getting the value of the current data of the attribute from my table which is auto increment after inserting my query. Here is my code:

Imports MySql.Data.MySqlClient

Try
    Dim dbcon As New MySqlConnection
    Public dbcom As New MySqlCommand
    Public sql As String
    Dim theID As String ' this is the variable where the auto increment ID will be stored.
        dbcon.ConnectionString = "server=localhost; uid=root; pwd=; database=test;"
        dbcon.Open()
        sql = "insert into tableThis values('" & Name & "');" 'this will produced an auto increment value
        dbcom = New MySqlCommand(sql, dbcon)
        dbcom.ExecuteNonQuery()
        MsgBox("Added!Your ID IS: ", & theID) ' the auto increment ID will be display
    Catch ex As Exception
        MsgBox("Error!")
        Exit Sub
End Try

** my table is:

-------------------------------------
--         tableThis             ----
-------------------------------------
-- id   -- INTEGER (AUTO_INCREMENT) -
-- name -- CHAR                     -
-------------------------------------

Thanks! Sorry for my bad English.

score 0 · Accepted Answer · edited May 23 '17 at 12:22

The result of the last auto increment field in your connection could be obtained using

SELECT LAST_INSERT_ID()

To add this part to your current code you need

Using dbcon = new MySqlConnection("server=localhost; uid=root; pwd=; database=test;")
Using dbcom = New MySqlCommand("insert into tableThis values(@name);SELECT LAST_INSERT_ID();", dbCon)
     dbcon.Open()
     dbcom.Parameters.AddWithValue("@name", Name )
     Dim theID = Convert.ToInt32(dbcom.ExecuteScalar())
     MsgBox("Added!Your ID IS: " & theID.ToString)     
End Using
End Using

I have changed other things here:

First of all, you should always use a parameterized query and not string concatenations (read about Sql Injection to understand why)
Second, I have used the Using Statement around disposable objects to correctly dispose the connection and the command.
Third, I have passed the two commands together separating them with a semicolon and retrieved the resulting autoincrement using ExecuteScalar. Notice also that the autoincrement column is of type integer and not a string

How to Get the Auto-Increment Values after Insert Statement i n vb.net 2010

1 Answers1