VBNet Koneksi ke Ms Access Database

koneksi.vb


Imports Microsoft.VisualBasic

Imports MySql.Data.MySqlClient


Public Class koneksi
    Protected tblPengguna = New DataTable
    Protected SQL As String
    Protected Cn, m_conn As MySql.Data.MySqlClient.MySqlConnection
    Protected Cmd As MySql.Data.MySqlClient.MySqlCommand
    Protected Da As MySql.Data.MySqlClient.MySqlDataAdapter
    Protected Ds As DataSet
    Protected Dt As DataTable

    '    Public conn As New MySql.Data.MySqlClient.MySqlConnection
    '   Public CMD As New MySql.Data.MySqlClient.MySqlCommand
    '  Public DS As New DataSet
    ' Public DA As New MySql.Data.MySqlClient.MySqlDataAdapter
    ' Public RD As MySqlDataReader
    'Public simpan, ubah, hapus As String

    Public Function OpenConnAccess() As Boolean

        Dim SQLConnection As MySql.Data.MySqlClient.MySqlConnection
        Dim sConnString As String = "server=localhost;user id=root;password=;database=db_peramalan"
        SQLConnection = New MySql.Data.MySqlClient.MySqlConnection(sConnString)
        SQLConnection.Open()

        Try
            Cn = New MySql.Data.MySqlClient.MySqlConnection(sConnString)
            Cn.Open()
            If Cn.State <> ConnectionState.Open Then
                Return False
            Else
                Return True
            End If
        Catch ex As Exception
            MsgBox(ex.ToString())
            Return False
        End Try
    End Function

    Public Sub CloseConn()
        If Not IsNothing(Cn) Then
            Cn.Close()
            Cn = Nothing
        End If
    End Sub
    Public Function ExecuteQuery(ByVal Query As String) As DataTable
        If Not OpenConnAccess() Then
            MsgBox("Koneksi Gagal..!!", MsgBoxStyle.Critical, "Access Failed")
            Return Nothing
            Exit Function
        End If

        Cmd = New MySql.Data.MySqlClient.MySqlCommand(Query, Cn)
        Da = New MySql.Data.MySqlClient.MySqlDataAdapter
        Da.SelectCommand = Cmd

        Ds = New Data.DataSet
        Da.Fill(Ds)

        Dt = Ds.Tables(0)

        Return Dt

        Dt = Nothing
        Ds = Nothing
        Da = Nothing
        Cmd = Nothing

        CloseConn()

    End Function
    Public Sub ExecuteNonQuery(ByVal Query As String)
        If Not OpenConnAccess() Then
            MsgBox("Koneksi Gagal..!!", MsgBoxStyle.Critical, "Access Failed..!!")
            Exit Sub
        End If

        Cmd = New MySql.Data.MySqlClient.MySqlCommand
        Cmd.Connection = Cn
        Cmd.CommandType = CommandType.Text
        Cmd.CommandText = Query
        Cmd.ExecuteNonQuery()
        Cmd = Nothing
        CloseConn()
    End Sub
End Class


KONEKSI KE MYSQL (sebelumnya install dahulu driver odbc mysql)
koneksi.vb


Imports Microsoft.VisualBasic
Imports MySql.Data.MySqlClient


Public Class koneksi
    Protected tblPengguna = New DataTable
    Protected SQL As String
    Protected Cn, m_conn As MySql.Data.MySqlClient.MySqlConnection
    Protected Cmd As MySql.Data.MySqlClient.MySqlCommand
    Protected Da As MySql.Data.MySqlClient.MySqlDataAdapter
    Protected Ds As DataSet
    Protected Dt As DataTable

    Public Function OpenConnAccess() As Boolean

        Dim SQLConnection As MySql.Data.MySqlClient.MySqlConnection
        Dim sConnString As String = "server=localhost;user id=root;password=;database=db_peramalan"
        SQLConnection = New MySql.Data.MySqlClient.MySqlConnection(sConnString)
        SQLConnection.Open()

        Try
            Cn = New MySql.Data.MySqlClient.MySqlConnection(sConnString)
            Cn.Open()
            If Cn.State <> ConnectionState.Open Then
                Return False
            Else
                Return True
            End If
        Catch ex As Exception
            MsgBox(ex.ToString())
            Return False
        End Try
    End Function

    Public Sub CloseConn()
        If Not IsNothing(Cn) Then
            Cn.Close()
            Cn = Nothing
        End If
    End Sub
    Public Function ExecuteQuery(ByVal Query As String) As DataTable
        If Not OpenConnAccess() Then
            MsgBox("Koneksi Gagal..!!", MsgBoxStyle.Critical, "Access Failed")
            Return Nothing
            Exit Function
        End If

        Cmd = New MySql.Data.MySqlClient.MySqlCommand(Query, Cn)
        Da = New MySql.Data.MySqlClient.MySqlDataAdapter
        Da.SelectCommand = Cmd

        Ds = New Data.DataSet
        Da.Fill(Ds)

        Dt = Ds.Tables(0)

        Return Dt

        Dt = Nothing
        Ds = Nothing
        Da = Nothing
        Cmd = Nothing

        CloseConn()

    End Function
    Public Sub ExecuteNonQuery(ByVal Query As String)
        If Not OpenConnAccess() Then
            MsgBox("Koneksi Gagal..!!", MsgBoxStyle.Critical, "Access Failed..!!")
            Exit Sub
        End If

        Cmd = New MySql.Data.MySqlClient.MySqlCommand
        Cmd.Connection = Cn
        Cmd.CommandType = CommandType.Text
        Cmd.CommandText = Query
        Cmd.ExecuteNonQuery()
        Cmd = Nothing
        CloseConn()
    End Sub
End Class
+++++++++++++++++++

Login.vb


Public Class Login
    Dim SQL As String
    Dim Proses As New koneksi
    Dim myTbl As DataTable


    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Dim u, p As String
        u = txtUsername.Text
        p = txtPassword.Text

        myTbl = Proses.ExecuteQuery("Select * From tb_petugas where username='" & u & "' and password='" & p & "' and status='" & "Aktif" & "'")
        If myTbl.Rows.Count > 0 Then
            Me.Visible = False
            MenuUtama.Show()

            txtUsername.Text = ""
            txtPassword.Text = ""
            txtUsername.Focus()

        Else
            MsgBox("Login Gagal...Silakan Hub Administrator", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Fail")
        End If

    End Sub
End Class

++++++++++++++++++++++

Transaksi.vb

Public Class DataPenjualan
    Dim SQL As String
    Dim Proses As New koneksi
    Dim myTbl As DataTable
 


    Private Sub Save_Click() Handles btnSimpan.Click
        Dim id_barang, nama_barang, tahun, jumlah_produksi As String
        id_barang = txtIdBarang.Text
        nama_barang = txtNamaBarang.Text
        tahun = txtTahun.Text
        jumlah_produksi = txtJumlahProduksi.Text

        SQL = "Insert into tb_penjualan (`id_barang`, `nama_barang`, `tahun`, `jumlah_produksi`) Values ('" & id_barang & "','" & nama_barang & "','" & tahun & "','" & jumlah_produksi & "')"
        Proses.ExecuteNonQuery(SQL)

        MsgBox("Simpan data berhasil", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Simpan")
        bersih()
    End Sub

    Private Sub btnUbah_Click() Handles btnUbah.Click
        Dim id_barang, nama_barang, tahun, jumlah_produksi As String
        id_barang = txtIdBarang.Text
        nama_barang = txtNamaBarang.Text
        tahun = txtTahun.Text
        jumlah_produksi = txtJumlahProduksi.Text

        If MessageBox.Show("Yakin akan diubah..?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then

            SQL = "Insert into tb_penjualan (`id_barang`, `nama_barang`, `tahun`, `jumlah_produksi`) Values ('" & id_barang & "','" & nama_barang & "','" & tahun & "','" & jumlah_produksi & "')"
            Proses.ExecuteNonQuery(SQL)
        End If
        MsgBox("Ubah data berhasil", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Ubah")
        bersih()
    End Sub


    Private Sub btnHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHapus.Click
        Dim id_barang As String
        id_barang = txtIdBarang.Text
        Dim mau As String
        mau = MsgBox("Anda mau hapus data ini?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Hapus")
        If mau = vbYes Then
            SQL = "delete from tb_penjualan where id_barang = '" & txtIdBarang.Text & "'"
            Proses.ExecuteNonQuery(SQL)
        End If
        Call bersih()
        MsgBox("HApus data berhasil", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Hapus")
    End Sub


    Sub bersih()
        txtIdBarang.Text = ""
        txtNamaBarang.Text = ""
        txtTahun.Text = ""
        txtJumlahProduksi.Text = ""
        lihat()

        btnUbah.Enabled = False
        btnHapus.Enabled = False
        btnSimpan.Enabled = True
        txtIdBarang.Enabled = True
    End Sub

    Sub Kode_Otomatis()
        myTbl = Proses.ExecuteQuery("Select * From `tb_penjualan` order by id_barang desc")
        If myTbl.Rows.Count = 0 Then
            txtIdBarang.Text = "ADM" + "-001"
        Else
            With myTbl.Rows(0)
                txtIdBarang.Text = .Item("Id_Barang")
            End With

            txtIdBarang.Text = Val(Microsoft.VisualBasic.Mid(txtIdBarang.Text, 5)) + 1

            If Len(txtIdBarang.Text) = 1 Then
                txtIdBarang.Text = "ADM" + "-00" & txtIdBarang.Text & ""
            ElseIf Len(txtIdBarang.Text) = 2 Then
                txtIdBarang.Text = "ADM" + "-0" & txtIdBarang.Text & ""
            ElseIf Len(txtIdBarang.Text) = 3 Then
                txtIdBarang.Text = "ADM" + "" & txtIdBarang.Text & ""
            End If
        End If
    End Sub


    Sub lihat()
        myTbl = Proses.ExecuteQuery("Select * From tb_penjualan order by id_barang desc")
        dgpenjualan.DataSource = myTbl
        dgpenjualan.Columns(1).Width = 130
    End Sub


    Private Sub dgpenjualan_CellMouseClick() Handles dgpenjualan.CellMouseClick
        On Error Resume Next
        txtIdBarang.Enabled = False
        txtIdBarang.Text = dgpenjualan.SelectedCells(0).Value
        txtNamaBarang.Text = dgpenjualan.SelectedCells(1).Value
        txtTahun.Text = dgpenjualan.SelectedCells(2).Value
        txtJumlahProduksi.Text = dgpenjualan.SelectedCells(3).Value

        btnUbah.Enabled = True
        btnHapus.Enabled = True
        btnSimpan.Enabled = False
    End Sub
End Class

++++++++++++++++++++++++++++++++++++

Fungsi Menghitung Durasi Bulan dan Hari

  Function bacaUsia()
        On Error Resume Next
        Dim tgl1, tgl2 As String
        tgl1 = txtTanggalLahir.Text
        tgl2 = Now.Date
        Console.WriteLine("NOW=" & tgl2)
        Console.WriteLine("CHOOSE =" & tgl1)

        Dim ctgl1, ctgl2 As Date
        ctgl1 = CDate(tgl1)
        ctgl2 = CDate(tgl2)

        Dim selt, selb, selh As Integer
       selh = DateDiff(DateInterval.Day, ctgl1, ctgl2)
   
        Dim d As Date = Date.FromOADate(selh)
        Dim hs = d.ToString
        hs = hs.Replace(" 00:00:00", "")
        Dim ar() As String
        ar = hs.Split("/")
        Dim tothari As String = ar(0)
        Dim totbulan = Convert.ToInt32(ar(1)) + ((Convert.ToInt32(ar(2)) - 1900) * 12)
        Console.WriteLine(hs & "#totbulan=" & totbulan & ", " & tothari)
        Console.WriteLine("=====================================")
        '14/04/1901 00:00:00

        txtBulan.Text = CStr(totbulan)
        txtHari.Text = tothari


    End Function