latihan 40B

Public Class Form40B36109062

    Dim dt As New DataTable

    Dim urim As New ByIskandar.CariKeDataBaseByIskandar

    Dim fani As New OleDb.OleDbCommand

    Dim KJS As New OleDb.OleDbConnection("PROVIDER = MICROSOFT.ACE.OLEDB.12.0; DATA SOURCE =" & Application.StartupPath & "/DataMajemuk.ACCDB")

    Public Sub okta2()

        Dim b As New OleDb.OleDbDataAdapter

        b = New OleDb.OleDbDataAdapter(" select BARANG.KODEBARANG, BARANG.NAMABARANG,DETAILTRANSAKSI.UNIT,DETAILTRANSAKSI.HARGA, DETAILTRANSAKSI.UNIT*DETAILTRANSAKSI.HARGA AS JUMLAH from DETAILTRANSAKSI INNER JOIN BARANG ON DETAILTRANSAKSI.KODEBARANG = BARANG.KODEBARANG where notrans = '" & nt062.Text & "'", KJS)

        dt.Rows.Clear()

        b.Fill(dt)

    End Sub

    Public Sub okta3()

        Dim total As Integer = 0

        For Each x As DataRow In dt.Rows

            total += x("Jumlah")

        Next

        tot062.Text = total

    End Sub

    Private Sub okta4()

        If nt062.Text.Length = 0 Then

            MsgBox("silahkan isi transaksinya")

            Exit Sub

        End If

        If jt062.Text.Length = 0 Then

            MsgBox("silahkan isi jenis transaksinya")

            Exit Sub

        End If

        If dt.Rows.Count = 0 Then

            MsgBox("silahkan isi datatable")

            Exit Sub

        End If

    End Sub

    Private Sub tambah()

        okta4()

        urim.AturPencarianDataBase("mastertransaksi", "notrans", nt062.Text, 1, KJS)

        If urim.JumlanBaris > 0 Then

            MsgBox("Kode barang tidak ada")

            Exit Sub

        End If

        fani = New OleDb.OleDbCommand("insert into mastertransaksi ( notrans, tanggaltransaksi, jenistransaksi)values ('" & nt062.Text & "' ,#" & tt062.Value.Month & "/" & tt062.Value.Day & "/" & tt062.Value.Year & "#, '" & jt062.Text & "')", KJS)

        KJS.Open()

        fani.ExecuteNonQuery()

        KJS.Close()

        For Each x As DataRow In dt.Rows

            fani = New OleDb.OleDbCommand("insert into detailtransaksi ( notrans, kodebarang, unit, harga) values ('" & nt062.Text & "','" & x("kodebarang") & "','" & x("unit") & "','" & x("harga") & "')", KJS)

            KJS.Open()

            fani.ExecuteNonQuery()

            KJS.Close()

        Next

        fani.Dispose()

        nt062.Text = ""

        jt062.Text = ""

        tot062.Text = ""

        ff.Text = "-"

        dt.Rows.Clear()

        Form40A36109062.OKTA1()

    End Sub

    Public Sub EDIT()

        okta4()

        If nt062.Text <> ff.Text Then

            urim.AturPencarianDataBase("mastertransaksi", "notrans", nt062.Text, 1, KJS)

            If urim.JumlanBaris > 0 Then

                MsgBox("Kode barang tidak ada")

                Exit Sub

            End If

        End If

        fani = New OleDb.OleDbCommand("insert into mastertransaksi ( notrans, tanggaltransaksi, jenistransaksi)values ('" & nt062.Text & "' ,#" & tt062.Value.Month & "/" & tt062.Value.Day & "/" & tt062.Value.Year & "#, '" & jt062.Text & "')", KJS)

        KJS.Open()

        fani.ExecuteNonQuery()

        KJS.Close()

        For Each x As DataRow In dt.Rows

            fani = New OleDb.OleDbCommand("insert into detailtransaksi ( notrans, kodebarang, unit, harga) values ('" & nt062.Text & "','" & x("kodebarang") & "','" & x("unit") & "','" & x("harga") & "')", KJS)

            KJS.Open()

            fani.ExecuteNonQuery()

            KJS.Close()

        Next

        fani.Dispose()

        nt062.Text = ""

        jt062.Text = ""

        tot062.Text = ""

        ff.Text = "-"

        dt.Rows.Clear()

        Form40A36109062.OKTA1()

    End Sub

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

        okta2()

        Dim dc(1) As DataColumn

        dc(0) = dt.Columns("kodebarang")

        dt.PrimaryKey = dc

        dt.Columns("unit").DefaultValue = 0

        dt.Columns("harga").DefaultValue = 0

        dt.Columns("jumlah").DefaultValue = 0

        dgv062.DataSource = dt

    End Sub

    Private Sub dgv062_cellendedit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv062.CellEndEdit

        If dgv062.Columns(e.ColumnIndex).Name = "KODEBARANG" Then

            'If dgv.CurrentRow.Cells(0) = "KODEBARANG" Then  (rumus lain)

            'untuk mencari nama barang

            dgv062.CurrentRow.Cells("NAMABARANG").Value = ""

            dgv062.CurrentRow.Cells("UNIT").Value = 0

            dgv062.CurrentRow.Cells("HARGA").Value = 0

            dgv062.CurrentRow.Cells("JUMLAH").Value = 0

            urim.AturPencarianDataBase("barang", "kodebarang", dgv062.CurrentRow.Cells("KODEBARANG").Value, 1, KJS)

            If urim.JumlanBaris > 0 Then  '(menampilkan nama barang jika kolom kode barang ditemukan oleh proses di atas

                dgv062.CurrentRow.Cells("NAMABARANG").Value = urim.DataTablenya.Rows(0).Item("NAMABARANG")

            Else

                dgv062.CurrentRow.Cells("NAMABARANG").Value = ""

                If Form383940_36109062.ShowDialog = Windows.Forms.DialogResult.OK Then

                    dgv062.CurrentRow.Cells("KODEBARANG").Value = Form383940_36109062.dgv062.CurrentRow.Cells("KODEBARANG").Value

                    dgv062.CurrentRow.Cells("NAMABARANG").Value = Form383940_36109062.dgv062.CurrentRow.Cells("NAMABARANG").Value

                End If

            End If

        ElseIf dgv062.Columns(e.ColumnIndex).Name = "UNIT" Or dgv062.Columns(e.ColumnIndex).Name = "HARGA" Then

            dgv062.CurrentRow.Cells("JUMLAH").Value = dgv062.CurrentRow.Cells("UNIT").Value * dgv062.CurrentRow.Cells("HARGA").Value

            okta3()

        End If

    End Sub

    Private Sub s062_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles s062.Click

        If ff.Text = "-" Then

            tambah()

        Else

            EDIT()

        End If

    End Sub

End Class

latihan 40A

Public Class Form40A36109062

    Dim KJS As New OleDb.OleDbConnection("PROVIDER = MICROSOFT.ACE.OLEDB.12.0; DATA SOURCE =" & Application.StartupPath & "/DataMajemuk.accdb")

    Dim dt1 As New DataTable

    Dim dt2 As New DataTable

    Dim ds As New DataSet

    Dim bs1 As New BindingSource

    Dim bs2 As New BindingSource

    Public Sub okta1()

        Dim a As New OleDb.OleDbDataAdapter

        dt2.Rows.Clear()

        dt1.Rows.Clear()

        a = New OleDb.OleDbDataAdapter("select * from mastertransaksi", KJS)

        a.Fill(dt1)

        a = New OleDb.OleDbDataAdapter("select detailtransaksi.notrans, barang.kodebarang, barang.namabarang, detailtransaksi.unit, detailtransaksi.harga, detailtransaksi.unit*harga as jumlah from barang inner join detailtransaksi on barang.kodebarang = detailtransaksi.kodebarang", KJS)

        a.Fill(dt2)

    End Sub

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

        okta1()

        ds.Tables.Add(dt1)

        ds.Tables.Add(dt2)

        dt1.TableName = "abeth1"

        dt2.TableName = "abeth2"

        ds.Relations.Add(New DataRelation("b", ds.Tables("abeth1").Columns("notrans"), ds.Tables("abeth2").Columns("notrans")))

        bs1.DataSource = ds

        bs1.DataMember = "abeth1"

        bs2.DataSource = bs1

        bs2.DataMember = "b"

        Dim dc(1) As DataColumn

        dc(0) = dt1.Columns("notrans")

        dt1.PrimaryKey = dc

        DGV1.DataSource = bs1

        DGV2.DataSource = bs2

    End Sub

    Private Sub AA062_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AA062.Click

        Dim fani As New OleDb.OleDbCommand

        fani = New OleDb.OleDbCommand("delete * from mastertransaksi where notrans = '" & DGV1.CurrentRow.Cells("notrans").Value & "'", KJS)

        KJS.Open()

        fani.ExecuteNonQuery()

        KJS.Close()

        fani = New OleDb.OleDbCommand("delete * from detailtransaksi where notrans = '" & DGV1.CurrentRow.Cells("notrans").Value & "'", KJS)

        KJS.Open()

        fani.ExecuteNonQuery()

        KJS.Close()

        fani.Dispose()

        okta1()

    End Sub

    Private Sub BB062_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BB062.Click

        If Form40B36109062.Visible = False Then

            Form40B36109062.Show()

        Else

            Form40B36109062.Activate()

        End If

        Form40B36109062.nt062.Text = ""

        Form40B36109062.jt062.Text = ""

        Form40B36109062.ff.Text = "-"

        Form40B36109062.okta2()

        Form40B36109062.okta3()

    End Sub

    Private Sub CC062_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CC062.Click

        If Form40B36109062.Visible = False Then

            Form40B36109062.Show()

        Else

            Form40B36109062.Activate()

        End If

        Form40B36109062.nt062.Text = DGV1.CurrentRow.Cells("notrans").Value

        Form40B36109062.tt062.Value = DGV1.CurrentRow.Cells("tanggaltransaksi").Value

        Form40B36109062.jt062.Text = DGV1.CurrentRow.Cells("jenistransaksi").Value

        Form40B36109062.ff.Text = DGV1.CurrentRow.Cells("notrans").Value

        Form40B36109062.okta2()

        Form40B36109062.okta3()

    End Sub

End Class