Relasi sql dengan vb 2008

RELASI DATABASE MYSQL DENGAN

VISUAL BASIC.NET 2008

A.   MYSQL

Membuat Database Penjualan

Bangulah tabel Berikut

Tabel Barang

kd_barang	nama_barang	harga
K001	Pulpen	1000
K002	Binder	2000
K003	Pensil	3000

Tabel Konsumen

kd_konsumen	nama	alamat
KS001	Fahmi	Padang
KS002	Anto	Medan
KS003	Doni	Medan
KS004	Sulis	Aceh
KS005	Ridho	Aceh

Tabel Pembelian

kd_faktor	kd_barang	kd_konsumen	jumlah	total
F1	K001	KS001	2	2000
F2	K002	KS002	1	1000
F3	K003	KS003	2	2000
F4	K004	KS004	1	6000

Langkah-langkah pembuatan database dengan menggunakan MYSQL adalah :

1.      Aktifkan terlebih dahulu MySQL-D/MySQL-D-NT

2.      Buka  dengan  computer “C:\apache\mysql\bin”,dan buka file mysql

3.      Setelah file mysql dibuka akan tampil program mysql.

4.      Buatlah database dengan perintah :

mysql> create database Penjualan;

5.      Pilih database yang digunakan :

mysql> use Penjualan;

6.      Buat Table dengan perintah :

·         mysql> create table barang

    -> (kd_barang varchar(15),

    -> nama_barang varchar(25),

    -> harga integer(25));

·         mysql> create table kosumen

    -> (kd_konsumen varchar(15),

    -> nama varchar(25),

    -> alamat varchar(30));

·         mysql> create table pembelian

    -> (kd_faktor varchar(15),

    -> kd_konsumen varchar(15),

    -> kd_barang varchar(15),

    -> jumlah integer(10),

    -> total integer(30));

7.      Masukan data ke tabel :

·         barang

mysql> insert into barang values

    -> ('K001','Pulpen','1000'),

    -> ('K002','Binder','2000'),

    -> ('K003','Pensil','3000');

·         konsumen

mysql> insert into kosumen  values

    -> ('KS001','Fahmi','Padang'),

    -> ('KS002','Anto','Medan'),

    -> ('KS003','Tary','Aceh'),

    -> ('KS004','Doni Gunawan','Medan'),

    -> ('KS005','Ridho','Aceh');

·         pembelian

mysql> insert into pembelian  values

    -> ('F1','K001','KS001','2','2000');

8.      Tampilkan record yang ada :

mysql> select * from barang;

            mysql> select * from kosumen;

mysql> select * from pembelian;

mysql> select * from pembelian;

B.     Visual Basic .NET 2008

1.      Instalkan Connector ODBC untuk VB.NET

2.      Buatlah project baru dan design form seperti gambar dibawah ini

3.      Aturan Properti seperti di tabel berikut ini :

Object	Properti	Nilai properti
Label1	Text	Kode Faktor
Label2	Text	Kode barang
Label3	Text	Nama Barang
Label4	Text	Harga
Label5	Text	Jumlah
Label6	Text	Total harga
Label7	Text	Kode konsumen
Label8	Text	Nama
TextBox1	Name	Kode_faktor
TextBox2	Name	Kode_barang
TextBox3	Name	Nama_barang
TextBox4	Name	Harga
TextBox5	Name	Jlh
TextBox6	Name	T_harga
ComboBox1	Name	Kd_konsumen
ComboBox2	Name
Button1	Name	Hapus
	Text	Hapus
Button2	Name	Simpan
	Text	Simpan
Button3	Name	Keluar
	Text	Keluar
ListView1	Name	Liv

Hasil Form setelah dibuat :

4.      Tambahkan Reference kedalam projetct

Pilih projetct lalu klik Add Reference

Maka akan tampil kotak di bawah ini

5.      Pilih tab Browser dan cari file mysql data.dll dengan alamat “C:\Program Files\Mysql Connector Net 1.0.7\bin\.NET 1.1\” dan klik tombol Ok

6.     Listing Program

Imports MySql.Data.MySqlClient

 Public Class Form1

    Public db As New    MySql.Data.MySqlClient.MySqlConnection

    Public SQL As String

    Public CMD As New MySqlCommand

    Public rs As MySqlDataReader

Private Sub Form1_Load(ByVal sender As System.Object,  ByVal e As System.EventArgs) Handles MyBase.Load

SQL = "server=localhost;uid=root;database=penjualan"

        Try

            db.ConnectionString = SQL

            db.Open()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

        Call isitabel()

        kdbarang()

        kodekonsumen()

        buattabel()

    End Sub

  Sub kdbarang()

        SQL = "select * from barang"

        CMD = New MySqlCommand(SQL, db)

        rs = CMD.ExecuteReader

        Try

            While rs.Read

                kode_barang.Items.Add(rs!kd_barang)

            End While

        Catch ex As Exception

        End Try

        rs.Close()

    End Sub

    Private Sub kode_barang_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles kode_barang.SelectedIndexChanged

        Dim nb As String = ""

        Dim hr As String = ""

        SQL = "select * from barang where kd_barang='" & kode_barang.Text & "'"

        CMD = New MySqlCommand(SQL, db)

        rs = CMD.ExecuteReader

        Try

            rs.Read()

            nb = rs!nama_barang

            hr = rs!harga

        Catch ex As Exception

        End Try

        rs.Close()

        nama_barang.Text = nb

        harga.Text = hr

    End Sub

    Sub kodekonsumen()

        SQL = "select * from kosumen"

        CMD = New MySqlCommand(SQL, db)

        rs = CMD.ExecuteReader

        Try

            While rs.Read

                kd_konsumen.Items.Add(rs!kd_konsumen)

            End While

        Catch ex As Exception

        End Try

        rs.Close()

    End Sub

   

    Private Sub kd_konsumen_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles kd_konsumen.SelectedIndexChanged

        Dim nb As String = ""

        SQL = "select * from kosumen where kd_konsumen='" & kd_konsumen.Text & "'"

        CMD = New MySqlCommand(SQL, db)

        rs = CMD.ExecuteReader

        Try

            rs.Read()

            nb = rs!nama

        Catch ex As Exception

        End Try

        rs.Close()

        nama.Text = nb

    End Sub

Private Sub jlh_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles jlh.TextChanged

        t_harga.Text = Val(jlh.Text) * Val(harga.Text)

    End Sub

     Sub buattabel()

        liv.Columns.Add("Kode Faktor", 100, HorizontalAlignment.Left)

        liv.Columns.Add("Kode Barang", 100, HorizontalAlignment.Left)

        liv.Columns.Add("Kode Konsumen", 100, HorizontalAlignment.Left)

        liv.Columns.Add("Nama Barang", 100, HorizontalAlignment.Left)

        liv.Columns.Add("Harga", 100, HorizontalAlignment.Left)

        liv.Columns.Add("jumlah Beli", 80, HorizontalAlignment.Left)

        liv.Columns.Add("Kode Konsumen", 100, HorizontalAlignment.Left)

        liv.Columns.Add("Nama ", 100, HorizontalAlignment.Left)

        liv.GridLines = True

        liv.FullRowSelect = True

        liv.View = View.Details

    End Sub

    Sub isitabel()

        liv.Items.Clear()

        SQL = "select * from pembelian"

        CMD = New MySqlCommand(SQL, db)

        rs = CMD.ExecuteReader

        Try

            While rs.Read

                Dim lst As New ListViewItem

                lst.Text = rs("kd_faktor")

                lst.SubItems.Add(rs("kd_barang"))

                lst.SubItems.Add(rs("kd_konsumen"))

                lst.SubItems.Add(nama_barang.Text)

                lst.SubItems.Add(harga.Text)

                lst.SubItems.Add(rs("jumlah"))

                lst.SubItems.Add(rs("total"))

                lst.SubItems.Add(nama.Text)

                liv.Items.Add(lst)

            End While

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        rs.Close()

  

     End Sub

    Private Sub simpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles simpan.Click

        SQL = "insert into pembelian values('"& kode_faktor.Text & _

           "','" & kode_barang.Text & "','" & kd_konsumen.Text & _

           "','" & jlh.Text & "','" & t_harga.Text & "')"

        Try

            CMD = New MySqlCommand(SQL, db)

            CMD.ExecuteNonQuery()

        Catch ex As Exception

        End Try

        isitabel()

        kode_faktor.Clear()

        kode_barang.Text = ""

        nama_barang.Text = ""

        harga.Text = ""

        kd_konsumen.Text = ""

        jlh.Text = ""

        t_harga.Text = ""

        nama.Text = ""

End Sub

       

Private Sub hapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles hapus.Click

        SQL = "delete from pembelian where kd_faktor ='" & kode_faktor.Text & "'"

        Try

            CMD = New MySqlCommand(SQL, db)

            CMD.ExecuteNonQuery()

            MsgBox("Data Telah Dihapus?")

        Catch ex As Exception

        End Try

        isitabel()

        kode_faktor.Clear()

    End Sub

           

    Private Sub keluar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles keluar.Click

        End

    End Sub

    Sub showdb()

        rs.Read()

        kode_faktor.Text = rs("kd_faktor")

        kode_barang.Text = rs("kd-barang")

        kd_konsumen.Text = rs("kd_konsumen")

        jlh.Text = rs("jumlah")

        t_harga.Text = rs("total")

    End Sub

    Private Sub kode_faktor_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles kode_faktor.KeyPress

        If Asc(e.KeyChar) = 13 Then

            If kode_faktor.Text = "" Then

                MsgBox("Nama Kosong")

            Else

                SQL = "select * fROM pembelian where kd_faktor='" & kode_faktor.Text & "'"

                CMD = New MySqlCommand(SQL, db)

                rs = CMD.ExecuteReader

                Try

                    Call showdb()

                  

                Catch ex As Exception

                    MessageBox.Show(ex.Message)

                End Try

                rs.Close()

            End If

        End If

    End Sub

End Clas

7.     Hasil  Program