how to get last insert id (vb + mysql)

Expand Post »

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 :

VB.NET Syntax (Toggle Plain Text)
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
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.

Last edited by peter_budo; Dec 31st, 2009 at 10:59 am. Reason: Keep it Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

Similar Threads

insert values to mysql database in JSP
VB-MySQL Check if exist then insert in VB.NET
insert csv file into mysql through php in PHP
Problem with INSERT command into MySQL in PHP
mock credit card with mysql & filewriter INSERT into mysql in Java

venom80

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Dec 2009

Permalink

Jan 2nd, 2010

Re: how to get last insert id (vb + mysql)

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

VB.NET Syntax (Toggle Plain Text)
 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
 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

venom80

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Dec 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: urgent need-webbrowser

Next Thread in VB.NET Forum Timeline: Escaping a Loop While It's Running

DaniWeb Message

Cancel Changes

User Name
Password