Cara Koneksi Database menggunakan vb 6

Setelah saya mempostingkan "Cara Download Video tanpa Software" sebelumnya, pada kesempatan kali ini sayaakan belajar berposting dan 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 dulufilenyadi sini.

Nah jika drivernya sudah terinstall sekarang kita mulai tutornya, pertama buat dulu database MySQLnya dengan Xampp di PHP Myadmin dengan nama latihan sqldb.

Nama table = latihansqldb

Field = 3

Nama	Tipe Data	Keterangan
Nrp	Varchar(10)	Primary Key
Nama	Varchar(30)
jurusan	ENUM(‘SistemInformasi’,’TeknikInformatika’,’KomputerAkuntansi’,’ManajemenInformatika’)	Default : SistemInformasi

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 digunakandalam form inidankonfigurasinyaadalah :

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 KonekDbAs New ADODB.Connection

Public Rs_mhsAs 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 "SistemInformasi"

cmbjurusan.AddItem "TeknikInformatika"

cmbjurusan.AddItem "KomputerAkuntansi"

cmbjurusan.AddItem "ManajemenInformatika"

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 BarisAs 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.ExecuteSQLsimpan, ,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.ExecuteSQLubah, ,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("yakininginmenghapus 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 Tampilan form yg kita jalankhn...