how to get last insert id (vb + mysql)

[venom80]

Hi guys,

I'm a newbie in Visual Basic and I need some assistance.

I have 2 tables in mysql:

1. stats (stats_id,statsno,statsName)
2.stats_det (stats_det_ID,stats_id,stat_DateAdd,stat_UserAdd,stat_DateModif,stat_UserModif)

I have a form named status and from here I want to add a new record in the first table (stats).
I've created the insert statement in the first table which is running well, but I don't know how to get the last_insert_id from stats table and insert it in the second table stats_det.

My code is below :

Private Sub checkDuplicates()
        'the field must contain some values
        If txtStatNo.Text = "" Then
            'field StatNo is empty
            tsResult.Text = "Field StatNo must have a value"
            tsResult.ForeColor = Color.Red
            ErProvider.SetError(txtStatNo, "Please fill the Status No field")
            ErProvider.SetIconPadding(txtStatNo, 5)
        Else
            'field statNo is not empty
            SQL = "select stats_ID,statsNo,statsName from stats where " _
                    & " statsNo= '" & txtStatNo.Text & "'"

            Try
                conn.Open()
                Try
                    myComm.Connection = conn
                    myComm.CommandText = SQL

                    myReader = myComm.ExecuteReader
                    If myReader.HasRows = 0 Then
                        'is not duplicate
                        'we have to insert the values
                        insertValues_stats()
                    Else
                        'is duplicate
                        'we have to post back he information
                        tsResult.Text = "This record exists in our database. Duplicate."
                        tsResult.ForeColor = Color.Red
                        ErProvider.SetError(txtStatNo, "Duplicate Record")
                        ErProvider.SetIconPadding(txtStatNo, 5)
                        txtStatNo.Focus()
                    End If
                Catch myerror As MySqlException
                    MsgBox("Error loading datas " & myerror.Message)
                End Try
            Catch myerror As MySqlException
                MsgBox("Error connecting to the server" & myerror.Message)
            Finally
                If conn.State <> ConnectionState.Closed Then conn.Close()
            End Try
        End If
    End Sub

Private Sub insertValues_stats()

        SQL = "insert into stats(statsNo,statsName) " _
            & " values(?statsNo,?statsName); " _
        & "select last_insert_id() from stats"

        With myComm
            .Connection = conn
            .CommandText = SQL
            .Parameters.AddWithValue("?StatsNo", txtStatNo.Text)
            .Parameters.AddWithValue("?StatsName", txtStatName.Text)
        End With

        Try
            conn.Close()
            conn.Open()
            myComm.ExecuteNonQuery()
            insertValues_statsDet()
        Catch myerror As MySqlException
            MsgBox("Error updating the database :" & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
    End Sub

Private Sub insertValues_statsDet()

        SQL = "insert into stats_det(stats_ID,stats_dateadd, " _
        & "stats_useradd,stats_datemodif,stats_usermodif) " _
        & " values(last_insert_id(),now(),?userID,now(),?userID)"

        With myComm
            .Connection = conn
            .CommandText = SQL
            .Parameters.AddWithValue("?UserID", UserID)

        End With
        Try

            conn.Close()
            conn.Open()
            myComm.ExecuteNonQuery()

        Catch myerror As MySqlException
            MsgBox("Error updating the stats_def table :" & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try

    End Sub

On the btnSave I placed the code :
Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        checkDuplicates()
    End Sub

Please tell me where I've made the mistake.

Thank you.

[venom80]

I've just modified the Sub and now it's working :

Private Sub insertValues_statsDet()
        Dim LastID As New MySqlCommand
        Dim ID As Integer

        LastID.CommandText = "select last_insert_id()"
        LastID.Connection = conn
        Lid = LastID.ExecuteScalar()
        txtStatsID.Text = Lid
        ID = Lid.ToString

        SQL = "insert into stats_det(stats_ID,stats_dateadd, " _
        & "stats_useradd,stats_datemodif,stats_usermodif) " _
        & " values(?ID,now(),?userID,now(),?userID)"

        With myComm
            .Connection = conn
            .CommandText = SQL
            .Parameters.Add("?ID", Lid)
            .Parameters.Add("?UserID", My.Settings.UserID)
        End With
        Try

            conn.Close()
            conn.Open()
            myComm.ExecuteNonQuery()
            clearText()
        Catch myerror As MySqlException
            MsgBox("Error updating the stats_def table :" & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try

    End Sub