Return the Auto Incremented ID number of the current inserted row in VB.Net from mysql database

Question

I am using VB.net to write a recipe to a mysql database. Once i have inserted the recipe, I need to return the Auto Incremented id number for that row. (Auto Incremented id Column name is RecipeID)

I have tried different variations of this question: Return Last ID (IDENTITY) On Insert row VB.NET MySQL

but keep getting unhandled exception errors.

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick

    Dim mytimestamp As String = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "server=localhost;userid=root;password=Splash123;database=awsbdata"

    Dim reader As MySqlDataReader
    Dim jobnumber As String = EthernetIPforCLXCom1.Read("BC_BatchCode")
    Dim recipename As String = EthernetIPforCLXCom1.Read("Recipe_library[0].Name")
    Dim recipeid As String = EthernetIPforCLXCom1.Read("Recipe_library[0].Number")
    Dim perator As String = EthernetIPforCLXCom1.Read("BC_CurrentUser")
    Dim slurrypress As Decimal = EthernetIPforCLXCom1.Read("PT1343_Imp")
    Dim g1airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1022_Imp")
    Dim g2airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1023_Imp")
    Dim g3airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1024_Imp")
    Dim g4airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1025_Imp")
    Dim airflowcfm As Decimal = EthernetIPforCLXCom1.Read("FIT1042_Imp")
    Dim concentration As Decimal = EthernetIPforCLXCom1.Read("MC_AbrasiveConcentation")
    Dim tanklevel As Decimal = EthernetIPforCLXCom1.Read("LT1345_Imp")

    Try
        MysqlConn.Open()
        Dim query As String
        query = "insert into awsbdata.batchdata (RunTime,JobNumber,RecipeID,RecipeName,Operator,Concentration,G1AirPressure,G2AirPressure,G3AirPressure,G4AirPressure,AirFlow_CFM,SlurryPressure,TankLevel) values ('" & mytimestamp & "','" & jobnumber & "','" & recipeid & "','" & recipename & "','" & perator & "','" & concentration & "','" & g1airpressure & "','" & g2airpressure & "','" & g3airpressure & "','" & g4airpressure & "','" & airflowcfm & "','" & slurrypress & "','" & tanklevel & "')"
        COMMAND = New MySqlCommand(query, MysqlConn)
        reader = COMMAND.ExecuteReader

        MysqlConn.Close()

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        MysqlConn.Dispose()

    End Try
End Sub

I either get an unhandled exception or connection not open error

The code that i have posted here is before I started getting the following unhandled exception error. — MDS, Jun 18 '19 at 19:47
System.InvalidOperationException: Connection must be valid and open. — MDS, Jun 18 '19 at 19:49
Do NOT concatenate strings to build your query. You will be WIDE-OPEN to SQL-Injection. Parameterize your query. MANY Examples, just search on vb.net and sql-injection. — DRapp, Jun 18 '19 at 20:05
An `INSERT` command is executed with `.ExecuteNonQuery()`. Then you can read the last `AUTO_INCREMENT` value with `COMMAND.CommandText = "SELECT Last_insert_id()" Dim lastID = COMMAND.ExecuteScalar()`. It reports the last ID assigned to the first record in an `INSERT` command (if you insert more than one record, the ID is the `AUTO_INCREMENT` value of the first record inserted) — Jimi, Jun 18 '19 at 21:43

score 0 · Answer 1 · answered Jun 19 '19 at 11:39

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick

    Dim mytimestamp As String = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "server=localhost;userid=root;password=Splash123;database=awsbdata"

    Dim reader As MySqlDataReader
    Dim jobnumber As String = EthernetIPforCLXCom1.Read("BC_BatchCode")
    Dim recipename As String = EthernetIPforCLXCom1.Read("Recipe_library[0].Name")
    Dim recipeid As String = EthernetIPforCLXCom1.Read("Recipe_library[0].Number")
    Dim perator As String = EthernetIPforCLXCom1.Read("BC_CurrentUser")
    Dim slurrypress As Decimal = EthernetIPforCLXCom1.Read("PT1343_Imp")
    Dim g1airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1022_Imp")
    Dim g2airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1023_Imp")
    Dim g3airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1024_Imp")
    Dim g4airpressure As Decimal = EthernetIPforCLXCom1.Read("PIT1025_Imp")
    Dim airflowcfm As Decimal = EthernetIPforCLXCom1.Read("FIT1042_Imp")
    Dim concentration As Decimal = EthernetIPforCLXCom1.Read("MC_AbrasiveConcentation")
    Dim tanklevel As Decimal = EthernetIPforCLXCom1.Read("LT1345_Imp")

    Try
        MysqlConn.Open()
        Dim query As String
        query = "insert into awsbdata.batchdata (RunTime,JobNumber,RecipeID,RecipeName,Operator,Concentration,G1AirPressure,G2AirPressure,G3AirPressure,G4AirPressure,AirFlow_CFM,SlurryPressure,TankLevel) values ('" & mytimestamp & "','" & jobnumber & "','" & recipeid & "','" & recipename & "','" & perator & "','" & concentration & "','" & g1airpressure & "','" & g2airpressure & "','" & g3airpressure & "','" & g4airpressure & "','" & airflowcfm & "','" & slurrypress & "','" & tanklevel & "')"
        COMMAND = New MySqlCommand(query, MysqlConn)
        reader = COMMAND.ExecuteReader

        MysqlConn.Close()

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        MysqlConn.Dispose()

    End Try

    Try
        MysqlConn.Open()
        COMMAND.CommandText = "SELECT Last_insert_id()"
        Dim lastID = COMMAND.ExecuteScalar()
        MsgBox(lastID)

        MysqlConn.Close()

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        MysqlConn.Dispose()

    End Try

End Sub

btw... I am very new to programming in vb.net, so if there is a better way to set up the code, please let me know....thanks — MDS, Jun 19 '19 at 12:22

Return the Auto Incremented ID number of the current inserted row in VB.Net from mysql database

1 Answers1