Imports MySql.Data.MySqlClient
Imports MySql.Data
Public Class Form1

    Dim mycmd As New MySqlCommand
    Dim myconnection As New DTConnection
    Dim objreader As MySqlDataReader
    Dim cmdstr As String

    'Dim sort As String = "alter table tbla drop id; alter table tbla auto_increment=1; alter table tbla add id int UNSIGNED NOT NULL auto_increment primary key first;"

    Private Sub BTINSERT_Click(sender As Object, e As EventArgs) Handles BTINSERT.Click
        mycmd.Connection = myconnection.open
        mycmd.CommandText = "insert into tbla(fname,username,password) values('" & TNAMES.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "')"
        mycmd.ExecuteNonQuery()
        myconnection.close()
        MsgBox("Data added !!", MsgBoxStyle.Information, "Notice..")
        Dim isi As ListViewItem
        LISTSHOW.Items.Clear()
        mycmd.Connection = myconnection.open
        mycmd.CommandText = "select * from tbla"
        objreader = mycmd.ExecuteReader
        While objreader.Read
            isi = LISTSHOW.Items.Add(objreader.Item("id").ToString)
            isi.SubItems.Add(objreader.Item("fname").ToString)
            isi.SubItems.Add(objreader.Item("username").ToString)
            isi.SubItems.Add(objreader.Item("password").ToString)
        End While
        TextBox3.Text = ""
        myconnection.close()
    End Sub

    Private Sub BTUPDATE_Click(sender As Object, e As EventArgs) Handles BTUPDATE.Click
        mycmd.Connection = myconnection.open
        mycmd.CommandText = "update tbla set fname='" & TNAMES.Text & "',username='" & TextBox1.Text & "',password='" & TextBox2.Text & "' where id='" & TID.Text & "'"
        mycmd.ExecuteNonQuery()
        myconnection.close()
        MsgBox("Data changed !!", MsgBoxStyle.Information, "Notice..")
        Dim isi As ListViewItem
        LISTSHOW.Items.Clear()
        mycmd.Connection = myconnection.open
        mycmd.CommandText = "select * from tbla"
        objreader = mycmd.ExecuteReader
        While objreader.Read
            isi = LISTSHOW.Items.Add(objreader.Item("id").ToString)
            isi.SubItems.Add(objreader.Item("fname").ToString)
            isi.SubItems.Add(objreader.Item("username").ToString)
            isi.SubItems.Add(objreader.Item("password").ToString)
        End While
        TextBox3.Text = ""
        myconnection.close()
    End Sub

    Private Sub BTDELETE_Click(sender As Object, e As EventArgs) Handles BTDELETE.Click
        mycmd.Connection = myconnection.open
        mycmd.CommandText = "delete from tbla where id='" & TID.Text & "'; alter table tbla drop id; alter table tbla auto_increment=1; alter table tbla add id int UNSIGNED NOT NULL auto_increment primary key first;"
        'mycmd.CommandText = "truncate tbla"
        mycmd.ExecuteNonQuery()
        myconnection.close()
        MsgBox("Data deleted !!", MsgBoxStyle.Information, "Notice..")
        Dim isi As ListViewItem
        LISTSHOW.Items.Clear()
        mycmd.Connection = myconnection.open
        mycmd.CommandText = "select * from tbla"
        objreader = mycmd.ExecuteReader
        While objreader.Read
            isi = LISTSHOW.Items.Add(objreader.Item("id").ToString)
            isi.SubItems.Add(objreader.Item("fname").ToString)
            isi.SubItems.Add(objreader.Item("username").ToString)
            isi.SubItems.Add(objreader.Item("password").ToString)
        End While
        TextBox3.Text = ""
        myconnection.close()
    End Sub

    'Private Sub BTSHOW_Click(sender As Object, e As EventArgs) Handles BTSHOW.Click
    '    Dim isi As ListViewItem
    '    LISTSHOW.Items.Clear()
    '    mycmd.Connection = myconnection.open
    '    mycmd.CommandText = "select * from tbla"
    '    objreader = mycmd.ExecuteReader
    '    While objreader.Read
    '        isi = LISTSHOW.Items.Add(objreader.Item("id").ToString)
    '        isi.SubItems.Add(objreader.Item("fname").ToString)
    '        isi.SubItems.Add(objreader.Item("username").ToString)
    '        isi.SubItems.Add(objreader.Item("password").ToString)
    '    End While
    '    myconnection.close()
    'End Sub

    Private Sub LISTSHOW_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LISTSHOW.SelectedIndexChanged
        Dim index As Integer
        If LISTSHOW.SelectedItems.Count = 0 Then Exit Sub
        With LISTSHOW
            index = .SelectedIndices(0)
            TID.Text = .Items(index).Text
            TNAMES.Text = .Items(index).SubItems(1).Text
            TextBox1.Text = .Items(index).SubItems(2).Text
            TextBox2.Text = .Items(index).SubItems(3).Text
        End With
        'Update sub
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim isi As ListViewItem
        LISTSHOW.Items.Clear()
        mycmd.Connection = myconnection.open
        mycmd.CommandText = "select * from tbla where fname LIKE '%" & TextBox3.Text & "%' or username LIKE '%" & TextBox3.Text & "%' or password LIKE '%" & TextBox3.Text & "%'"
        objreader = mycmd.ExecuteReader
        While objreader.Read
            isi = LISTSHOW.Items.Add(objreader.Item("id").ToString)
            isi.SubItems.Add(objreader.Item("fname").ToString)
            isi.SubItems.Add(objreader.Item("username").ToString)
            isi.SubItems.Add(objreader.Item("password").ToString)
        End While
        myconnection.close()
    End Sub

    Private Sub TextBox3_TextChanged(sender As Object, e As EventArgs) Handles TextBox3.TextChanged
        Form1_Load(Me, New System.EventArgs)
        'myconnection.open()
        'cmdstr = "select * from tbla where fname LIKE '%" & TextBox3.Text & "%';"

        'mycmd = MySqlCommand(cmdstr, myconnection)
        'objreader = mycmd.ExecuteReader
        'LISTSHOW.Items.Clear()
        'Dim isi As ListViewItem

        'mycmd.Connection = myconnection.open
        'mycmd.CommandText = "select * from tbla"
        'objreader = mycmd.ExecuteReader
        'While objreader.Read
        '    isi = LISTSHOW.Items.Add(objreader.Item("id").ToString)
        '    isi.SubItems.Add(objreader.Item("fname").ToString)
        '    isi.SubItems.Add(objreader.Item("username").ToString)
        '    isi.SubItems.Add(objreader.Item("password").ToString)
        'End While
        'myconnection.close()
    End Sub

    Private Sub LISTSHOW_DoubleClick(sender As Object, e As EventArgs) Handles LISTSHOW.DoubleClick
        Form2.Show()
        Dim index As Integer
        If LISTSHOW.SelectedItems.Count = 0 Then Exit Sub
        With LISTSHOW
            index = .SelectedIndices(0)
            Form2.TextBox1.Text = .Items(index).SubItems(1).Text
            Form2.TextBox2.Text = .Items(index).SubItems(2).Text
            Form2.TextBox3.Text = .Items(index).SubItems(3).Text
        End With
    End Sub
End Class