Cara Koneksi Database MySQL Dengan Visual Basic 6.0

Setelah sekian lama vakum dari dunia blog karena banyaknya aktifitas yang saya lakukan  (padahal cuma main game online) akhirnya bisa update artikel lagi di awal tahun 2013 ini (bulan 3 masih awal kan?) :D .., okay sambil merapikan blog yang mulai usang karna lama gak di urus, pada kesempatan kali ini saya akan berbagi tutor tentang cara koneksi database MySQL dengan Visual Basic 6.0. Sebelum kita mulai, pertama kalian harus install dulu driver MySQL ODBC untuk menghubungkan database kedalam program nantinya. Kalau belum punya drivernya silahkan download dulu filenya di sini.

Nah jika drivernya sudah terinstall sekarang kita mulai tutornya, pertama buat dulu database MySQLnya dengan Xampp di PHPMyadmin dengan nama latihansqldb.

Nama table = latihansqldb

Field = 3

Nama	Tipe Data	Keterangan
Nrp	Varchar(10)	Primary Key
Nama	Varchar(30)
jurusan	ENUM(‘Sistem Informasi’,’Teknik Informatika’,’Komputer Akuntansi’,’Manajemen Informatika’)	Default : Sistem Informasi

Atau anda bisa langsung import table databasenya dari folder File Database yang sudah saya sediakan di file source code nya.

Setelah membuat database selesai, desain form seperti dibawah ini :

 

Komponen yang digunakan dalam form ini dan konfigurasinya adalah :

Komponen	Property	Nilai
Form	Name	LATSQL
	Caption	Latihan Database MySQL
Text1	Name	Txtnrp
Text2	Name	Txtnama
Text3	Name	Txtjurusan
MsFlexfrid	Name	Gridmhs

 

Jika sudah selesai desain formnya,, sekarang kita buat modul untuk setting koneksi ke database MySQL yg telah kita buat sebelumnya, untuk membuka modul caranya pilih menu Project > Add Modul > Open. Setelah muncul jendela koding modul, ketikkan koding dibawah ini :

========================================================================

Option Explicit

Public KonekDb As New ADODB.Connection

Public Rs_mhs As New ADODB.Recordset

Public SQLubah, SQLsimpan, SQLhapus As String

Sub BukaDatabase()

    Dim strSS, strPort, strDB, strUID, strPWD As String  

            strSS = "localhost"

            strPort = "3306"

            strDB = "latihansqldb"

            strUID = "root"

            strPWD = ""

    Set KonekDb = New ADODB.Connection

    KonekDb.CursorLocation = adUseClient 

KonekDb.ConnectionString = "" _

        & "DRIVER={MYSQL ODBC 3.51 Driver};" _

        & "SERVER=" & strSS & ";" _

        & "DATABASE=" & strDB & ";" _

        & "UID=" & strUID & ";" _

        & "PWD=" & strPWD & ";" _

        & "OPTION="           

    On Error Resume Next

    If KonekDb.State = adStateOpen Then

        KonekDb.Close

        Set KonekDb = New ADODB.Connection

        KonekDb.Open

    Else

        KonekDb.Open

    End If

    If Err.Number <> 0 Then

        MsgBox "GAGAL KONEKSI SERVER", vbOKOnly, "Kesalahan"

    End If

End Sub

========================================================================

Setelah membuat modul koneksi, sekarang masuk kedalam form yang kita buat tadi dan ketikkan koding berikut di form load, tombol simpan, edit, hapus, keluar dan flexgrid.

========================================================================

Private Sub Form_Load()

Call BukaDatabase

Call TampilGridData

cmbjurusan.Clear

cmbjurusan.AddItem "Sistem Informasi"

cmbjurusan.AddItem "Teknik Informatika"

cmbjurusan.AddItem "Komputer Akuntansi"

cmbjurusan.AddItem "Manajemen Informatika"

End Sub
========================================================================

Sub kosong()

txtnrp.Text = ""

txtnama.Text = ""

cmbjurusan.Text = ""

End Sub

========================================================================

Sub Aktifgridmhs()

    With gridmhs

        .Cols = 3

       

        .RowHeightMin = 300

        .Col = 0

        .Row = 0

        .Text = "NRP"

        .CellFontBold = True

        .ColWidth(0) = 1700

        .AllowUserResizing = flexResizeColumns

        .CellAlignment = flexAlignCenterCenter

       

        .Col = 1

        .Row = 0

        .Text = "NAMA"

        .CellFontBold = True

        .ColWidth(1) = 2500

        .AllowUserResizing = flexResizeColumns

        .CellAlignment = flexAlignCenterCenter

       

        .Col = 2

        .Row = 0

        .Text = "JURUSAN"

        .CellFontBold = True

        .ColWidth(2) = 2650

        .AllowUserResizing = flexResizeColumns

        .CellAlignment = flexAlignCenterCenter

       

    End With

End Sub

======================================================================== 

Sub TampilGridData()

    Dim Baris As Integer

    gridmhs.Clear

    Call Aktifgridmhs

    gridmhs.Rows = 2

    Baris = 0 

    Set Rs_mhs = New ADODB.Recordset

    Rs_mhs.Open "SELECT * FROM mhs", KonekDb, adOpenDynamic, adLockOptimistic

  

        With Rs_mhs

        .MoveFirst

        Do While Not .EOF

            On Error Resume Next

            Baris = Baris + 1

            gridmhs.Rows = Baris + 1

            gridmhs.TextMatrix(Baris, 0) = !nrp

            gridmhs.TextMatrix(Baris, 1) = !nama

            gridmhs.TextMatrix(Baris, 2) = !jurusan

        .MoveNext

        Loop

        End With

   End Sub

======================================================================== 

Private Sub cmdsimpan_Click()

Set Rs_mhs = New ADODB.Recordset

            Rs_mhs.Open "SELECT * FROM mhs WHERE nrp='" & txtnrp.Text & "'", _

            KonekDb, adOpenDynamic, adLockBatchOptimistic

If Rs_mhs.BOF Then

                SQLsimpan = ""

                SQLsimpan = "INSERT INTO mhs (nrp,nama,jurusan)" _

                    & " VALUES('" & txtnrp.Text & "','" & txtnama.Text & "','" _

                    & cmbjurusan.Text & "')"

                KonekDb.Execute SQLsimpan, , adCmdText

                Call Form_Load

                MsgBox "DATA BARU TELAH TERSIMPAN", vbOKOnly + vbInformation, "INFO"

            Else

                MsgBox "NRP SUDAH ADA", vbOKOnly + vbInformation, "INFO"

            End If

Rs_mhs.Requery

Call kosong

Call TampilGridData

End Sub

======================================================================== 

Private Sub cmdedit_Click()

            SQLubah = "UPDATE mhs " _

                & " set nama='" & txtnama.Text & "', " _

                & " jurusan='" & cmbjurusan.Text & "' " _

                & " WHERE nrp='" & txtnrp.Text & "'"

            KonekDb.Execute SQLubah, , adCmdText

            Call kosong

            Call Form_Load

            MsgBox "DATA mhs TELAH DIUBAH", vbOKOnly + vbInformation, "Konfirmasi"

Rs_mhs.Requery

Call TampilGridData    

End Sub

======================================================================== 

Private Sub cmdhapus_Click()

hapus = MsgBox("yakin ingin menghapus data ini?", vbQuestion + vbYesNo, "Hapus")

If hapus = vbYes Then

        SQL = "DELETE FROM mhs WHERE nrp='" & txtnrp.Text & "'"

        KonekDb.Execute SQL, , adCmdText     

        Rs_mhs.Requery

        Call kosong

        Call TampilGridData

    Else

    Call kosong

End If

End Sub

======================================================================== 

Private Sub cmdkeluar_Click()

Unload Me

End Sub    
========================================================================          

Private Sub gridmhs_DblClick()

    GridBaris = gridmhs.Row

    Set Rs_mhs = New ADODB.Recordset

    Rs_mhs.Open " SELECT * FROM mhs WHERE nrp='" & gridmhs.TextMatrix(GridBaris, 0) & "' " _

            , KonekDb, adOpenDynamic, adLockOptimistic

    If Rs_mhs.BOF Then

        MsgBox "TABEL MASIH KOSONG", vbOKOnly + vbInformation, "Perhatian"

        Exit Sub

        Call kosong

    Else

        Rs_mhs.MoveFirst

        Do While Not Rs_mhs.EOF

            On Error Resume Next

            txtnrp.Text = Rs_mhs!nrp

            txtnama.Text = Rs_mhs!nama

            cmbjurusan.Text = Rs_mhs!jurusan

        Rs_mhs.MoveNext

        Loop

    End If

End Sub

======================================================================== 

Jika semua koding sudah diketikkan coba jalankan form yang telah diisi dengan koding diatas, jika berhasil, maka akan tampil seperti gambar dibawah ini :

Okay, sampai disini dulu tutor vb nya, semoga bermanfaat buat teman2 yang ingin belajar VB 6.0. ^^

Sampai jumpa lagi di pembahasan selanjutnya