Langkah - langkah mudah membuat website instan dengan menggunakan Incomedia Website X5 :

1.Install softwarenya
2.Buat new project - next
----------------------------

----------------------------
3.isikan textbox yang ada - next
----------------------------

----------------------------
4.pilih salah satu bentuk template yang diinginkan
----------------------------

----------------------------
5.Membuat menu - next
----------------------------

----------------------------
6.Membuat content dari menu yang telah dibuat
----------------------------

----------------------------
7.Export / save hasilnya
----------------------------

----------------------------

Selamat mencoba

hari ini tanggal 17 Oktober 2008 hari jum'at aku mencoba menengok blogspotku, blog ini aku dedikasikan untuk mengingatkan aku dalam belajar program karena aku tahu persis akan kelemahanku yaitu mudah sekali lupa.

Sebetulnya kelemahan ini tidak cocok untuk menjadi seorang programer,karena salah satu syarat utama untuk menjadi itu haruslah memilki daya ingat yang tinggi.

hampir empat bulan aku tidak membaca dan melatih lagi seolah-olah aku sudah lupa semuanya tentang coding, karena hal itu aku jadi takut untuk memulai lagi, takut ini sama sekali tak beralasan dan aku tahu itu.
Kalau rasa takut itu menjalar terus kemudian membatu dalam hati maka aku sadar bahwa aku semakin jauh dari blog ini dan rasanya aku jadi pesimis akan nasib blog ini.

Ya Alloh..........
tolonglah aku...........
rahmatilah aku.....
limpahilah aku akan hidayahmu....
taufiqmu.....
bukakanlah hijabmu atas hati dan pikiranku.....
sehingga aku tahu......
engkau pernah menganugerahkan aku akan ingatan.....
karena anugrah itu aku hingga jadi idol kabupaten.......
namun anugrah itu sekarang kau ambil......

daya ingat itu......
daya tangkap itu......
konsentrasi itu..........
sekarang telah pergi dariku.....
entah dimana engkau berada......

Ya Alloh yang Maha rokhman dan rakhim.....
bimbinglah aku............................................Amiin

Import package yang di perlukan :

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Deklarasi

#Region "Declaration"
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataAdapter As OleDbDataAdapter
Dim StrSQL As String
Dim objDataTableText As New DataTable
Dim objDataTableAccess As New DataTable
#End Region

Buat namespace dengan nama accesdata dan masukkan clas didalamnya dengan nama dataconnection :

Imports System.Data
Imports System.Data.OleDb
Namespace acces

Public Class dataconnection
Dim objcon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\dotnut\My Project\project 1\fifo\bin\fifo wo cek.mdb;")
Public Function open() As OleDbConnection
objcon.Open()
Return objcon
End Function
Public Function close() As OleDbConnection
objcon.Close()
Return objcon
End Function
End Class
End Namespace

Buat form dengan kontrol textbox,combo box,button dan label dan open file dialog



Button .... di klik

Try
OFD.Filter = "CSV (Comma delimited) (*.csv) *.csv Text Documents(*.txt) *.txt All Files (*.*) *.*"
OFD.FilterIndex = 0
OFD.FileName = ""
Dim result As DialogResult = OFD.ShowDialog()
If result = DialogResult.Cancel Then
OFD.FileName = Nothing
txtfile.Text = ""
Else
txtfile.Text = OFD.FileName
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Sebelum klik tombol import pilih combo boxnya,untuk menentukan pilihan file
Button import klik :

If cbfile.Text = "FIFO" Then
fifo()
ElseIf cbfile.Text = "F4101" Then
f4101()
ElseIf cbfile.Text = "F4801" Then
'f4801()
backup()
End If

Buat prosedure backup bila combo box memilih f4801

Sub backup()
Try
If txtfile.Text.Trim = "" Then
MsgBox("Cari file terlebih dahulu", MsgBoxStyle.Information, "Pesan kesalahan(")
Exit Sub
End If
Dim TempPath, TempFile As String
TempFile = Mid(txtfile.Text.Trim, InStrRev(txtfile.Text.Trim, "\") + 1, Len(txtfile.Text.Trim))
TempPath = Microsoft.VisualBasic.Left(txtfile.Text.Trim, (Len(txtfile.Text.Trim) - Len(TempFile)))
Dim cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = "INSERT INTO F4801 (WADCTO,WADOCO,WASFXO,WARCTO,WARORN,WALNID," & _

" WAPTWO,WAPARS,WATYPS,WAPRTS,WADL01,WASTCM,WACO,WAMCU,WAMMCU,WALOCN,WAAISL,WABIN,WASRST,WADCG," & _
" WASUB,WAAN8,WAANO,WAANSA,WAANPA,WAANT,WANAN8,WATRDJ,WASTRT,WADRQJ,WADPL,WASTRX,WADAP,WADAT,WAPPDT," & _

" WAWR01,WAWR02,WAWR03,WAWR04,WAWR05,WAWR06,WAWR07,WAWR08,WAWR09,WAWR10,WAVR01,WAVR02,WAAMTO,WASETC," & _
" WABRT,WAPAYT,WAAMTC,WAHRSO,WAHRSC,WAAMTA,WAHRSA,WAITM,WAAITM,WALITM,WANUMB,WAAPID,WAUORG,WASOBK," & _
" WASOCN,WASOQS,WAQTYT,WAUOM,WASHNO,WAPBTM,WATBM,WATRT,WASHTY,WAPEC,WAPPFG,WABM,WARTG,WASPRT,WAUNCD," & _
" WAINDC,WARESC,WAMOH,WATDT,WAPOU,WAPC,WALTLV,WALTCM,WACTS1,WALOTN,WALOTP,WALOTG,WARAT1,WARAT2,WADCT," & _
" WASBLI,WARKCO,WABREV,WARREV,WADRWC,WARTCH,WAPNRQ,WAREAS,WAPHSE,WAXDSP,WABOMC,WAURCD,WAURDT,WAURAT," & _
"WAURAB,WAURRF,WAUSER,WAPID,WAJOBN,WAUPMJ,WATDAY,WAAAID,WANTST,WAXRTO,WAESDN,WAACDN,WASAID,WAMPOS,WAAPRT," & _
"WAAMLC,WAAMMC,WAAMOT,WALBAM,WAMTAM) SELECT * FROM [Text;DATABASE=" & TempPath & ";].[" & TempFile.Trim & "]"

cmd.Connection = mykoneksi.open()
cmd.ExecuteNonQuery()
mykoneksi.close()
MsgBox("Import Finish", MsgBoxStyle.Information, "Import")
Catch ex As Exception
MsgBox(ex.Message)
mykoneksi.close()
Exit Sub
End Try

Import package yang diperlukan :

Imports System.Data
Imports System.Data.OleDb

pada kesempatan ini kita akan menggunakan ADO.NET OLEDB dengan database Ms.Acces 2003
dengan tabel nama user terdiri dari field user dan password dan user sebagai primery keynya

langkah kedua :
-------------------------------------------------
deklarasi :

deklarasi untuk objek koneksi dan string koneksinya
Dim objcon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\dotnut\My Project\project 1\trainingharian\user.mdb;")

deklarasi komponen ado oledb

Dim objdatatable As New DataTable
Dim objdatset As New DataSet
Dim objcommand As OleDbCommand
Dim objdataadapter As OleDbDataAdapter
Dim objdatreader As OleDbDataReader
Dim strsql As String
Dim r As DataRow
Dim cmd As OleDbCommandBuilder
Dim t As Integer

Buat form seperti gambar di bawah
terdiri dari label ,textbox dan datagrid dan button

Tombol add di klik

Try
Dim box As String = Trim(TextBox1.Text)
Dim box2 As String = Trim(TextBox2.Text)
strsql = " INSERT INTO [user]([userid],[password]) values ('" & TextBox1.Text & "','" & TextBox2.Text & "') "
objcommand = New OleDbCommand
objcommand.Connection = objcon
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objcon.Open()
objcommand.ExecuteNonQuery()
objcon.Close()
MsgBox("sukses")

Catch ex As Exception
MsgBox(ex.Message)
End Try

Tombol edit di klik

Try

'cara kuno------------------------
'mykoneksi.open()
Dim NAMA As String = Trim(TextBox1.Text)
Dim PASS As String = TextBox2.Text
strsql = "UPDATE [user] set [password] = '" & TextBox2.Text & " ' where userid = '" & TextBox1.Text & "'"
objcommand = New OleDbCommand
objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objcommand.ExecuteNonQuery()
mykoneksi.close()
MsgBox("sukses")

Catch ex As Exception
MsgBox(ex.Message)
End Try

Tombol view diklik

Try
objdatatable.Clear()
strsql = "select * from [user]"
objcommand = New OleDbCommand
objcon.Open()
objcommand.Connection = objcon
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdatset, "user")
objdatatable = objdatset.Tables("user")
DataGrid1.DataSource = objdatatable
objcon.Close()

Catch ex As Exception
MsgBox(ex.Message)
End Try

Tombol delete diklik

Try
strsql = "delete from [user] where userid = '" & TextBox1.Text & "'"
objcommand = New OleDbCommand
objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objcommand.ExecuteNonQuery()
mykoneksi.close()
MsgBox("sukses")

Catch ex As Exception
MsgBox(ex.Message)
End Try

salah satu baris di datagrid di pilih untuk menampilkan di textbox
Ini berfungsi untuk edit dan delete

t = DataGrid1.CurrentRowIndex
With objdatatable.Rows(t)
TextBox1.Text = .Item(0).ToString
TextBox2.Text = .Item(1).ToString
End With
TextBox1.ReadOnly = True

Pertama kali program dijalankan

strsql = "select * from [user]"
objcommand = New OleDbCommand
objcommand.Connection = objcon
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdatset, "user")
objdatatable = objdatset.Tables("user")
DataGrid1.DataSource = objdatatable
objcon.Close()

Button Cancel

#Region "button cancel"

Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click

btnadd.Enabled = True

btnkoreksi.Enabled = False

Button1.Enabled = False

End Sub

#End Region

Cari WO Master

#Region "cari wo_master"

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

objdatatable.Clear()

ListView2.Refresh()

ListView2.Items.Clear()

If rbitemwo.Checked = False And rbprodwo.Checked = False And rbwomaster.Checked = False Then

MsgBox("Pilih dasar pencarian", MsgBoxStyle.Exclamation, "Informasi")

ElseIf rbitemwo.Checked = True And rbprodwo.Checked = False And rbwomaster.Checked = False Then

Try

cariwo(txtcariwo.Text, "itemno")

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

ElseIf rbitemwo.Checked = False And rbprodwo.Checked = True And rbwomaster.Checked = False Then

Try

cariprodwo(txtcariwo.Text)

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

ElseIf rbitemwo.Checked = False And rbprodwo.Checked = False And rbwomaster.Checked = True Then

Dim s1 As String = txtcariwo.Text

Dim s2 As Double = Integer.Parse(s1)

Try objcommand = New OleDbCommand

("select wono,docty,itemno,prodno,itemdes,model,color,xsize,brandname,qtyord from [VI_WOMASTER] where wono = " & s2 & "")

objcommand.Connection = mykoneksi.open

objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

objdatareaer = objcommand.ExecuteReader

For I = 0 To objdatatable.Rows.Count - 1

objdatareaer.Read()

With ListView2.Items.Add(objdatatable.Rows(I).Item(0))

.SubItems.Add(objdatatable.Rows(I).Item(1))

.SubItems.Add(objdatatable.Rows(I).Item(2))

.SubItems.Add(objdatatable.Rows(I).Item(3))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(4)), "", objdatatable.Rows(I).Item(4)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(5)), "", objdatatable.Rows(I).Item(5)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(6)), "", objdatatable.Rows(I).Item(6)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(7)), "", objdatatable.Rows(I).Item(7)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(8)), "", objdatatable.Rows(I).Item(8)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(9)), "", objdatatable.Rows(I).Item(9))) End With

Next I

objdatatable.Clear()

ListView2.Refresh()

ListView2.ResetText()

mykoneksi.close()

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End If Button1.Enabled = True

End Sub

#End Region

Button Pilih

#Region "button pilih"

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If tp2.Visible = True And tp3.Visible = False Then

Dim str As String = ListView1.SelectedItems(0).SubItems.Item(1).Text

txtwo.Text = ListView1.SelectedItems(0).SubItems.Item(1).Text

'txt(xrecno)wono,itemno,prodno,datein,prio,depsend,qtydest,qtysf,qtyinst,qtyqa,

'other(, depcause),remark,hapus

txtitem.Text = ListView1.SelectedItems(0).SubItems.Item(2).Text

txtprod.Text = ListView1.SelectedItems(0).SubItems.Item(3).Text

txtdate.Text = ListView1.SelectedItems(0).SubItems.Item(4).Text

cbprioritas.Text = ListView1.SelectedItems(0).SubItems.Item(5).Text

cbdept.Text = ListView1.SelectedItems(0).SubItems.Item(6).Text

txtdestroy.Text = ListView1.SelectedItems(0).SubItems.Item(7).Text

txtsf.Text = ListView1.SelectedItems(0).SubItems.Item(8).Text

txtinstan.Text = ListView1.SelectedItems(0).SubItems.Item(9).Text

txtqa.Text = ListView1.SelectedItems(0).SubItems.Item(10).Text

txtother.Text = ListView1.SelectedItems(0).SubItems.Item(11).Text

cbdepcause.Text = ListView1.SelectedItems(0).SubItems.Item(12).Text

txtremark.Text = ListView1.SelectedItems(0).SubItems.Item(13).Text

tp2.SendToBack()

tp1.BringToFront()

tp1.Show()

btnadd.Text = "Koreksi"

ElseIf tp2.Visible = False And tp3.Visible = True Then

txtwo.Text = ListView2.SelectedItems(0).SubItems.Item(0).Text

txtwo1.Text = ListView2.SelectedItems(0).SubItems.Item(1).Text

txtitem.Text = ListView2.SelectedItems(0).SubItems.Item(2).Text

txtprod.Text = ListView2.SelectedItems(0).SubItems.Item(3).Text

txtdesc.Text = ListView2.SelectedItems(0).SubItems.Item(4).Text

txtmodel.Text = ListView2.SelectedItems(0).SubItems.Item(5).Text

txtcolor.Text = ListView2.SelectedItems(0).SubItems.Item(6).Text

txtsize.Text = ListView2.SelectedItems(0).SubItems.Item(7).Text

txtcollec.Text = ListView2.SelectedItems(0).SubItems.Item(8).Text

txtqty.Text = ListView2.SelectedItems(0).SubItems.Item(9).Text

tp3.SendToBack()

tp1.BringToFront()

tp1.Show()

btnadd.Text = "Add"

End If End Sub

#End Region

Button Add

#Region "button add"

Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

If btnadd.Text = "Koreksi" Then

txtwo.ReadOnly = True

txtwo1.ReadOnly = True

txtitem.ReadOnly = True

txtprod.ReadOnly = True

txtdesc.ReadOnly = True

txtmodel.ReadOnly = True

txtcolor.ReadOnly = True

txtsize.ReadOnly = True

txtcollec.ReadOnly = True

txtqty.ReadOnly = True

txtdate.Enabled = True

cbprioritas.Enabled = True

cbdepcause.Enabled = True

txttotal.Enabled = True

txttotal.Enabled = True

txtdestroy.Enabled = True

txtsf.Enabled = True

txtinstan.Enabled = True

txtqa.Enabled = True

txtother.Enabled = True

cbdepcause.Enabled = True

txtremark.Enabled = True

cbdept.Enabled = True

txtwo.Focus()

btnkoreksi.Text = "Update"

Else txtwo.ReadOnly = True

txtwo1.ReadOnly = True

txtitem.ReadOnly = True

txtprod.ReadOnly = True

txtdesc.ReadOnly = True

txtmodel.ReadOnly = True

txtcolor.ReadOnly = True

txtsize.ReadOnly = True

txtcollec.ReadOnly = True

txtqty.ReadOnly = True

txtdate.Enabled = True

cbprioritas.Enabled = True

cbdepcause.Enabled = True

txttotal.Enabled = True

txttotal.Enabled = True

txtdestroy.Enabled = True

txtsf.Enabled = True

txtinstan.Enabled = True

txtqa.Enabled = True

txtother.Enabled = True

cbdepcause.Enabled = True

txtremark.Enabled = True

cbdept.Enabled = True

txtdate.Focus()

btnkoreksi.Enabled = True

btnkoreksi.Text = "Save"

btnadd.Enabled = False

End If

Button Koreksi

#Region "button koreksi"

Private Sub btnkoreksi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnkoreksi.Click

Try

If btnkoreksi.Text = "Update" Then

myinsert.updatescrapt(txtwo.Text, txtitem.Text, txtprod.Text, txtdate.Text, cbprioritas.Text, cbdept.Text, txtdestroy.Text, txtsf.Text, txtinstan.Text, txtqa.Text, txtother.Text, cbdepcause.Text, txtremark.Text, "F")

MsgBox("Sukses melakukan Update")

ElseIf btnkoreksi.Text = "Save" Then

myinsert.insertscrapt(txtwo.Text, txtitem.Text, txtprod.Text, txtdate.Text, cbprioritas.Text, cbdept.Text, txtdestroy.Text, txtsf.Text, txtinstan.Text, txtqa.Text, txtother.Text, cbdepcause.Text, txtremark.Text, "F")

MsgBox("Sukses melakukan simpan")

End If

Catch ex As Exception

MsgBox("Konek ke databse gagal" & vbCrLf & ex.Message)

End Try

End Sub

#End Region

Button WO

#Region "buttonwo"

Private Sub btnwo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnwo.Click

tp1.SendToBack()

tp2.SendToBack()

tp1.Visible = False

tp2.Visible = False

tp3.BringToFront()

tp3.Visible = True

tp3.Show()

rbwomaster.Checked = True

txtcariwo.Focus()

End Sub

#End Region

Prosedure Tampil

#Region "tampil"

Sub tampil(ByVal str As String)

Try

objcommand = New OleDbCommand

("select * from [db_scrapt] where wono = " & str & "")

objcommand.Connection = mykoneksi.open objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand") objdatatable = objdataset.Tables("brand") mykoneksi.close()

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End Sub

#End Region

Tombol Cari

Button Cari :
#Region "cari_data_input_scrapt"

Private Sub btncari_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncari.Click

objdatatable.Clear()

ListView1.Refresh()

ListView1.Items.Clear()

If rbwo.Checked = False And rbprod.Checked = False And rbdept.Checked = False Then MsgBox("Pilih dasar pencarian", MsgBoxStyle.Exclamation, "Informasi")

ElseIf rbdept.Checked = True And rbprod.Checked = False And rbwo.Checked = False Then

Try

cari(txtcari.Text, "depsend")

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

ElseIf rbdept.Checked = False And rbprod.Checked = True And rbwo.Checked = False Then

Try

cariprod(txtcari.Text, "prodno")

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

ElseIf rbdept.Checked = False And rbprod.Checked = False And rbwo.Checked = True Then Dim s1 As String = txtcari.Text

Dim s2 As Double = Integer.Parse(s1)

Try

objcommand = New OleDbCommand("select * from [db_scrapt] where wono = " & s2 & "") objcommand.Connection = mykoneksi.open

objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

objdatareaer = objcommand.ExecuteReader

For I = 0 To objdatatable.Rows.Count - 1

objdatareaer.Read()

Dim tgl As Date = CDate(objdatareaer.Item(4))

With ListView1.Items.Add(objdatareaer.Item(0))

.SubItems.Add(objdatareaer.Item(1))

.SubItems.Add(objdatareaer.Item(2))

.SubItems.Add(objdatareaer.Item(3))

.SubItems.Add(tgl.ToShortDateString)

'.SubItems.Add(objdatareaer.Item(4))

.SubItems.Add(objdatareaer.Item(5))

.SubItems.Add(objdatareaer.Item(6))

.SubItems.Add(objdatareaer.Item(7))

.SubItems.Add(objdatareaer.Item(8))

.SubItems.Add(objdatareaer.Item(9))

.SubItems.Add(objdatareaer.Item(10))

.SubItems.Add(objdatareaer.Item(11))

.SubItems.Add(objdatareaer.Item(12))

.SubItems.Add(objdatareaer.Item(13))

.SubItems.Add(objdatareaer.Item(14))

End With

Next I

mykoneksi.close()

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End If

Button1.Enabled = True

End Sub

#End Region

Program Di Load

#Region "load program"

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

'contoh()

txtwo.Enabled = False

txtwo1.Text = "WO"

txtwo1.ReadOnly = True

txtitem.Enabled = False

txtprod.Enabled = False

txtdesc.Enabled = False

txtmodel.Enabled = False

txtcolor.Enabled = False

txtsize.Enabled = False

txtcollec.Enabled = False

txtqty.Enabled = False

txtdate.Enabled = False

cbprioritas.Enabled = False

cbdepcause.Enabled = False

txttotal.Enabled = False

txttotal.Enabled = False

txtdestroy.Enabled = False

txtsf.Enabled = False

txtinstan.Enabled = False

txtqa.Enabled = False

txtother.Enabled = False

cbdepcause.Enabled = False

txtremark.Enabled = False

cbdept.Enabled = False

btnadd.Enabled = True

End Sub

#End Region

Tombol Cari klik

#Region "cari"

Sub cari(ByVal cari As String, ByVal fil As String) objdatatable.Clear()

ListView1.Refresh()

ListView1.Items.Clear()

Try

objcommand = New OleDbCommand

("select * from [db_scrapt]where " & fil & " = '" & cari & "'")

objcommand.Connection = mykoneksi.open objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

objdatareaer = objcommand.ExecuteReader

For I = 0 To objdatatable.Rows.Count - 1

objdatareaer.Read()

Dim tgl As Date = CDate(objdatareaer.Item(4))

With ListView1.Items.Add(objdatareaer.Item(0))

.SubItems.Add(objdatareaer.Item(1))

.SubItems.Add(objdatareaer.Item(2))

.SubItems.Add(objdatareaer.Item(3))

.SubItems.Add(tgl.ToShortDateString)

.SubItems.Add(objdatareaer.Item(5))

.SubItems.Add(objdatareaer.Item(6))

.SubItems.Add(objdatareaer.Item(7))

.SubItems.Add(objdatareaer.Item(8))

.SubItems.Add(objdatareaer.Item(9))

.SubItems.Add(objdatareaer.Item(10))

.SubItems.Add(objdatareaer.Item(11))

.SubItems.Add(objdatareaer.Item(12))

.SubItems.Add(objdatareaer.Item(13))

.SubItems.Add(objdatareaer.Item(14))

End With

Next I

mykoneksi.close()

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End Sub

#End Region

Cari Berdasarkan Production Number

#Region "cariprod"

Sub cariprod(ByVal cari As String, ByVal fil As String)

objdatatable.Clear()

ListView1.Refresh()

ListView1.Items.Clear()

Try objcommand = New OleDbCommand

("select * from [db_scrapt]where " & fil & " like '%" & cari & "%'")

objcommand.Connection = mykoneksi.open

objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

objdatareaer = objcommand.ExecuteReader

For I = 0 To objdatatable.Rows.Count - 1

objdatareaer.Read()

Dim tgl As Date = CDate(objdatareaer.Item(4))

With ListView1.Items.Add(objdatareaer.Item(0))

.SubItems.Add(objdatareaer.Item(1))

.SubItems.Add(objdatareaer.Item(2))

.SubItems.Add(objdatareaer.Item(3))

.SubItems.Add(tgl.ToShortDateString)

.SubItems.Add(objdatareaer.Item(5))

.SubItems.Add(objdatareaer.Item(6))

.SubItems.Add(objdatareaer.Item(7))

.SubItems.Add(objdatareaer.Item(8))

.SubItems.Add(objdatareaer.Item(9))

.SubItems.Add(objdatareaer.Item(10))

.SubItems.Add(objdatareaer.Item(11))

.SubItems.Add(objdatareaer.Item(12))

.SubItems.Add(objdatareaer.Item(13))

.SubItems.Add(objdatareaer.Item(14))

End With Next I mykoneksi.close()

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End Sub

#End Region

#Region "contoh"

Sub contoh()

Try

objcommand = New OleDbCommand("select * from [db_scrapt] ")

objcommand.Connection = mykoneksi.open

objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

objdatareaer = objcommand.ExecuteReader

For I = 0 To objdatatable.Rows.Count - 1

objdatareaer.Read()

Dim tgl As Date = CDate(objdatareaer.Item(4))

With ListView1.Items.Add(objdatareaer.Item(0))

'.SubItems.Add(objdatareaer.Item(0))

.SubItems.Add(objdatareaer.Item(1))

.SubItems.Add(objdatareaer.Item(2))

.SubItems.Add(objdatareaer.Item(3))

.SubItems.Add(tgl.ToShortDateString)

.SubItems.Add(objdatareaer.Item(5))

.SubItems.Add(objdatareaer.Item(6))

.SubItems.Add(objdatareaer.Item(7))

.SubItems.Add(objdatareaer.Item(8))

.SubItems.Add(objdatareaer.Item(9))

.SubItems.Add(objdatareaer.Item(10))

.SubItems.Add(objdatareaer.Item(11))

.SubItems.Add(objdatareaer.Item(12))

.SubItems.Add(objdatareaer.Item(13))

.SubItems.Add(objdatareaer.Item(14))

End With

Next I

mykoneksi.close()

Catch ex As Exception

MsgBox("tidak dapat menampilkan data" & vbCrLf & ex.Message)

End Try

End Sub

#End Region

Pencarian berdasarkan WO

#Region "cari wo"

Sub cariwo(ByVal cari As String, ByVal fil As String)

objdatatable.Clear()

ListView2.Refresh()

ListView2.Items.Clear()

Dim s1 As String = txtcariwo.Text

Dim s2 As Double = Integer.Parse(s1)

Try

objcommand = New OleDbCommand

("select wono,docty,itemno,prodno,itemdes,model,color,xsize,brandname,qtyord from [VI_WOMASTER]where " & fil & " = " & s2 & "")

objcommand.Connection = mykoneksi.open

objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

objdatareaer = objcommand.ExecuteReader

For I = 0 To objdatatable.Rows.Count - 1 objdatareaer.Read()

'Dim tgl As Date = CDate(objdatareaer.Item(4))

With ListView2.Items.Add(objdatatable.Rows(I).Item(0))

.SubItems.Add(objdatatable.Rows(I).Item(1))

.SubItems.Add(objdatatable.Rows(I).Item(2))

.SubItems.Add(objdatatable.Rows(I).Item(3))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(4)), "", objdatatable.Rows(I).Item(4))) '.SubItems.Add(objdatatable.Rows(I).Item(5))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(5)), "", objdatatable.Rows(I).Item(5)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(6)), "", objdatatable.Rows(I).Item(6))) '.SubItems.Add(objdatatable.Rows(I).Item(7))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(7)), "", objdatatable.Rows(I).Item(7))) '.SubItems.Add(objdatatable.Rows(I).Item(8))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(8)), "", objdatatable.Rows(I).Item(8))) '.SubItems.Add(objdatatable.Rows(I).Item(9))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(9)), "", objdatatable.Rows(I).Item(9))) End With

Next I

mykoneksi.close()

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End Sub

#End Region

Cari Berdasarkan Prod Wo

#Region "cariprodwo"

Sub cariprodwo(ByVal cari As String)

objdatatable.Clear()

ListView2.Refresh()

ListView2.Items.Clear()

Try

objcommand = New OleDbCommand

("select wono,docty,itemno,prodno,itemdes,model,color,xsize,brandname,qtyord from [VI_WOMASTER]where prodno like '%" & cari & "%'")

objcommand.Connection = mykoneksi.open

objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

objdatareaer = objcommand.ExecuteReader

For I = 0 To objdatatable.Rows.Count - 1

objdatareaer.Read()

'Dim tgl As Date = CDate(objdatareaer.Item(4))

With ListView2.Items.Add(objdatatable.Rows(I).Item(0))

.SubItems.Add(objdatatable.Rows(I).Item(1))

.SubItems.Add(objdatatable.Rows(I).Item(2))

.SubItems.Add(objdatatable.Rows(I).Item(3))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(4)), "", objdatatable.Rows(I).Item(4)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(5)), "", objdatatable.Rows(I).Item(5)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(6)), "", objdatatable.Rows(I).Item(6)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(7)), "", objdatatable.Rows(I).Item(7)))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(8)), "", objdatatable.Rows(I).Item(8))) '.SubItems.Add(objdatatable.Rows(I).Item(9))

.SubItems.Add(IIf(IsDBNull(objdatatable.Rows(I).Item(9)), "", objdatatable.Rows(I).Item(9))) End With Next I mykoneksi.close() Catch ex As Exception MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End Sub

#End Region

Tampil Berdasarkan WO

#Region "tampilwo"

Sub tampilwo()

Try

objcommand = New OleDbCommand("select * from [db_item]")

objcommand.Connection = mykoneksi.open

objdataadapter = New OleDbDataAdapter(objcommand)

objdataadapter.Fill(objdataset, "brand")

objdatatable = objdataset.Tables("brand")

mykoneksi.close()

Catch ex As Exception

MsgBox("konek ke database gagal" & ex.Message)

mykoneksi.close()

End Try

End Sub

#End Region

General declarasi

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Deklarasi

#Region "declar"
Dim mykoneksi As New accesdata.dataconnection
Dim myinsert As New accesdata.dataacces
Dim mytampil As New accesdata.tampil
Dim rc, x, I, bln, rowexce As Integer
Dim r As DataRow
Dim objdataset As New DataSet
Dim objdatatable As New DataTable
Dim objdatatable1 As New DataTable
Dim sql As String
#End Region

Konversi bulan

#Region "conversi"
Sub conversi()
If cbbulan.Text = "January" Then
bln = 1
ElseIf cbbulan.Text = "Pebruary" Then
bln = 2
ElseIf cbbulan.Text = "Maret" Then
bln = 3
ElseIf cbbulan.Text = "April" Then
bln = 4
ElseIf cbbulan.Text = "Mei" Then
bln = 5
ElseIf cbbulan.Text = "Juni" Then
bln = 6
ElseIf cbbulan.Text = "Juli" Then
bln = 7
ElseIf cbbulan.Text = "Agustus" Then
bln = 8
ElseIf cbbulan.Text = "September" Then
bln = 9
ElseIf cbbulan.Text = "Oktober" Then
bln = 10
ElseIf cbbulan.Text = "Nopember" Then
bln = 11
ElseIf cbbulan.Text = "Desember" Then
bln = 12
End If
End Sub
#End Region

Tampil

#Region "tampil"
Sub tampil()
objdatatable.Clear()
Try
objcommand = New OleDbCommand("select * from [q_export] where bln = " & bln & " and th='" & ndtahun.Text & "'")
objcommand.Connection = mykoneksi.open
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdataset, "scrapt")
objdatatable = objdataset.Tables("scrapt")
objdatareaer = objcommand.ExecuteReader
objdatareaer.Read()
Label5.Text = objdatatable.Rows.Count
dgexport.DataSource = objdatatable
mykoneksi.close()
Catch ex As Exception
MsgBox("konek ke database gagal" & ex.Message)
mykoneksi.close()
End Try
End Sub
#End Region

#Region "konek excel"
Sub konekexcel()
Dim namafile As String = Mid(Trim(txtnama.Text), InStrRev(Trim(txtnama.Text), "\", +1), Len(Trim(txtnama.Text)))
Dim objcon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & txtnama.Text & "'")
Dim objcommand1 As New OleDbCommand("select * from [namafile]")
objcommand1.Connection = objcon
objcon.Open()
objdataadapter1 = New OleDbDataAdapter(objcommand1)
objdataadapter1.Fill(objdataset1, "excell")
objdatatable1 = objdataset1.Tables("excell")
End Sub
#End Region

Export To Text / CSV

#Region "expor"
Sub exksportxt()
Try
conversi()
objdatatable.Clear()
objcommand = New OleDbCommand("select * from [q_export] where bln = " & bln & " and th='" & ndtahun.Text & "'")
objcommand.Connection = mykoneksi.open
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdataset, "scrapt")
objdatatable = objdataset.Tables("scrapt")
objdatareaer = objcommand.ExecuteReader
objdatareaer.Read()
Dim fs As FileStream = New FileStream(Trim(txtnama.Text), FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
'Dim tgl As Date = CDate(objdatareaer.Item(4))
Dim filn0 As String = objdatareaer.GetName(0)
Dim filn1 As String = objdatareaer.GetName(1)
Dim filn2 As String = objdatareaer.GetName(2)
Dim filn3 As String = objdatareaer.GetName(3)
Dim filn4 As String = objdatareaer.GetName(4)
Dim filn5 As String = objdatareaer.GetName(5)
Dim filn6 As String = objdatareaer.GetName(6)
Dim filn7 As String = objdatareaer.GetName(7)
Dim filn8 As String = objdatareaer.GetName(8)
Dim filn9 As String = objdatareaer.GetName(9)
Dim filn10 As String = objdatareaer.GetName(10)
Dim filn11 As String = objdatareaer.GetName(11)
Dim filn12 As String = objdatareaer.GetName(12)
Dim filn13 As String = objdatareaer.GetName(13)
Dim filn14 As String = objdatareaer.GetName(14)
sw.WriteLine(filn0 & "," & filn1 & "," & filn2 & "," & filn3 & "," & filn4 & "," & filn5 & "," & filn6 & "," & filn7 & "," & filn8 & "," & filn9 & "," & filn10 & "," & filn11 & "," & filn12 & "," & filn13 & "," & filn14)
For I = 0 To objdatatable.Rows.Count - 1
Dim tgl As Date = CDate(objdatareaer.Item(4))
Dim fil0 As String = objdatareaer.Item(0)
Dim fil1 As String = objdatareaer.Item(1)
Dim fil2 As String = objdatareaer.Item(2)
Dim fil3 As String = objdatareaer.Item(3)
Dim fil4 As String = tgl.ToShortDateString
Dim fil5 As String = objdatareaer.Item(5)
Dim fil6 As String = objdatareaer.Item(6)
Dim fil7 As String = objdatareaer.Item(7)
Dim fil8 As String = objdatareaer.Item(8)
Dim fil9 As String = objdatareaer.Item(9)
Dim fil10 As String = objdatareaer.Item(10)
Dim fil11 As String = objdatareaer.Item(11)
Dim fil12 As String = objdatareaer.Item(12)
Dim fil13 As String = objdatareaer.Item(13)
Dim fil14 As String = objdatareaer.Item(14)
sw.WriteLine(fil0 & "," & fil1 & "," & fil2 & "," & fil3 & "," & fil4 & "," & fil5 & "," & fil6 & "," & fil7 & "," & fil8 & "," & fil9 & "," & fil10 & "," & fil11 & "," & fil12 & "," & fil13 & "," & fil14)
jum1.Text = Convert.ToString(I)
jum2.Text = Convert.ToString(objdatatable.Rows.Count - 1)
Next I
MsgBox("Export data scrapt sukses", MsgBoxStyle.Information, "Informasi")
mykoneksi.close()
Catch ex As Exception
MsgBox("Export file gagal,ulangi lagi" & vbCrLf & ex.Message)
mykoneksi.close()
End Try
End Sub
#End Region

Exksport Ke Excel

#Region "exkspor excel"
Sub exsportexcel1()
conversi()
Dim objcom2 As New OleDbCommand("select * from [q_export] where bln = " & bln & " and th='" & ndtahun.Text & "'")
objcom2.Connection = mykoneksi.open
objdatareaer = objcom2.ExecuteReader
objdatareaer.Read()
sfd.ShowDialog()
Dim nama As String = sfd.FileName
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim tNode As TreeNode
Dim x, Add, z As Integer
Try
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlSheet.Cells(1, 1) = CStr(objdatareaer.GetName(0).ToString)
xlSheet.Cells(1, 2) = CStr(objdatareaer.GetName(1).ToString)
xlSheet.Cells(1, 3) = CStr(objdatareaer.GetName(2).ToString)
xlSheet.Cells(1, 4) = CStr(objdatareaer.GetName(3).ToString)
xlSheet.Cells(1, 5) = CStr(objdatareaer.GetName(4).ToString)
xlSheet.Cells(1, 6) = CStr(objdatareaer.GetName(5).ToString)
xlSheet.Cells(1, 7) = CStr(objdatareaer.GetName(6).ToString)
xlSheet.Cells(1, 8) = CStr(objdatareaer.GetName(7).ToString)
xlSheet.Cells(1, 9) = CStr(objdatareaer.GetName(8).ToString)
xlSheet.Cells(1, 10) = CStr(objdatareaer.GetName(9).ToString)
xlSheet.Cells(1, 11) = CStr(objdatareaer.GetName(10).ToString)
xlSheet.Cells(1, 12) = CStr(objdatareaer.GetName(11).ToString)
xlSheet.Cells(1, 13) = CStr(objdatareaer.GetName(12).ToString)
xlSheet.Cells(1, 14) = CStr(objdatareaer.GetName(13).ToString)
xlSheet.Cells(1, 15) = CStr(objdatareaer.GetName(14).ToString)
mykoneksi.close()
conversi()
objdatatable.Clear()
tampil()
For x = 0 To objdatatable.Rows.Count - 1
xlSheet.Cells(x + 2, 1) = CStr(objdatatable.Rows(x).Item(0))
xlSheet.Cells(x + 2, 2) = CStr(objdatatable.Rows(x).Item(1))
xlSheet.Cells(x + 2, 3) = CStr(objdatatable.Rows(x).Item(2))
xlSheet.Cells(x + 2, 4) = CStr(objdatatable.Rows(x).Item(3))
xlSheet.Cells(x + 2, 5) = CStr(objdatatable.Rows(x).Item(4))
xlSheet.Cells(x + 2, 6) = CStr(objdatatable.Rows(x).Item(5))
xlSheet.Cells(x + 2, 7) = CStr(objdatatable.Rows(x).Item(6))
xlSheet.Cells(x + 2, 8) = CStr(objdatatable.Rows(x).Item(7))
xlSheet.Cells(x + 2, 9) = CStr(objdatatable.Rows(x).Item(8))
xlSheet.Cells(x + 2, 10) = CStr(objdatatable.Rows(x).Item(9))
xlSheet.Cells(x + 2, 11) = CStr(objdatatable.Rows(x).Item(10))
xlSheet.Cells(x + 2, 12) = CStr(objdatatable.Rows(x).Item(11))
xlSheet.Cells(x + 2, 13) = CStr(objdatatable.Rows(x).Item(12))
xlSheet.Cells(x + 2, 14) = CStr(objdatatable.Rows(x).Item(13))
xlSheet.Cells(x + 2, 15) = CStr(objdatatable.Rows(x).Item(14))
Next x
xlSheet.Application.Visible = True
xlSheet.SaveAs(nama)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region

Button Eksport

#Region "button export"
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If cbext.SelectedIndex = 1 Then
exsportexcel1()
ElseIf cbext.SelectedIndex = 0 Then
exksportxt()
End If
End Sub
#End Region

Button Go

#Region "button go"
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
conversi()
tampil()
jum2.Text = Convert.ToString(objdatatable.Rows.Count - 1)
End Sub
#End Region

Load Program

Private Sub exportscrapt_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cbbulan.SelectedIndex = 0
End Sub

Buat Class Library :

1. Data koneksi :

mports System.Data
Imports System.Data.OleDb
Namespace accesdata
Public Class dataconnection
Dim Conect As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\dotnut\My Project\project 1\datascrapt.mdb;")
Public Function open() As OleDbConnection
Conect.Open()
Return Conect
End Function
Public Function close() As OleDbConnection
Conect.Close()
Return Conect
End Function
End Class
End Namespace

2. Data Acces :

Imports System.Data
Imports System.Data.OleDb
Namespace accesdata
Public Class dataacces

Public Function insert(ByVal brand As String, ByVal ket As String, ByVal hapus As String)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand("insert into [db_brand](brandcode,brandname,hapus) values ('" & brand & "','" & ket & "','" & hapus & "')")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function insertscrapt(ByVal wono As String, ByVal itemno As String, ByVal prodno As String, ByVal datein As String, ByVal prio As String, ByVal depsend As String, ByVal qtydest As String, ByVal qtysf As String, ByVal qtyinst As String, ByVal qtyqa As String, ByVal other As String, ByVal depcause As String, ByVal remark As String, ByVal hapus As String)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand("insert into [db_scrapt](wono,itemno,prodno,datein,prio,depsend,qtydest,qtysf,qtyinst,qtyqa,other,depcause,remark,hapus) values ('" & wono & "','" & itemno & "','" & prodno & "','" & datein & "','" & prio & "','" & depsend & "','" & qtydest & "','" & qtysf & "','" & qtyinst & "','" & qtyqa & "','" & other & "','" & depcause & "','" & remark & "','" & hapus & "')")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function insertdept(ByVal brand As String, ByVal ket As String, ByVal hapus As String)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand("insert into [db_dept](depcode,depname,hapus) values ('" & brand & "','" & ket & "','" & hapus & "')")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function insertmaster(ByVal itemno As String, ByVal prodno As String, ByVal itemdes As String, ByVal stty As String, ByVal itty As String, ByVal procode As String, ByVal brand As String, ByVal proddet As String, ByVal model As String, ByVal color As String, ByVal xsize As String)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand("insert into [db_item](itemno,prodno,itemdes,stty,itty,procode,brand,proddet,model,color,xsize) values ('" & itemno & "','" & prodno & "','" & itemdes & "','" & stty & "','" & itty & "','" & procode & "','" & brand & "','" & proddet & "','" & model & "','" & color & "','" & xsize & "')")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function updatescrapt(ByVal wono As String, ByVal itemno As String, ByVal prodno As String, ByVal datein As String, ByVal prio As String, ByVal depsend As String, ByVal qtydest As String, ByVal qtysf As String, ByVal qtyinst As String, ByVal qtyqa As String, ByVal other As String, ByVal depcause As String, ByVal remark As String, ByVal hapus As String)
Dim x As Integer = CInt(wono)
Dim i As Integer = CInt(itemno)
Dim a As Integer = CInt(qtydest)
Dim b As Integer = CInt(qtysf)
Dim c As Integer = CInt(qtyinst)
Dim d As Integer = CInt(qtyqa)
Dim e As Integer = CInt(other)
Try
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand

("update [db_scrapt] set itemno=" & i & ",prodno='" & prodno & "',datein='" & datein & "',prio='" & prio & "',depsend='" & depsend & "',qtydest=" & a & ",qtysf=" & b & ",qtyinst=" & c & ",qtyqa=" & d & ",other=" & e & ",depcause='" & depcause & "',remark='" & remark & "',hapus='" & hapus & "' where wono=" & x & "")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
Catch ex As Exception
MsgBox("gagal konek ke database OK" & vbCrLf & ex.Message)
End Try
End Function

Public Function update(ByVal brand1 As String, ByVal ket As String, ByVal hapus As String)
Dim x As Integer = CInt(brand1)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand

("update [db_brand] set brandname='" & ket & "',hapus='" & hapus & "' where brandcode= " & x & " ")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function updatedept(ByVal brand As String, ByVal ket As String, ByVal hapus As String)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand

("update [db_dept] set depname='" & ket & "',hapus='" & hapus & "' where depcode='" & brand & "'")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function updateuser(ByVal user As String, ByVal record As String, ByVal userid As String, ByVal passw As String, ByVal status As String, ByVal hapus As String)
Dim u As Integer = CInt(record)
Dim mykoneksi As New accesdata.dataconnection
'Dim objcommand As New OleDbCommand

("update [db_user2] set [password]='" & passw & "' where nama='" & user & "'")
Dim objcommand As New OleDbCommand

("update [db_user2] set record_id=" & u & ",userid='" & userid & "',[password]='" & passw & "',status='" & status & "',hapus='" & hapus & "' where username='" & user & "'")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function delete(ByVal brand As String)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand("delete from [db_brand] where brandcode='" & brand & "'")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

Public Function deletedept(ByVal brand As String)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand

("delete from [db_dept] where depcode='" & brand & "'")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function
End Class
End Namespace

3. Module :

Imports System.Data
Imports System.Data.OleDb
Module _Module
Public objconnection As OleDbConnection
Public objcommand As OleDbCommand
Public objdataadapter As OleDbDataAdapter
Public objdataadapter1 As OleDbDataAdapter
Public objdatareaer As OleDbDataReader
Public objdatareaer1 As OleDbDataReader
Public objdataset As New DataSet
Public objdataset1 As New DataSet
Public objdatatable As New DataTable
Public objdatatable1 As New DataTable
Public strsql As String
Public user, pass As String
End Module

FORM INPUT SCRAPT :

source code :

Deklarasi

#Region "declar"
Dim mykoneksi As New accesdata.dataconnection
Dim myinsert As New accesdata.dataacces
Dim mytampil As New accesdata.tampil
Dim rc, x, I As Integer
Dim r As DataRow
Dim objdataset As New DataSet
Dim objdatatable As New DataTable
#End Region

Menu utama

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim kosong As New Persiapan
kosong.Show()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim proses As New Form1
proses.Show()
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim export As New export
export.Show()
End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim importf As New import
importf.Show()
End Sub

Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles ToolBar1.ButtonClick
If e.Button Is ToolBarButton1 Then
Dim cur As New Persiapan
cur.Show()
ElseIf e.Button Is ToolBarButton2 Then
Dim import As New import
import.Show()
ElseIf e.Button Is ToolBarButton3 Then
Dim prose As New Form1
prose.Show()
ElseIf e.Button Is ToolBarButton4 Then
Dim expot As New export
expot.Show()
Else
End
End If
End Sub

Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
Dim kosong As New Persiapan
kosong.Show()
End Sub

Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
Dim importf As New import
importf.Show()
End Sub

Private Sub MenuItem5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem5.Click
Dim proses As New Form1
proses.Show()
End Sub

Private Sub MenuItem7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem7.Click
Dim export As New export
export.Show()
End Sub

Private Sub MenuItem8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem8.Click
End
End Sub

Private Sub Mainmenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim tgl As Date = System.DateTime.Now
StatusBar1.Panels(1).Text = CDate(tgl).ToShortDateString
StatusBar1.Panels(2).Text = CDate(tgl).ToLongTimeString
StatusBar1.Panels(0).Text = "CopyRifht ©Juli2008"
End Sub

Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click
Dim kosong As New Persiapan
kosong.Show()
End Sub

Private Sub Label5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label5.Click
Dim importf As New import
importf.Show()
End Sub

Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click
Dim proses As New Form1
proses.Show()
End Sub

Private Sub Label4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label4.Click
Dim export As New export
export.Show()
End Sub
End Class

Sebelum codeng tambahkan reference excel 11. dan office 11. object library ke project

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

deklaresi-----------------------

Inherits System.Windows.Forms.Form
Dim mykonek As New acces.datacon

Menampilkan yang akan di export-------------------

#Region "tampil"
Sub tampil()
Try
objdatatable.Clear()
Dim strsql As String
strsql = "Select * from [export] order by [urut]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "export")
objdatatable = objdatset.Tables("export")
DataGrid1.DataSource = objdatatable
mykonek.close()
Catch ex As Exception
MsgBox("kenapa ya tidak Konek" & ex.Message & vbCrLf)
End Try
End Sub
#End Region

Export ke excel------------------------------------

#Region "export"
Sub export()
Dim objcom2 As New OleDbCommand("select * from [export] order by [urut]")
objcom2.Connection = mykonek.open
objdatreader = objcom2.ExecuteReader
objdatreader.Read()
sfd.ShowDialog()
Dim nama As String = sfd.FileName
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim tNode As TreeNode
Dim x, Add, z As Integer
Try
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlSheet.Cells(1, 1) = CStr(objdatreader.GetName(0).ToString)
xlSheet.Cells(1, 2) = CStr(objdatreader.GetName(1).ToString)
xlSheet.Cells(1, 3) = CStr(objdatreader.GetName(2).ToString)
xlSheet.Cells(1, 4) = CStr(objdatreader.GetName(3).ToString)
xlSheet.Cells(1, 5) = CStr(objdatreader.GetName(5).ToString)
xlSheet.Cells(1, 6) = CStr(objdatreader.GetName(6).ToString)
xlSheet.Cells(1, 7) = CStr(objdatreader.GetName(7).ToString)
xlSheet.Cells(1, 8) = CStr(objdatreader.GetName(8).ToString)
xlSheet.Cells(1, 9) = CStr(objdatreader.GetName(10).ToString)
xlSheet.Cells(1, 10) = CStr(objdatreader.GetName(11).ToString)
xlSheet.Cells(1, 11) = CStr(objdatreader.GetName(12).ToString)
xlSheet.Cells(1, 12) = CStr(objdatreader.GetName(13).ToString)
xlSheet.Cells(1, 13) = CStr(objdatreader.GetName(14).ToString)
xlSheet.Cells(1, 14) = CStr(objdatreader.GetName(15).ToString)
'xlSheet.Cells(1, 15) = CStr(objdatreader.GetName(15).ToString)
'xlSheet.Cells(1, 16) = CStr(objdatreader.GetName(15).ToString)
mykonek.close()
'conversi()
objdatatable.Clear()
tampil()
For x = 0 To objdatatable.Rows.Count - 1
xlSheet.Cells(x + 2, 1) = CStr(objdatatable.Rows(x).Item(0))
xlSheet.Cells(x + 2, 2) = CStr(objdatatable.Rows(x).Item(1))
xlSheet.Cells(x + 2, 3) = CStr(objdatatable.Rows(x).Item(2))
xlSheet.Cells(x + 2, 4) = CStr(objdatatable.Rows(x).Item(3))
xlSheet.Cells(x + 2, 5) = CStr(objdatatable.Rows(x).Item(5))
xlSheet.Cells(x + 2, 6) = CStr(objdatatable.Rows(x).Item(6))
xlSheet.Cells(x + 2, 7) = CStr(objdatatable.Rows(x).Item(7))
xlSheet.Cells(x + 2, 8) = CStr(objdatatable.Rows(x).Item(8))
xlSheet.Cells(x + 2, 9) = CStr(objdatatable.Rows(x).Item(10))
xlSheet.Cells(x + 2, 10) = CStr(objdatatable.Rows(x).Item(11))
xlSheet.Cells(x + 2, 11) = CStr(objdatatable.Rows(x).Item(12))
xlSheet.Cells(x + 2, 12) = CStr(objdatatable.Rows(x).Item(13))
xlSheet.Cells(x + 2, 13) = CStr(objdatatable.Rows(x).Item(14))
xlSheet.Cells(x + 2, 14) = CStr(objdatatable.Rows(x).Item(15))
'xlSheet.Cells(x + 2, 15) = CStr(objdatatable.Rows(x).Item(14))
'xlSheet.Cells(x + 2, 16) = CStr(objdatatable.Rows(x).Item(15))
Next x
xlSheet.Application.Visible = True
xlSheet.SaveAs(nama)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region

Button export diklik--------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
export()
End Sub

Button view diklik---------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
tampil()
End Sub

Button close-----------------------------------

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub

Codeng untuk form proses :

Sebelumnya tambahkan reference office dan excelnya yang versi 11

Imports System.IO
Imports System.Data
Imports System.Data.OleDb

#Region "declare"
Dim br As DataRow
Dim strsql As String
Dim mykonek As New acces.datacon
Dim insert As New acces.datainsrt
Dim urut, no, lev, master, itemmaster, desc, Qty, uom, bkolom, tkolom, purchase, labor, machine, over, extra, total As String
#End Region

memecah kemudian menyimpan ke acces--------------

#Region "proses2"
Sub proses2()
Try
objdatatable.Clear()
Dim strsql As String
strsql = "SELECT urut,Left([item],4) AS lev, Mid([item],9,14) AS itemmaster, Mid([item],33,17) AS [desc],Mid([item],19,4) AS Qty,Mid([item],24,3) AS UOM,Mid([item],50,1) AS bkolom,Mid([item],52,1) AS tkolom,Mid([item],54,12) AS purchae,Mid([item],66,13) AS labour,Mid([item],79,13) AS machine,Mid([item],92,17) AS over,Mid([item],105,13) AS extra,Mid([item],118,14) AS total from [cost]"
' 0 1 2 3 4 5 6 7 8 9 10 11 12 13
'backup
'strsql = "SELECT Left([item],4) AS lev, Mid([item],9,14) AS itemmaster, Mid([item],33,17) AS [desc],Mid([item],21,23) AS Qty,Mid([item],24,32) AS UOM,Mid([item],51,53) AS bkolom,Mid([item],53,55) AS tkolom,Mid([item],56,68) AS purchae,Mid([item],69,81) AS labour,Mid([item],82,94) AS machine,Mid([item],95,107) AS over,Mid([item],108,120) AS extra,Mid([item],123,133) AS total from [cost]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "cost")
objdatatable = objdatset.Tables("cost")
'DataGrid1.DataSource = objdatatable
mykonek.close()
Catch ex As Exception
MsgBox("kenapa ya tidak Konek" & ex.Message & vbCrLf)
End Try
Try
Dim lev, itemmaster, desc, Qty, uom, bkolom, tkolom, purchase, labor, machine, over, extra, total As String
Dim i, jum As Integer
jum = objdatatable.Rows.Count - 1
For i = 0 To objdatatable.Rows.Count - 1
With objdatatable.Rows(i)
urut = Convert.ToString(.Item(0).ToString.Trim())
lev = .Item(1).ToString.Trim
itemmaster = .Item(2).ToString
desc = .Item(3).ToString()
bkolom = .Item(6).ToString()
tkolom = .Item(7).ToString()
purchase = .Item(8).ToString()
labor = .Item(9).ToString()
machine = .Item(10).ToString()
over = .Item(11).ToString()
extra = .Item(12).ToString()
total = .Item(13).ToString()
End With
'i = i + 1
With objdatatable.Rows(i + 1)
Qty = .Item(4).ToString()
uom = .Item(5).ToString()
End With
'insert.proses2(urut, lev, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
insert.proses2(urut, lev, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
Label5.Text = "Langkah 1 sukses"
ProgressBar1.Maximum = objdatatable.Rows.Count - 1
If i <= ProgressBar1.Maximum Then
ProgressBar1.Value = i
ElseIf ProgressBar1.Maximum >= objdatatable.Rows.Count - 1 Then
ProgressBar1.Dispose()
End If
Next i
Catch ex As Exception
MsgBox("gagal konek ke database" & ex.Message & vbCrLf)
End Try
End Sub
#End Region 'langkah kedua

hapus yang tidak perlu--------------------------

#Region "hapus"
Sub hapus()
Try
'---- 0 ** 3044 901 Acco Batc Cost Leve Mult Requ S I
Dim hapus1, hapus2, hapus3, hapus4, hapus5, hapus6, hapus7, hapus8, hapus9, hapus10, hapus11 As String
hapus1 = "----"
hapus2 = "0 **"
hapus3 = "3044"
hapus4 = "901"
hapus5 = "Acco"
hapus6 = "Batc"
hapus7 = "Cost"
hapus8 = "Leve"
hapus9 = "Mult"
hapus10 = "Requ"
hapus11 = "S I"
strsql = "delete from [backcoast] where ([level])='" & hapus1 & "' or ([level])= '" & hapus2 & "' or ([level])= '" & hapus3 & "' or ([level])= '" & hapus4 & "' or ([level])= '" & hapus5 & "' or ([level])= '" & hapus6 & "' or ([level])= '" & hapus7 & "' or ([level])= '" & hapus8 & "' or ([level])= '" & hapus9 & "' or ([level])= '" & hapus10 & "' or ([level])= '" & hapus11 & "' "
'strsql = "delete from [backcoast] where ([level])='" & hapus1 & "' or ([level])= '" & hapus2 & "' or ([level])= '" & hapus7 & "' "
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objcom.ExecuteNonQuery()
'DataGrid1.DataSource = objdatatable
mykonek.close()
Label5.Text = "langkah 2 sukses"
Catch ex As Exception
MsgBox("tidak dapat menghapus" & ex.Message & vbCrLf)
End Try
End Sub
#End Region 'langkah ketiga

Insert kolom baru dengan menggunakan formula------------------------

#Region "Proses3"
Sub masterdanno()
Try
objdatatable.Clear()
Dim strsql, nos, nos1, rmaster As String
Dim i, nom As Integer
strsql = "SELECT * from [backcoast]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "backcoast")
objdatatable = objdatset.Tables("backcoast")
For i = 0 To objdatatable.Rows.Count - 1
With objdatatable.Rows(i)
urut = .Item(0).ToString
nos = .Item(1).ToString.Trim
nos1 = CInt(.Item(1).Replace(".", ""))
no = (IIf(nos1 = 0, nos1, no + 1))
rmaster = .Item(2).ToString()
nom = no.ToString
lev = .Item(1).Replace(".", "")
If nos1 = 1 Then
master = .Item(2).ToString()
ElseIf nos1 = 2 Then
master = rmaster.Insert(0, "_")
ElseIf nos1 = 3 Then
master = rmaster.Insert(0, "__")
ElseIf nos1 = 4 Then
master = rmaster.Insert(0, "___")
ElseIf nos1 = 5 Then
master = rmaster.Insert(0, "____")
Else
master = .Item(2).ToString()
End If
itemmaster = .Item(2).ToString
desc = .Item(3).ToString()
Qty = .Item(5).ToString()
uom = .Item(6).ToString()
bkolom = .Item(4).ToString()
tkolom = .Item(7).ToString()
purchase = .Item(8).ToString()
labor = .Item(9).ToString()
machine = .Item(10).ToString()
over = .Item(11).ToString()
extra = .Item(12).ToString()
total = .Item(13).ToString()
End With
insert.proses3(urut, nom, lev, master, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
Label5.Text = "Total record #" & objdatatable.Rows.Count - 1
ProgressBar1.Maximum = objdatatable.Rows.Count - 1
If i <= ProgressBar1.Maximum Then
ProgressBar1.Value = i
ElseIf ProgressBar1.Maximum >= objdatatable.Rows.Count - 1 Then
ProgressBar1.Dispose()
End If
Next i
mykonek.close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region 'langkah keempat

menampilkan hasil----------------------------

#Region "viewexport"
Sub viewexport()
objdatatable.Clear()
strsql = "SELECT * from [export] ORDER BY [urut]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "export")
objdatatable = objdatset.Tables("export")
DataGrid1.DataSource = objdatatable
mykonek.close()
End Sub
#End Region 'langkah kelima

Button proses diklik------------------------------

Private Sub btnproses_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnproses.Click
proses2()
hapus()
masterdanno()
viewexport()
End Sub

Button close klik------------------------------

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub

Codeng pada form import

Public Class import
Inherits System.Windows.Forms.Form
Dim mykonek As New acces.datacon
Dim insert As New acces.datainsrt

Sub import()-------------------------
Try
Dim oFile As System.IO.File
Dim oRead As System.IO.StreamReader
Dim EntireFile, LineIn, tempfile As String
Dim pro As Integer = 0
If txtfile.Text.Trim = "" Then
MsgBox("Cari file terlebih dahulu", MsgBoxStyle.Information, "Pesan kesalahan")
Exit Sub
End If
oRead = oFile.OpenText(txtfile.Text.Trim)
While oRead.Peek <> -1
LineIn = oRead.ReadLine()
Dim level As String = LineIn.Substring(0, 4)
Dim item As String = LineIn.Trim
insert.insrt(level, item)
Label1.Text = oRead.Peek.MaxValue
pro = pro + 1
End While
oRead.Close()
MsgBox("Import data berhasil", MsgBoxStyle.Information, "Pesan keberhasilan")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
mykonek.close()
End Try
End Sub

Button open file dialog-----------------------

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenFileDialog1.Filter = "CSV (Comma delimited) (*.csv) *.csv Text Documents(*.txt) *.txt All Files (*.*) *.*"
OpenFileDialog1.FilterIndex = 0
OpenFileDialog1.FileName = ""
Dim result As DialogResult
OpenFileDialog1.ShowDialog()
txtfile.Text = OpenFileDialog1.FileName
End Sub

Button import---------------------------------

Private Sub btnimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnimport.Click
import()
End Sub

Skenario:

1. File di ambil dari JDE berupa file dengan extensi .txt


2. mengosongkan table untuk menampung imports


3. File di import ke acces sebagai databasenya


4. File hasil import diolah


5. Hasil olahan di export ke excel

Coding langkah 2 dengan judul form pengosongan tabel

Imports System.Data
Imports System.Data.OleDb

#Region "declare"
Dim mykoneksi As New acces.datacon
Dim objcommand As OleDbCommand
Dim objdataadapter As OleDbDataAdapter
Dim objdatatable As New DataTable
Dim objdataset As New DataSet
Dim strsql As String
#End Region

#Region "import"
Sub hapusimport()
Try
objcommand = New OleDbCommand("delete from [cost]")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
'MsgBox("Hapus tabel hasil import dari JDE sukses")
Catch ex As Exception
MsgBox("koneksi ke database gagal" & ex.Message)
End Try
End Sub
#End Region

#Region "proses"
Sub hapusproses()
Try
objcommand = New OleDbCommand("delete from [backcoast]")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
'MsgBox("Hapus tabel Proses Data dari JDE sukses")
Catch ex As Exception
MsgBox("koneksi ke database gagal" & ex.Message)
End Try
End Sub
#End Region

#Region "export"
Sub hapusexport()
Try
objcommand = New OleDbCommand("delete from [export]")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
'MsgBox("Hapus tabel Export Ke Excel sukses")
Catch ex As Exception
MsgBox("koneksi ke database gagal" & ex.Message)
End Try
End Sub
#End Region

Button Kosongkan diklik

hapusimport()
hapusproses()
hapusexport()

Misal kita punya table dengan struktur

1. Kode type number
2. ket type text
3. Number type autonumber
4. hapus type text

misal kita ingin mengupdate ket dengan acuan field kode maka kita set terlebih dahulu nilai dari textbox kode menjadi integer,soalnya kalau masih bertype string maka akan terjadi error

Contoh codenya :

Diterima dari textbox dan kemudian dikirm dalam bentuk function

myinsert.update(txtkode.Text, txtket.Text, "F")

Code Function updatenya :

Public Function update(ByVal brand1 As String, ByVal ket As String, ByVal hapus As String)
Dim x As Integer = CInt(brand1)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand("update [db_brand] set brandname='" & ket & "',hapus='" & hapus & "' where brandcode= " & x & " ")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

pada number tidak disertakan karena autonumber tidak dapat diupdate.

NB: untuk field bertipe date/time tidak perlu melakukan proses conversi terlebih dahulu,sehingga langsung dikirim

Misal : tanggal tertentu dikurangi tanggal tertentu dan ditambah 1 = tertentu :

SELECT datediff(dd, cast('4/11/2008' as datetime), cast('4/12/2008' as datetime)) + 1 = 2
---------------------------------------------------------------------------------------
Misal : tanggal tertentu ditambah 1 = tertentu :

SELECT dateadd(day, 2, cast('4/11/2008' as datetime)) = '04/13/2008'

Kita akan menyimpan file bitmap ke database Ms Acces dan kemudian menampilkannya ke form,scenarionya :

1. pada gambar paling bawah adalah pada saat form diload
2. kemudian kita klik ambil gambar maka tampilan gambar seperti pada form atas
3. klik save untuk menyimpannya ke database
4. file disimpan dalam type byte
5. klik open untuk menampilkan gambar yang disimpan

-----------------------------------------------------------------

Open file dialog filter:Bmp Files(*.bmp)*.bmpGif Files(*.gif)*.gifJpg Files(*.jpg)*.jpg

Tombol Ambil gambar_clik :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenFileDialog1.ShowDialog()
PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
Exit Sub
End Sub

-----------------------------------------------------------------

Tombol save_clik :

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Dim fs As FileStream = New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
Dim s As String = TextBox1.Text
Dim r As BinaryReader = New BinaryReader(fs)
Dim FileByteArray() As Byte = r.ReadBytes(fs.Length)
Dim sql As String = "INSERT INTO Images (Filename,Photo) VALUES (?,?)"
Dim cmd As New System.Data.OleDb.OleDbCommand(sql, conn)
cmd.Parameters.Add("@Filename", System.Data.OleDb.OleDbType.VarChar).Value = TextBox1.Text
cmd.Parameters.Add("@Photo", System.Data.OleDb.OleDbType.Binary).Value = FileByteArray
conn.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Image Succesfully inserted !", "Image Save and Read", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()
End Sub

-----------------------------------------------------------------
Tombol open_clik :

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sql As String = "SELECT Photo FROM Images WHERE FileName='" & TextBox1.Text & "'"
conn.Open()
Dim objcom As New OleDbCommand(sql, conn)
rad = objcom.ExecuteReader
rad.Read()
Dim gambar() As Byte = CType(rad.Item("photo"), Byte())
Dim ms As MemoryStream = New MemoryStream(gambar)
Dim hslgambar As Drawing.Image = Bitmap.FromStream(ms)
PictureBox1.Image = New Bitmap(hslgambar)
Exit Sub
End Sub

----------------------------------------------------------
Tombol Delete_click :

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
On Error GoTo fix
Dim fn As String = InputBox("Enter name to delete")
Dim sql As String = "DELETE FROM Images WHERE FileName='" & fn & "'"
conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
MessageBox.Show("Image Succesfully deleted !", "Image Save and Read", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()
fix:
Exit Sub
End Sub

Ini adalah contoh penggunaan database dengan control textbox dan datagrid.

Scenarionya adalah apabila kita pilih add maka akan muncul idnumber automatis

1. apabila kita klik pada salah satu row di datagrid maka akan muncul pada textbox
2. apabila kita edit pada textbox maka dengan cepat datagrid akan berubah
3. apabila kita klik pada datagrid dan kita tekan tombol delete maka data akan hilang dari datagrid
4. semua hal diatas secara otomatis terupdate ke database

deklarasi :

#Region "declarations"
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.MDB")
Dim dad As New OleDbDataAdapter
Dim ds As New DataSet
Dim t As DataTable
Dim rc As Integer
Dim r As DataRow
#End Region

----------------------------------------------------------

Saat program diload :

Private Sub coba_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cn.Open()
dad = New OleDbDataAdapter("select * from [table1]", cn)
dad.Fill(ds, "mdt_emplo")
t = ds.Tables("mdt_emplo")
dg1.DataSource = t
cn.Close()
End Sub

---------------------------------------------------------

Tombol add_clik :

Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim i As Integer
Dim str As String
i = t.Rows.Count + 1
'str = t.Rows(dg1.CurrentRowIndex).Item(0)
TextBox1.Text = i
TextBox1.ReadOnly = True
End Sub

---------------------------------------------------------

Tombol delete_clik :

Private Sub btndelet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelet.Click
Dim cmd As New OleDbCommandBuilder(dad)
rc = dg1.CurrentRowIndex
t.Rows(rc).Delete()
dg1.Refresh()
MsgBox("deleted successfully")
dad.Update(ds, "emp1")
End Sub

-----------------------------------------------------------------

Tombol save_clik ;

cn.Open()
Dim cmd As New OleDbCommandBuilder(dad)
r = t.NewRow
r(0) = TextBox1.Text
r(1) = TextBox2.Text
r(2) = TextBox3.Text
r(3) = TextBox4.Text
t.Rows.Add(r)
dad.Update(ds, "mdt_emplo")
cn.Close()
End Sub

-------------------------------------------------------

Tombol edit_clik ;

Dim i As Integer
Dim str As String
Dim cmd As New OleDbCommandBuilder(dad)
str = TextBox1.Text
i = Integer.Parse(str)
r = t.Rows(i - 1)
r("empname") = TextBox2.Text
r("salary") = TextBox3.Text
r("location") = TextBox4.Text
dad.Update(ds, "mdt_emplo")
MsgBox("sukses update")

-----------------------------------------------------------------

salah satu row Datagrid diselect :

Private Sub dg1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dg1.MouseUp
rc = dg1.CurrentRowIndex
r = t.Rows(rc)
With t.Rows(rc)
TextBox1.Text = .Item(0)
TextBox2.Text = .Item(1)
TextBox3.Text = .Item(2)
TextBox4.Text = .Item(3)
End With
End Sub

String koneksi :

Imports System.Data.OleDb
Module Module1
Public Con As New ADODB.Connection
Public Sub Connection()
Con = New ADODB.Connection
Con.Open("Provider = MICROSOFT.JET.OLEDB.4.0;data Source=DataBase.MDB ")
End Sub
End Module

-------------------------------------------
dim RsSave as adodb.recordset
dim RsSearch as adodb.recordset
-------------------------------------------------
Save dan update record :


Private Sub SaveRecord()
RsSave.Fields("identitas").Value = txtserial.Text
RsSave.Fields("Nama").Value = txtname.Text
RsSave.Fields("UserID").Value = txtuser.Text
RsSave.Fields("Password").Value = txtpass.Text
RsSave.Fields("Label").Value = cmbstatus.Text
RsSave.Fields("tgl").Value = txtdate.Text
End Sub

-------------------------------------------------
Button save_click :

RsSave = New ADODB.Recordset
RsSave.Open("select * from Logon", Con, 1, 2)
RsSave.AddNew()
Call SaveRecord()
RsSave.Update()
MsgBox("Sukses menambahkan record")

------------------------------------------------
Button Edit_click :

RsSave = New ADODB.Recordset
ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
RsSave.Open("select * from Logon where SerialNo=" & (txtid.Text), Con, 1, 2)
Call SaveRecord()
RsSave.Update()
MsgBox("Record update successfull")

-----------------------------------------------
Button Delete_ click :

Con.Execute("delete from Logon where SerialNo=" & Trim(txtid.Text) & "")
txtserial.Text = ""
txtname.Text = ""
txtuser.Text = ""
txtpass.Text = ""
cmbstatus.Text = ""
txtdate.Text = ""
RsSearch.MoveNext()
Call DisplayRecord()

-----------------------------------------------
Prosedur tampil data :

Sub DisplayRecord()

txtid.Text = RsSearch.Fields("identitas").Value
txtnama.Text = RsSearch.Fields("nama").Value
txtuser.Text = RsSearch.Fields("user").Value
txtpassword.Text = RsSearch.Fields("Password").Value
cmbposisi.Text = RsSearch.Fields("Label").Value
txttgl.Text = RsSearch.Fields("tgl").Value

End Sub

------------------------------------------------------
Control Navigasi :

Last :

RsSearch = New ADODB.Recordset
RsSearch.Open("select * from Logon", Con, 1, 2)
RsSearch.MoveLast()
Call DisplayRecord()

Next :

RsSearch.MoveNext()
Call DisplayRecord()

Previous :

RsSearch.MovePrevious()
Call DisplayRecord()