Membuat Autonumber di VB.Net dengan Database MySQL

Selamat Siang, kali ini saya akan berbagi tentang cara membuat autonumber di Ms. Visual Basic .Net dengan database MySQL. Berikut langkah-langkahnya :

1. Buatlah database MySQL dengan struktur :

2. Kemudian buat form di VB.Net seperti berikut ini :








3. Ketikkan Script seperti berikut ini :
Imports MySql.Data.MySqlClient

Public Class Form1
    Dim strconn As String = "server=localhost;uid=root;pwd=;database=dbjual;"
    Dim cmd As MySqlCommand
    Dim reader As MySqlDataReader
    Dim kotak As String
    Dim con As New MySqlConnection(strconn)
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If con.State = ConnectionState.Open Then con.Close()
        con.Open()
        autonumber()
    End Sub
    Private Sub autonumber()
        Try
            cmd = New MySqlCommand("SELECT kode_barang FROM barang order by kode_barang desc", con)
            reader = cmd.ExecuteReader
            If reader.Read Then
                kotak = reader.Item("kode_barang")
                kotak = kotak + 1
                TextBox1.Text = kotak
            Else
                TextBox1.Text = "1"
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub
End Class
4. Untuk kasus yang lebih rumit dapat menggunakan script berikut ini :

Imports MySql.Data.MySqlClient
Public Class Form1
    Public strKoneksi As String = "server=localhost;uid=root;pwd=;database=dbjual"
    Dim cmd As MySqlCommand
    Dim reader As MySqlDataReader
    Dim con As New MySqlConnection(strKoneksi)
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If con.State = ConnectionState.Open Then con.Close()
        con.Open()
        autonumber()
    End Sub
    Private Sub autonumber()
        Try
            cmd = New MySqlCommand("SELECT CONCAT( 'Y' ,LPAD((RIGHT(MAX(kode_barang),3)+1),3,'0')) as id_kd FROM barang", con)
            reader = cmd.ExecuteReader
            If reader.Read Then
                TextBox1.Text = reader.Item("id_kd")
            Else
                TextBox1.Text = "Y001"
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub
End Class

5. Semoga bermanfaat