Imports MySql.Data.MySqlClient
Public Class Form1
    Dim con As MySqlConnection = New MySqlConnection
    Dim da As New MySqlDataAdapter
    Dim listitem As ListViewItem
    Dim sql As String
    Dim start As Integer = 0
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ServerString As String = "Server=localhost;User=root;Password=;Database=test"
        con.ConnectionString = ServerString

        ComboBox1.Items.Add("PersonName")
        ComboBox1.Items.Add("City")
        ComboBox1.Items.Add("Phone")
        ComboBox1.Text = "PersonName"

        Dim Header As ColumnHeader
        ListView1.Width = 405
        Header = New ColumnHeader
        Header.Text = "No"
        Header.TextAlign = HorizontalAlignment.Center
        Header.Width = 50
        ListView1.Columns.Add(Header)

        Header = New ColumnHeader
        Header.Text = "Name"
        Header.TextAlign = HorizontalAlignment.Center
        Header.Width = 150
        ListView1.Columns.Add(Header)

        Header = New ColumnHeader
        Header.Text = "City"
        Header.TextAlign = HorizontalAlignment.Center
        Header.Width = 100
        ListView1.Columns.Add(Header)

        Header = New ColumnHeader
        Header.Text = "Phone"
        Header.TextAlign = HorizontalAlignment.Center
        Header.Width = 100
        ListView1.Columns.Add(Header)
        Display()
        start = 1
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then
            MsgBox("Please Fill in the blank")
        Else
            Try
                con.Open()
                sql = "insert into Table1(PersonName,City,Phone) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"
                Dim cmd As MySqlCommand = New MySqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sql
                cmd.Connection = con
                cmd.ExecuteNonQuery()
                con.Close()
                Display()
                TextBox1.Text = ""
                TextBox2.Text = ""
                TextBox3.Text = ""
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If TextBox1.Text <> "" Then
            Try
                con.Open()
                sql = "update Table1 set City='" & TextBox2.Text & "',Phone='" & TextBox3.Text & "' where PersonName='" & TextBox1.Text & "'"
                Dim cmd As MySqlCommand = New MySqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sql
                cmd.Connection = con
                cmd.ExecuteNonQuery()
                con.Close()
                Display()
                TextBox1.Text = ""
                TextBox2.Text = ""
                TextBox3.Text = ""
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Else
            MsgBox("Please Fill Name")
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If TextBox1.Text <> "" Then
            Try
                con.Open()
                sql = "delete from Table1 where PersonName='" & TextBox1.Text & "'"
                Dim cmd As MySqlCommand = New MySqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sql
                cmd.Connection = con
                cmd.ExecuteNonQuery()
                con.Close()
                Display()
                TextBox1.Text = ""
                TextBox2.Text = ""
                TextBox3.Text = ""
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Else
            MsgBox("Please Fill Name")
        End If
    End Sub

    Private Sub Display()
        ListView1.Items.Clear()
        Dim MyTable As New DataTable
        Try
            con.Open()
            sql = "select *from Table1"
            da.SelectCommand = New MySqlCommand(sql, con)
            da.Fill(MyTable)
            For i = 0 To MyTable.Rows.Count - 1
                listitem = ListView1.Items.Add((ListView1.Items.Count).ToString() + 1)
                listitem.SubItems.Add(MyTable.Rows(i)("PersonName"))
                listitem.SubItems.Add(MyTable.Rows(i)("City"))
                listitem.SubItems.Add(MyTable.Rows(i)("Phone"))
            Next
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Display2()
        ListView1.Items.Clear()
        Dim MyTable As New DataTable
        Try
            con.Open()
            sql = "select *from Table1 where " & ComboBox1.Text & " like '" & TextBox4.Text & "%'"
            da.SelectCommand = New MySqlCommand(sql, con)
            da.Fill(MyTable)
            For i = 0 To MyTable.Rows.Count - 1
                listitem = ListView1.Items.Add((ListView1.Items.Count).ToString() + 1)
                listitem.SubItems.Add(MyTable.Rows(i)("PersonName"))
                listitem.SubItems.Add(MyTable.Rows(i)("City"))
                listitem.SubItems.Add(MyTable.Rows(i)("Phone"))
            Next
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
        If start = 1 Then
            If TextBox4.Text <> "" Then
                Display2()
            Else
                Display()
            End If
        End If
    End Sub

    Private Sub ComboBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.TextChanged
        If start = 1 Then
            Display2()
        End If
    End Sub
End Class