Program chat dengan MySQL dan VB. 2008

Salam hangat sahabat blogger dimanapun berada , admin ingin berbagi sedikit ilmu ne,, ibarat pepatah yang berbunyi sampaikanlah olehmu walaupun satu ayat. Opss malah ceramah pula ne. ok tanpa basa basi admin akan membagi cara chat menggunakan MySQL, ikuti langkah – langkahnya jangan sampai ada yang error ya hehehehe
Caranya :




1. Menambahkan reference MySQL
      Agar database MySQL bisa digunakan di VB. NET 2008 kita harus menambahkan referancenya, caranya sebagai berikut :
a. Buat project baru windows application visual basic.NET
b. Klik menu project > add reference
c. Klik tab browse untuk menambah komponen msql.data
d. Cari lokasinya di “ C:\program files\MySQL Connector.Net 1.0.7\bin\Net 1.1\ Mysql.data
e. Kemudian klik OK


2. Merancang from chatting
Tampilan from untuk chatting :

Komponen yang digunakan antara lain :
a. 1 buah GroupBox
b. 6 buah label
c. 1 buah timer
d. 3 buah listbox
e. 2 buah button
f. 3 buah textbox


Tampilan from User

Komponen yang digunakan antara lain :
a. 1 buah label
b. 1 buah textbox




3. Code program
Berikut code program from chatting:

Imports MySql.Data.MySqlClient
Public Class Form1
    Public sql As String = ""
    Public DB As New MySql.Data.MySqlClient.MySqlConnection
    Public CMD As MySqlCommand
    Public RS As MySqlDataReader
    Public Sub OpenDB()
        Dim SqlCon As String
        SqlCon = "server=192.168.10.1;uid=root;pwd=;database=chat"
        Try
            DB.ConnectionString = SqlCon
            DB.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Function ubahkedtik(ByVal x As String)
        Dim a As Integer
        Dim b As Integer
        Dim c As Integer
        x = x
        a = Strings.Left(x, 2) * 3600
        b = Strings.Mid(x, 3, 2) * 60
        c = Strings.Right(x, 2)
        x = a + b + c
        Return x
    End Function
    Function ambilwaktu()
        Dim i As String = ""
        sql = "select (current_time()) as waktu"
        CMD = New MySqlCommand(sql, DB)
        RS = CMD.ExecuteReader
        RS.Read()
        i = RS(0)
        RS.Close()
        i = Strings.Left(i, 2) & Strings.Mid(i, 4, 2) & Strings.Right(i, 2)
        Return i
    End Function
    Function ambiltgl()
        Dim i As String = ""
        sql = "select (current_date()) as tgl"
        CMD = New MySqlCommand(sql, DB)
        RS = CMD.ExecuteReader
        RS.Read()
        i = RS(0)
        RS.Close()
        Return i
    End Function
    Sub opp2()
        '/// Menampilkan Isi Kotak Pesan
        Listpesan.Text = ""
        If terpilih.Text = "" Or user.Text = "" Then
            sql = "Select*from pesan where pesan!='xx' and penerima='' order by id desc limit 10"
        Else
            sql = "Select*from pesan where (user='" & user.Text & "' or user='" & terpilih.Text & "') and (penerima='" & user.Text & "' or penerima='" & terpilih.Text & "') order by id desc limit 10"
        End If
        CMD = New MySqlCommand(Sql, DB)
        RS = CMD.ExecuteReader
        Try
            While RS.Read
                Listpesan.Text = Listpesan.Text & "[" & RS("user") & "] : " & RS("pesan") & vbCr & vbLf & "- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - " & vbCr & vbLf
            End While
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        RS.Close()
        '////////////////////////////////

        '/// Setting pesan informasi
        If user.Text = "" Or terpilih.Text = "" Then
            info.Text = "Menampilkan Pesan Untuk Umum Yang Dapat dilihat Oleh Semua Pengguna "
        Else
            info.Text = "Menampilkan Pesan ''" & user.Text & "'' Dengan ''" & terpilih.Text & "''"
        End If
        '///////////////////////////


        'UPdate time
        Dim id As Integer
        If user.Text = "" Then
        Else
            'Memperbarui Time pada user yang sedang online
            sql = "select*from pesan where user='" & user.Text & "' order by id desc limit 1"
            CMD = New MySqlCommand(sql, DB)
            RS = CMD.ExecuteReader
            RS.Read()
            id = RS("id")
            RS.Close()

            sql = "update pesan set date='" & ambiltgl() & "',time='" & ubahkedtik(ambilwaktu()) & "' where id='" & id & "'"
            CMD = New MySqlCommand(sql, DB)
            CMD.ExecuteNonQuery()
            '//////////////////////////////////////////////


            'MEnampilkan user Online yang memiliki waktu dari 7 detik yang lalu hingga sekarang
            Dim ee As Double
            ee = ubahkedtik(ambilwaktu()) - 5 'Waktu Skrang kurang 7 Detik
            sql = "select*from pesan where id!='" & id & "' and date='" & ambiltgl() & "' and time >" & ee & " group by user"
            CMD = New MySqlCommand(sql, DB)
            RS = CMD.ExecuteReader
            list.Items.Clear()
            list.Items.Add("+++ PUBLIC +++")
            While RS.Read
                If user.Text = RS("user") Then
                Else
                    list.Items.Add(RS("user"))
                End If
            End While
            RS.Close()
            '////////////////////////////////////////////////////////////////////////////


            'MEnampilkan user offline
            ee = ee - 3
            sql = "select user from pesan where date='" & ambiltgl() & "' and time <" & ee & " group by user"
            CMD = New MySqlCommand(sql, DB)
            RS = CMD.ExecuteReader
            list2.Items.Clear()
            list2.Items.Add("+++ PUBLIC +++")
            While RS.Read
                If user.Text = RS("user") Then
                Else
                    list2.Items.Add(RS("user"))
                End If
            End While
            RS.Close()
            '////////////////////////////////////////////////////////////////////////////


            'Update Log inbox terakhir
            sql = "select*from pesan where penerima='" & user.Text & "' order by id desc limit 15"
            CMD = New MySqlCommand(sql, DB)
            RS = CMD.ExecuteReader
            loginbox.Items.Clear()
            While RS.Read
                loginbox.Items.Add("New Inbox {+" & RS("user") & "+} ")
            End While
            RS.Close()
            '/////////////////////////
        End If

    End Sub

    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        End
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call OpenDB()
        Call opp2()
        Timer1.Start()
        pesan.Focus()
        user.Enabled = False
        terpilih.Enabled = False
        If user.Text = "" Then
            user.Text = Nothing
            Form2.Show()
            Me.Hide()
        End If
    End Sub
    Sub insert()
        If pesan.Text = "" Then
            MsgBox("Msg Empty !!")
        Else
            Dim Sql As String = ""
            Sql = "Insert into pesan(user,pesan,penerima,date,time) values('" & user.Text & "','" & pesan.Text & "','" & terpilih.Text & "','" & ambiltgl() & "','" & ubahkedtik(ambilwaktu()) & "')"
            Try
                CMD = New MySqlCommand(Sql, DB)
                CMD.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            pesan.Text = ""
            Call opp2()
        End If
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Call opp2()
    End Sub

    Private Sub list_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        opp2()
        If list.SelectedItem = "+++ PUBLIC +++" Then
            terpilih.Text = ""
        Else
            terpilih.Text = list.SelectedItem
        End If
    End Sub

    Private Sub blogout_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles blogout.Click
        user.Text = ""
        terpilih.Text = ""
        Form2.Show()
        Me.Hide()
    End Sub

    Private Sub pesan_KeyPress1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles pesan.KeyPress
        If Asc(e.KeyChar) = 13 Then
            insert()
        End If
    End Sub

    Private Sub kirim_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles kirim.Click
        insert()
    End Sub

    Private Sub terpilih_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles terpilih.KeyPress
        If Asc(e.KeyChar) = 13 And terpilih.Text = "admin" Then
            Dim cc As String = MsgBox("Hapus Semua Pesan pada database ??", MsgBoxStyle.OkCancel)
            If cc = vbOK Then
                sql = "delete from pesan where pesan!='xx'"
                CMD = New MySqlCommand(sql, DB)
                CMD.ExecuteNonQuery()
            End If
            terpilih.Text = Nothing
            list2.Items.Clear()
            list.Items.Clear()
        ElseIf Asc(e.KeyChar) = 13 And terpilih.Text = "clear" Then
            Dim cc As String = MsgBox("Hapus Semua data di dabase ??", MsgBoxStyle.OkCancel)
            If cc = vbOK Then
                user.Text = Nothing
                sql = "delete from pesan"
                CMD = New MySqlCommand(sql, DB)
                CMD.ExecuteNonQuery()
            End If
            terpilih.Text = Nothing
        End If
    End Sub

    Private Sub list2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles list2.SelectedIndexChanged
        If list2.SelectedItem = "+++ PUBLIC +++" Then
            terpilih.Text = ""
        Else
            terpilih.Text = list2.SelectedItem
        End If
        opp2()
    End Sub

    Private Sub list_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles list.SelectedIndexChanged
        If list.SelectedItem = "+++ PUBLIC +++" Then
            terpilih.Text = ""
        Else
            terpilih.Text = list.SelectedItem
        End If
        opp2()
    End Sub

    Private Sub Label4_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Label4.DoubleClick
        terpilih.Enabled = True
    End Sub
End Class




Berikut code program from User :

Imports MySql.Data.MySqlClient
Public Class Form2
    Dim sql As String = ""
    Public DB As New MySql.Data.MySqlClient.MySqlConnection
    Public CMD As MySqlCommand
    Public RS As MySqlDataReader
    Public Sub OpenDB()
        Dim SqlCon As String
        SqlCon = "server=192.168.10.1;uid=root;pwd=;database=chat"
        Try
            DB.ConnectionString = SqlCon
            DB.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        End
    End Sub
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        OpenDB()
    End Sub
    Function ambilwaktu()
        Dim i As String = ""
        sql = "select (current_time()) as waktu"
        CMD = New MySqlCommand(sql, DB)
        RS = CMD.ExecuteReader
        RS.Read()
        i = RS(0)
        RS.Close()
        i = Strings.Left(i, 2) & Strings.Mid(i, 4, 2) & Strings.Right(i, 2)
        Return i
    End Function
    Function ambiltgl()
        Dim i As String = ""
        sql = "select (current_date()) as date"
        CMD = New MySqlCommand(sql, DB)
        RS = CMD.ExecuteReader
        RS.Read()
        i = RS(0)
        RS.Close()
        Return i
    End Function
    Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
        If Asc(e.KeyChar) = 13 Then
            If TextBox1.Text = "" Then
            Else
                Dim waktu As String
                Dim tgl As String
                tgl = ambiltgl()
                waktu = ambilwaktu()
                sql = "select*from pesan where user='" & TextBox1.Text & "'"
                CMD = New MySqlCommand(sql, DB)
                RS = CMD.ExecuteReader
                RS.Read()
                If RS.HasRows Then
                    RS.Close()
                    Dim ee As Double
                    ee = Form1.ubahkedtik(ambilwaktu()) - 5
                    sql = "select*from pesan where user='" & TextBox1.Text & "' and  date='" & tgl & "' order by id desc limit 1"
                    CMD = New MySqlCommand(sql, DB)
                    RS = CMD.ExecuteReader
                    RS.Read()
                    Dim xx As Double
                    xx = Form1.ubahkedtik(waktu) - RS!time
                    If xx < 7 Then
                        MsgBox("User Sedang Di gunakan")
                        TextBox1.Text = ""
                        TextBox1.Focus()
                    Else
                        Form1.user.Text = TextBox1.Text
                        Form1.Show()
                        Me.Hide()
                    End If
                    RS.Close()
                Else
                    RS.Close()
             sql = "insert into pesan(pesan,user) values('xx','" & TextBox1.Text & "')"
                    CMD = New MySqlCommand(sql, DB)
                    CMD.ExecuteNonQuery()
                    Form1.user.Text = TextBox1.Text
                    Form1.Show()
                    Me.Hide()
                End If
                RS.Close()
            End If
        End If
    End Sub
End Class