Menghubungkan Database Ms.Acces

Imports System.Data
Imports System.Data.OleDb
Imports System.DBNull

Public Class Form1
    Inherits System.Windows.Forms.Form
    'Pendeklarasian objek
    Dim objConnection As OleDbConnection = New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Dea Prantika\Database\Database dan Data Binding\buku.mdb")
    Dim objDataAdapter As OleDbDataAdapter
    Dim objDataSet As DataSet
    Dim objDataView As DataView
    Dim WithEvents objCurrencyManager As CurrencyManager

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'KatalogDataSet.buku' table. You can move, or remove it, as needed.
        'Me.BukuTableAdapter.Fill(Me.KatalogDataSet.buku)FillDataSetAndView()
        FillDataSetAndView()
    End Sub

    Private Sub FillDataSetAndView()
        objDataView = Nothing 'membersihkan objek sebelum digunakan
        objDataSet = New DataSet() 'inisialisasi instance baru dari bjek data set
        objConnection.Open() 'membuka koneksi dataset
        objDataAdapter = New OleDbDataAdapter("select * from buku ORDER BY kd_buku", objConnection)
        objDataAdapter.Fill(objDataSet, ("buku"))
        objDataView = New DataView(objDataSet.Tables("buku"))
        objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)
        objConnection.Close()

        DataGridView1.DataSource = objDataView
        DataGridView1.Refresh()
        tampil()
        objDataSet = Nothing
    End Sub

    Private Sub ShowPosition()
        txt_posisi.Text = objCurrencyManager.Position + 1 & "dari" & objCurrencyManager.Count()
    End Sub

    Private Sub btn_First_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_first.Click
        objCurrencyManager.Position = 0
        ShowPosition()
    End Sub

    Private Sub btn_previous_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_previous.Click
        objCurrencyManager.Position -= 1
        ShowPosition()
    End Sub

    Private Sub btn_next_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_next.Click
        objCurrencyManager.Position += 1
        ShowPosition()
    End Sub

    Private Sub btn_last_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_last.Click
        objCurrencyManager.Position = objCurrencyManager.Count()
        ShowPosition()
    End Sub

    Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add.Click
        Dim intPosition As Integer, intMaxID As Integer
        Dim strId As String
        Dim objCommand As OleDbCommand = New OleDbCommand()

        intPosition = objCurrencyManager.Position
        objDataSet = New DataSet()

        objConnection.Open()
        objDataAdapter = New OleDbDataAdapter("SELECT MAX(kd_buku) as MaxID from buku;", objConnection)
        objDataAdapter.Fill(objDataSet, "buku")

        'jika maxid kosong
        If objDataSet.Tables("buku").Rows(0).Item("MaxID") Is System.DBNull.Value Then
            intMaxID = 1000
        Else
            'strId = CStr(objDataSet.Tables("buku")).Rows(0).Item("MaxID")
            intMaxID += 1
        End If
        strId = "10" & intMaxID

        objCommand.Connection = objConnection
        objCommand.CommandText = "INSERT INTO buku (kd_buku, nm_buku, nm_pengarang, harga) values(?, ?, ?, ?,)"
        objCommand.CommandType = CommandType.Text

        'parameter untuk kolom kd_buku
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(0).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(0).DbType = DbType.String
        objCommand.Parameters.Item(0).Size = 4
        objCommand.Parameters.Item(0).Value = Txt_kd_buku.Text

        'parameter untuk kolom nm_buku
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(1).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(1).DbType = DbType.String
        objCommand.Parameters.Item(1).Size = 30
        objCommand.Parameters.Item(1).Value = Txt_nm_buku.Text

        'parameter untuk pengarang
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(2).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(2).DbType = DbType.String
        objCommand.Parameters.Item(2).Size = 20
        objCommand.Parameters.Item(2).Value = Txt_pengarang.Text

        'parameter untuk harga
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(3).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(3).DbType = DbType.Int64
        objCommand.Parameters.Item(3).Size = 8
        objCommand.Parameters.Item(3).Value = Txt_hrg.Text

        'eksekusi objek oledbcommand untuk memasukan data baru
        Try
            objCommand.ExecuteNonQuery()
        Catch err As OleDbException
            MessageBox.Show(err.Message)
        End Try

        objConnection.Close()

        'mengisi dataset dan binding kolom
        FillDataSetAndView()
        tampil()
        'BindFields()
        objCurrencyManager.Position = intPosition
        ShowPosition()
        'menampilkan pesan data telah ditambahkan
        ToolStripStatusLabel1.Text = "Data telah ditambahkan"
        'bersihkan
        objDataSet = Nothing
        objDataAdapter = Nothing
        objCommand = Nothing
    End Sub

    Private Sub btn_add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add.Click
        Txt_kd_buku.Text = ""
        Txt_nm_buku.Text = ""
        Txt_pengarang.Text = ""
        Txt_hrg.Text = ""
        Txt_cari.Text = ""
    End Sub

    Private Sub btn_update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_update.Click
        Dim intPosition As Integer
        Dim objCommand As OleDbCommand = New OleDbCommand()
        'menyimpan posisi data saat ini
        intPosition = objCurrencyManager.Position
        'membuka koneksi database
        objConnection.Open()

        objCommand.Connection = objConnection
        objCommand.CommandText = "UPDATE buku Set nm_buku = ?, nm_pengarang = ?, harga = ?, WHERE kd_buku = ?"
        objCommand.CommandType = CommandType.Text

        'parameter untuk kolom nm_buku
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(0).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(0).DbType = DbType.String
        objCommand.Parameters.Item(0).Size = 30
        objCommand.Parameters.Item(0).Value = Txt_nm_buku.Text

        'parameter untuk kolom pngarang
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(1).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(1).DbType = DbType.String
        objCommand.Parameters.Item(1).Size = 20
        objCommand.Parameters.Item(1).Value = Txt_pengarang.Text

        'parameter untuk kolom harga
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(2).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(2).DbType = DbType.Currency
        objCommand.Parameters.Item(2).Size = 8
        objCommand.Parameters.Item(2).Value = Txt_hrg.Text

        'parameter untuk kolom kd_buku
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(3).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(3).DbType = DbType.String
        objCommand.Parameters.Item(3).Size = 4
        objCommand.Parameters.Item(3).Value = Txt_kd_buku.Text

        objCommand.ExecuteNonQuery()
        objConnection.Close()
        FillDataSetAndView()
        tampil()
        'BindFields()
        objCurrencyManager.Position = intPosition
        ShowPosition()
        'menampilkan pesan data telah diganti
        ToolStripStatusLabel1.Text = "Data telah dganti"
        'bersihkan
        objCommand = Nothing
    End Sub

    Private Sub btn_sort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_sort.Click
        Select Case cmb_field.SelectedIndex
            Case 0 'kd_buku
                objDataView.Sort = "kd_buku"
            Case 1 'nm_buku
                objDataView.Sort = "nm_buku"
            Case 2 'nm_pengarang
                objDataView.Sort = "nm_pengarang"
            Case 3 'harga
                objDataView.Sort = "harga"
        End Select
        btn_First_Click(Nothing, Nothing)
    End Sub

    Private Sub btn_cari_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Cari.Click
        Dim intPosition As Integer
        Select Case cmb_field.SelectedIndex
            Case 0 'kd_buku
                objDataView.Sort = "kd_buku"
            Case 1 'nm_buku
                objDataView.Sort = "nm_buku"
            Case 2 'nm_pengarang
                objDataView.Sort = "nm_pengarang"
            Case 3 'harga
                objDataView.Sort = "harga"
        End Select
        If cmb_field.SelectedIndex < 3 Then
            intPosition = objDataView.Find(Txt_cari.Text)
        Else
            intPosition = objDataView.Find(CDec(Txt_cari.Text))
        End If

        If intPosition = -1 Then
            ToolStripStatusLabel1.Text = "data tidak ditemukan"
        Else
            ToolStripStatusLabel1.Text = "data ditemukan"
            objCurrencyManager.Position = intPosition
        End If
        ShowPosition()
    End Sub

    Private Sub btn_delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Delete.Click
        Dim intPosition As Integer
        Dim objcommand As OleDbCommand = New OleDbCommand()
        intPosition = Me.BindingContext(objDataView).Position - 1

        'jika posisi <0 set ke 0
        If intPosition < 0 Then
            intPosition = 0
        End If

        objConnection.Open()
        objcommand.Connection = objConnection
        objcommand.CommandText = "DELETE FROM buku WHERE kd_buku = ?;"
        objcommand.CommandType = CommandType.Text
        'menambahkan parameter
        'parameter untuk kolom kd_buku
        objcommand.Parameters.Add(New OleDbParameter())
        objcommand.Parameters.Item(0).Direction = ParameterDirection.Input
        objcommand.Parameters.Item(0).DbType = DbType.String
        objcommand.Parameters.Item(0).Size = 4
        objcommand.Parameters.Item(0).Value = Txt_kd_buku.Text

        'eksekusi untuk update data
        objcommand.ExecuteNonQuery()

        objConnection.Close()
        FillDataSetAndView()
        tampil()
        Me.BindingContext(objDataView).Position = intPosition
        ShowPosition()
        objcommand = Nothing
    End Sub

    Private Sub tampil()
        Me.Txt_kd_buku.Text = objDataView.Item(objCurrencyManager.Position).Item("kd_buku")
        Me.Txt_pengarang.Text = objDataView.Item(objCurrencyManager.Position).Item("nm_pengarang")
        Me.Txt_nm_buku.Text = objDataView.Item(objCurrencyManager.Position).Item("nm_buku")
        Me.Txt_hrg.Text = objDataView.Item(objCurrencyManager.Position).Item("harga")
    End Sub

    Private Sub objCurrencyManager_PositionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles objCurrencyManager.PositionChanged
        ShowPosition()
        tampil()
    End Sub

    Private Sub btn_exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_exit.Click
        End
    End Sub
End Class