Rekam DataGridView dari Import ke MYSQL - VB2010

Lihat sebelumnya :

Tabel Rincian Data SubDealer ( Sdeler )
Lihat  No.Faktur : SO-C-140715-0001

Headernya : Hsdeler

Selanjutnya lakukan Export  :

Setelah disimpan pilih format 97-2003

Kemudian lakukan Import Data Exel ke DataGridView

Kemudian Klik Tombol Rekam DataGridView ke MySQL :

Selanjutnya kita Cek data di MySQL nya :

Kita Lihat  No.Faktur : SO-C-140715-0002 dengan data yg sama dari import tadi

Data GridView hasil Import kita Rekam ke MYSQL karena di kawatirkan lampu mati mendadak
yg bisa menghilangkan data grid view tadi. Karena itu kita Rekam saja.

Karena Data ini bisa di panggil sewaktu - waktu karena sifatnya masih OC = Order Confirmation

Dan kalau OC sudah disetujui bisa di Posting dan menjadi Invoice = Tagihan kpd Customer.

Koding Data Grid View di Rekam ke MYSQL  :

Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
       ' Rekam ke header ( Hsdeler )
        CokHsdeler()
       ' Rekam ke Rincian / Sdeler
        konek()
        Dim cmd As New MySqlCommand("insert into Sdeler(kunci,noinv,torder,customercode,noitem,pol,kdrang,namrang,jumlah,hsat,stot,pdiskont,diskont,uang,inv,idisk,bersih) values(@kunciv,@nofakturv,@hdatav,@kdcustv,@nomorv,@nopolibagv,@kdrangv,@namrangv,@qtyv,@hsatv,@kotorv,@pdiskontv,@diskontv,@subtotalv,@pidiskv,@idiskv,@netv)", db)
    
        Try


            For i As Integer = 0 To (dgvoc.Rows.Count - 2)

                vnomor = dgvoc.Rows(i).Cells(0).Value
                vnopolibag = dgvoc.Rows(i).Cells(1).Value
                vkdrang = dgvoc.Rows(i).Cells(2).Value
                vnamrang = dgvoc.Rows(i).Cells(3).Value
                vqty = dgvoc.Rows(i).Cells(4).Value
                vhsat = dgvoc.Rows(i).Cells(5).Value
                vkotor = dgvoc.Rows(i).Cells(6).Value
                vpdiskont = dgvoc.Rows(i).Cells(7).Value
                vdiskont = dgvoc.Rows(i).Cells(8).Value
                vsubtotal = dgvoc.Rows(i).Cells(9).Value
                vpidisk = dgvoc.Rows(i).Cells(10).Value
                vidisk = dgvoc.Rows(i).Cells(11).Value
                vnet = dgvoc.Rows(i).Cells(12).Value
                mnom = vnomor
                mkunci = RTrim(txtnofaktur.Text) & Str(mnom)

                cmd.Parameters.AddWithValue("@kunciv", mkunci)
                cmd.Parameters.AddWithValue("@nofakturv", txtnofaktur.Text)
                cmd.Parameters.AddWithValue("@hdatav", hdata)
                'cmd.Parameters.AddWithValue("@dtpjualv", Format(dtpjual, "yyyy-MM-dd"))
                cmd.Parameters.AddWithValue("@kdcustv", txtkdcust.Text)
                cmd.Parameters.AddWithValue("@nomorv", vnomor)
                cmd.Parameters.AddWithValue("@nopolibagv", vnopolibag)
                cmd.Parameters.AddWithValue("@kdrangv", vkdrang)
                cmd.Parameters.AddWithValue("@namrangv", vnamrang)
                cmd.Parameters.AddWithValue("@qtyv", vqty)
                cmd.Parameters.AddWithValue("@hsatv", vhsat)
                cmd.Parameters.AddWithValue("@kotorv", vkotor)
                cmd.Parameters.AddWithValue("@pdiskontv", vpdiskont)
                cmd.Parameters.AddWithValue("@diskontv", vdiskont)
                cmd.Parameters.AddWithValue("@subtotalv", vsubtotal)
                cmd.Parameters.AddWithValue("@pidiskv", vpidisk)
                cmd.Parameters.AddWithValue("@idiskv", vidisk)
                cmd.Parameters.AddWithValue("@netv", vnet)

                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
            Next
            konek()
            Dim cmdx As New MySqlCommand(("update Sdeler set tglorder = '" & Format(dtpjual.Value, "yyyy-MM-dd HH:mm:ss") & "' WHERE noinv ='" & txtnofaktur.Text & "'"), db)
            cmdx.ExecuteNonQuery()
        Catch ex As Exception
            Beep()
            MsgBox(ex.Message, MsgBoxStyle.Information, "Informasi")
            cmd.Dispose()
        End Try

    End Sub

****

 Private Sub CokHsdeler()
        txtnofaktur.Text = Trim(txtnofaktur.Text)
        Try
            tutupDB()
            db.Open()

        Catch ex As MySqlException
            MsgBox(ex.Message)
            MsgBox("Koneksi tidak berhasil dilakukan")
            Exit Sub
        End Try
        Try
            strSql = "SELECT * FROM Hsdeler WHERE noinv ='" & txtnofaktur.Text & "'"

            cmd.CommandText = strSql
            cmd.Connection = db
            dadapter.SelectCommand = cmd

            datardr = cmd.ExecuteReader
            If datardr.HasRows Then
                datardr.Read()
                txtkdcust.Text = datardr("customercode")
                CekCust()
                MsgBox("Data dari Table HSdeler Ditemukan....!", MsgBoxStyle.Exclamation, "Perhatian")
                Tampilsdeler()
                Exit Sub
            Else
                MsgBox("Data di Table HSdeler Tidak Ditemukan....!", MsgBoxStyle.Exclamation, "Informasi")
                Try
                    tutupDB()
                    db.Open()

                Catch ex As MySqlException
                    MsgBox(ex.Message)
                    MsgBox("Koneksi tidak berhasil dilakukan")
                    Exit Sub
                End Try
                Dim mnoautob, mfaktur As Integer
                mnoautob = 1
                mfaktur = Val(hdata)
                Dim cmd As New MySqlCommand("insert into HSdeler(torder,noinv,tglorder,customercode,torderb) values('" & hdata & "','" & txtnofaktur.Text & "','" & Format(dtpjual.Value, "yyyy-MM-dd HH:mm:ss") & "','" & txtkdcust.Text & "', " & mfaktur & ")", db)
                cmd.ExecuteNonQuery()
                MsgBox("Data di Table HSdeler Berhasil di Rekam....!", MsgBoxStyle.Exclamation, "Informasi")
                Exit Sub
            End If
            ' db.Close()
        Catch ex As Exception

        End Try


    End Sub

Untuk Modulnya :
Module modulku
    Public vkunci, vnofaktur, vkdcust, vkdrang, vnamrang As String
    Public vhdata, vnomor, vnopolibag, vqty, vhsat, vkotor, vpdiskont, vdiskont, vtot As Integer
    Public vsubtotal, vpidisk, vidisk, vnet As Integer

    Public cmd, cmdx As New MySqlCommand
    Public dadapter, dadapterx As New MySqlDataAdapter
    Public datardr, datardrx As MySqlDataReader
    Public strSql, strSqlx, mnoinv As String
    Dim serv As String = "Server=192.168.1.8;"
    Dim dbase As String = "Database=toko;"
    Dim uid As String = "uid=root;"
    Dim pwd As String = "pwd=root;"
    Dim ConString = serv & dbase & uid & pwd
    Public db As New MySqlConnection(ConString)
    Public Sub konek()
        Try
            tutupDB()
            db.Open()
            MsgBox("Koneksi DataBase berhasil dilakukan")
        Catch ex As MySqlException
            MsgBox(ex.Message)
            MsgBox("Koneksi tidak berhasil dilakukan")
        End Try
    End Sub
    Public Sub tutupDB()
        If db.State = ConnectionState.Open Then
            db.Close()
        End If
    End Sub

End Module
***
declare variabel mnom bisa di letakkan di module

 Public Class lyr_oc
    Public mkunci, jum_record, hdata As String
    Public mthauto, nofak, mnom As Integer
'
'
'
'
'
End Class

Selamat Otak Atik