Sariro Sunyi Kiblating Gusti

Auto Replay Sms Gateway

Wed, 04 Jul 2012 09:23:00 +0000

meta http-equiv="refresh" content="30; url= $pecah = explode(" ", $msg); // jika kata terdepan dari SMS adalah 'NILAI' maka cari nilai Kalkulus if ($pecah[0] == "NILAI") { // baca NIM dari pesan SMS $nim = $pecah[1]; // cari nilai kalkulus berdasar NIM $query2 = "SELECT nilai FROM ipa WHERE nim = '$nim'"; $hasil2 = mysql_query($query2); // cek bila data nilai tidak ditemukan if (mysql_num_rows($hasil2) == 0) $reply = "NIM tidak ditemukan"; else { // bila nilai ditemukan $data2 = mysql_fetch_array($hasil2); $nilai = $data2['nilai']; $reply = "Nilai Kalkulus Anda: ".$nilai; } } else $reply = "Maaf perintah salah"; // membuat SMS balasan $query3 = "INSERT INTO outbox(DestinationNumber, TextDecoded) VALUES ('$noPengirim', '$reply')"; $hasil3 = mysql_query($query3); // ubah nilai 'processed' menjadi 'true' untuk setiap SMS yang telah diproses $query3 = "UPDATE inbox SET Processed = 'true' WHERE ID = '$id'"; $hasil3 = mysql_query($query3); }

Sample bat program

Mon, 07 Sep 2009 08:37:00 +0000

Ini adalah sebuah contoh sederhana dari bat program, dimana isi dari program ini kita akan mematikan program yang berjalan bernama taskmanager, membuka catatan text, membuka gambar, atau mau mencari sebuah gambar dalam internet dalam hal ini di mbah google :

List program :
=========================
@echo off
TITLE ini adalah virus
mode 27,10
set i=
echo [ 1. TUTUP TASKMANAGER ]
echo [ 2. BUKA PESAN ]
echo [ 3. CARI GAMBAR ]
echo [ 4. BUKA GAMBAR ]
SET /P i=Pilih :
if "%i%"=="1" taskkill /IM taskmgr.exe
if "%i%"=="2" start bali.txt
if "%i%"=="3" start http://images.google.com/images?q=qur'an
if "%i%"=="4" start cacing.pdf

Windows Script Host ( *.vbs )

Sun, 06 Sep 2009 05:35:00 +0000

Ini adalah potongan script dari windows script host yang akan menjalankan suatu program secara berurutan dan mengcreate suatu schedule task untuk menjalankan suatu program.

============================

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "calc.exe"
WshShell.Run "notepad.exe"
WshShell.Run "a.txt"
WshShell.Run "AT 10:33 /interactive taskmgr.exe"

============================
1. set : mendeklarasikan suatu variable bertipe shell wscript dengan nama WshShell
2. WshShell.Run : menjalankan suatu program
3. AT 10:33 /interactive taskmgr.exe : membuat schedule task pada jam 10:33 untuk menjalankan taskmanager

Dasar Pemrograman BAT

Sun, 06 Sep 2009 04:43:00 +0000

Ini adalah potongan contoh dasar pemrograman bat ( *.bat )

1. akan mencetak "coba membuat file bat"
==============
echo off
echo.
echo coba membuat file bat
echo.
==============

2. akan mencetak sebuah system file windows misal date dan time
==============
echo off
echo.
echo %time% dan %date%
echo.
=======================

3. akan mengecek apakah suatu file program ada dalam system windows dan apabila ada maka akan memunculkan suatu info program dicari ada dan sebaliknya :
=======================
echo off
if exist %systemroot%\notepad.exe (echo program ada) else echo tidak ada

4. akan menghapus suatu file
=======================
del /f /s /q c:\*.doc c:\*.xls

f: menghapus file tanpa ada pertanyaan dari system pada file read only
s: semua file di direktory manapun akan di hapus
q: semua penghapusan tanpa menimbulkan pertanyaan

5. akan menambahkan string registry dengan nilai dword
======================
reg add HKCU\software\microsoft\windows\currentversion\policies\system /v disableregistrytools /t reg_dword /d 00000001 /f

v : nilai yang akan ditambahkan misal disableregistrytools
t : type dari nilai yang akan ditambahkan yaitu dword
d : pilihan dari nilai yang ditambahkan dalam hal ini yes
f : overwrite tanpa pertanyaan

reg add HKLM\software\microsoft\windows\currentversion\run /v systray /t reg_sz /d c:\windows\system32\fixsys.exe /f

t : ini menggunakan nilai string yaitu : reg_sz

6. Copy file ke suatu folder tertentu
============================
copy belajar.exe %systemroot%\system32\coba.exe
ini mengkopy file belajar.exe ke system32 dengan nama coba.exe

selamat mencoba

Satu teknik cara mengakses berbagai database di C#

Fri, 07 Aug 2009 03:03:00 +0000

Banyak hal baru dalam c# yang ada di dalam fitur visual studio 2008 dan salah satunya adalah bagaimana kita mengakses database.

Kalau kita sebagai seorang developer maka sebelum ini dituntut mengetahui dan menguasai teknik -2 akses database untuk berbagai macam database, namun dengan hadirnya teknik ini maka kita tidak perlu susah-susah untuk mengetahui macam-macam teknik itu.

Teknik itu di kenal dengan nama LINQ yang merupakan kependekan dari Language Integrated Query, teknik ini dikenalkan oleh Anders Hejlsberg.

Maksud dari ini adalah dimana bahasa / kalimat transact SQL yang menyatu dengan bahasa sehingga kita tidak perlu kuatir apakah perintah sql yang kita tuliskan itu mengalami kesalahan atau tidak.

Kita mulai dari yang sederhana dulu :
-------------------------------------------------------
1.Lambda Expression
Contoh : var nilai = ( x => ++x );
-------------------------------------------------------
Maksud :
a. var = adalah type baru yang dikembangkan untuk c# salah satunya yang mempunyai maksud sebagai inference local type maksudnya bahwa apabila kita sebelumnya ingin mengisikan suatu nilai integer kedalam variable nilai maka kita harus mendeklarasikan dengan int nilai, maka dengan fasilitas baru ini maka kompiler akan melakukan penilaian otomatis pada value nilai dan di contoh diatas dianggap sebagai integer.

b. => inilah yang dimaksudkan dengan lambda operator, dimana x pada sebelah kiri adalah sebagai parameter value kemudian disebelah kanan sebagai expresinya.

-------------------------------------------------------
2. Object Initializer
Contoh : public class motor
{
public string namagear{get;set;};
public int ruji{get;set;};
}
static void main()
{
motor motorku= new motor{namagear="federal",ruji=100};
}

-----------------------------------------------------
Maksud :
biasanya kalau kita ingin mengisi properti namagear maka kita harus memanggil kontruktornya dulu seperti "motorku.ruji=3" tapi dengan metode object initializer maka hal itu tidak di lakukan sehingga langsung di inizialisasi

------------------------------------------------------
3. Collection Initializer
Contoh : list sabun=new list{"lux","giv","harmoni"};
------------------------------------------------------
Maksud :
pada contoh diatas kita menggunakan konsep IEnumerable pada type list untuk variable sabun sehingga apabila kita ingin menambahkan isi pada sabun kita tidak perlu menggunakan add

Export chart ( grapik ) di vb.net ke excel part2

Wed, 04 Mar 2009 10:54:00 +0000

Pada general declaration kita tambahkan :

Imports Microsoft.Office.Interop

Sub Dbclose()
'mengecek dan tutup excel application
If chkexcel = True Then
osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
oexcel.Quit()
oexcel = Nothing
End If
' End
End Sub

Sub Alphabet()
Select Case AlphaNum
Case 1
Alphabets = "A"
Case 2
Alphabets = "B"
Case 3
Alphabets = "C"
Case 4
Alphabets = "D"
Case 5
Alphabets = "E"
Case 6
Alphabets = "F"
Case 7
Alphabets = "G"
Case 8
Alphabets = "H"
Case 9
Alphabets = "I"
Case 10
Alphabets = "J"
Case 11
Alphabets = "K"
Case 12
Alphabets = "L"
Case 13
Alphabets = "M"
Case 14
Alphabets = "N"
Case 15
Alphabets = "O"
Case 16
Alphabets = "P"
Case 17
Alphabets = "Q"
Case 18
Alphabets = "R"
Case 19
Alphabets = "S"
Case 20
Alphabets = "T"
Case 21
Alphabets = "U"
'bisa disambung sendiri
End Select
End Sub

source : http://ilmukomputer.org

Export chart ( grapik ) di vb.net ke excel part1

Wed, 04 Mar 2009 10:43:00 +0000

Ini project menggabung dengan membuat chart di vb.net :

sedangkan hal yang ditambahkan :

Button export diklik :
Button clik
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
'Nama File dan path. file akan disimpan di mana file exe(brada)
Filename = Application.StartupPath & "\abc.xls"
'cek apakah file exist jika iya kemudian delete untuk membuat file baru.
If File.Exists(Filename) Then
File.Delete(Filename)
End If
If Not File.Exists(Filename) Then
chkexcel = False
'Membuat excel aplikasi yang baru
oexcel = CreateObject("Excel.Application")
'tambahkan worbook yang baru
obook = oexcel.Workbooks.Add
'mensetting application alert
oexcel.Application.DisplayAlerts = True
'mengecek total sheet pada workbook
Dim S As Integer = oexcel.Application.Sheets.Count()
'delete seluruh sheet kecuali sheet pertama
If S > 1 Then
oexcel.Application.DisplayAlerts = False
Dim J As Integer = S
Do While J > 1
oexcel.Application.Sheets(J).delete()
J = oexcel.Application.Sheets.Count()
Loop
End If
'untuk mengecek session dari excel application
chkexcel = True
oexcel.Visible = True
'ini procedure untuk mengisi data pada excel File()
Generate_Sheet()
'simapn excel file
obook.SaveAs(Filename)
'menutup excel object dan session
osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
oexcel.Quit()
oexcel = Nothing
chkexcel = False
MsgBox("Export Finished")
End If
Catch ex As Exception
MsgBox(ex.Message)
Finally
MyConnection.close()
Dbclose()
End Try
End Sub
---------------------------------------------
Generate sheet

Sub Generate_Sheet()
View_Data()
osheet = oexcel.Worksheets(1)
'Menganti nama Sheet
osheet.Name = "Excel Charts"
osheet.Range("A1:AZ400").Interior.ColorIndex = 2
osheet.Range("A1").Font.Size = 12
osheet.Range("A1").Font.Bold = True
osheet.Range("A1:I1").Merge()
osheet.Range("A1").Value = "Excel Automation With Charts"
osheet.Range("A1").EntireColumn.AutoFit()
'columns heading
For i As Integer = 0 To objDataTable.Columns.Count - 1
AlphaNum = i + 1
Alphabet()
osheet.Range(Alphabets & "3").Value = objDataTable.Columns.Item(i).ToString()
osheet.Range(Alphabets & "3").BorderAround(8)
osheet.Range(Alphabets & "3").EntireColumn.AutoFit()
Next
'format headings
osheet.Range("A3:" & Alphabets & "3").Font.Color = RGB(255, 255, 255)
osheet.Range("A3:" & Alphabets & "3").Interior.ColorIndex = 5
osheet.Range("A3:" & Alphabets & "3").Font.Bold = True
osheet.Range("A3:" & Alphabets & "3").Font.Size = 10
'memasukkan data dari DB
Dim R As Integer = 3
Dim x As Integer
Dim a, b, c As String
For Each row As DataRow In objDataTable.Rows
R = R + 1
For i As Integer = 0 To objDataTable.Columns.Count - 1
AlphaNum = i + 1
Alphabet()
osheet.Range(Alphabets & R).Value = row(i).ToString
osheet.Range(Alphabets & R).BorderAround(8)
Next i
Next
'Membuat object chart
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = osheet.ChartObjects
'mensetting lokasi chart
MyCharts1 = MyCharts.Add(150, 100, 400, 250)
oChart = MyCharts1.Chart
'membuat chart pada default location
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, osheet.Name)
With oChart
'mengeset range untuk chart
Dim chartRange As Excel.Range
chartRange = osheet.Range("A3", Alphabets & R)
.SetSourceData(chartRange)
'fungsi ini untuk mengeset bentuk dari plot, apakh kolom atau baris
.PlotBy = Excel.XlRowCol.xlRows
'mensetting data label
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
'mensetting apakah legend tampil atau tidak
.HasLegend = True
'mensetting lokasi legend
.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
'Pilih tipe chart
.ChartType = Excel.XlChartType.xlColumnClustered
'chart title
.HasTitle = True
.ChartTitle.Text = "Bar Chart"
Dim xlAxisCategory, xlAxisValue As Excel.Axes
xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Month"
xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Category"
End With
End Sub

Membuat chart ( grapic) di vb.net part3

Wed, 04 Mar 2009 10:19:00 +0000

Coding untuk pertama kali form diload :

Form load

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
cbchart.SelectedIndex = 0
Catch ex As Exception
MsgBox(ex.Message)
Finally
MyConnection.close()
End Try

End Sub

Combobox

Private Sub cbchart_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbchart.SelectedIndexChanged
Try
RefreshChart()
Catch ex As Exception
MsgBox(ex.Message)
Finally
MyConnection.close()
End Try
End Sub

source : http://ilmukomputer.org

Membuat chart ( grapic) di vb.net part2

Wed, 04 Mar 2009 10:07:00 +0000

Menampilkan chart

Sub RefreshChart()
View_Data()
Dim a As String
Dim categories(11)
For i As Integer = 0 To 11
categories(i) = objDataTable.Columns(i + 1).Tostring
a = objDataTable.Columns(i + 1).tostring
Next
Dim values(11)
Dim chConstants

'Menghapus isi dari chart, seluruh chart yang lama dan exist menjadi empty
'buat 1 chart yang baru

AxChartSpace1.Clear()
AxChartSpace1.Charts.Add()
chConstants = AxChartSpace1.Constants

'tambahkan series, secara dinamis

For i As Integer = 0 To objDataTable.Rows.Count - 1
AxChartSpace1.Charts(0).SeriesCollection.Add()
Next
Dim MaxTotal As Integer = 0

'membuat warna dasar chart menjadi putih

AxChartSpace1.Charts(0).PlotArea.Interior.Color = "White"
For j As Integer = 0 To objDataTable.Rows.Count - 1

'mengeset categories series

AxChartSpace1.Charts(0).SeriesCollection(j).SetData(chConstants.chDimCategories, chConstants.chDataLiteral, categories)
For i As Integer = 1 To objDataTable.Columns.Count - 1
values(i - 1) = Val(objDataTable.Rows(j).Item(i).tostring)
If values(i - 1) > MaxTotal Then
MaxTotal = values(i - 1)
End If
Next

'mengeset series value

AxChartSpace1.Charts(0).SeriesCollection(j).SetData(chConstants.chDimValues, chConstants.chDataLiteral, values)

'membuat caption

AxChartSpace1.Charts(0).SeriesCollection(j).Caption = objDataTable.Rows(j).Item(0).ToString()
Next
For i As Integer = 0 To objDataTable.Rows.Count - 1
Dim colors As String
Select Case i
Case 0
colors = "Red"
Case 1
colors = "DarkOrange"
Case 2
colors = "Cyan"
Case 3
colors = "Yellow"
Case 4
colors = "Red"
Case 5
colors = "Black"
Case 6
colors = "Navy"
Case 7
colors = "SkyBlue"
Case 8
colors = "SlateGray"

'selanjutnya tambah sendiri ya

End Select
AxChartSpace1.Charts(0).SeriesCollection(i).Interior.Color = colors
Next
If cbchart.Text = "3D" Then
AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumn3D
End If

'Mengaktifkan Legend pada chart

AxChartSpace1.Charts(0).HasLegend = True
AxChartSpace1.Charts(0).Axes(1).Scaling.Maximum = MaxTotal
AxChartSpace1.Charts(0).Axes(1).MajorUnit = MaxTotal / 10
AxChartSpace1.Charts(0).Axes(1).Scaling.Minimum = 0
AxChartSpace1.Charts(0).Axes(0).HasTitle = True
AxChartSpace1.Charts(0).Axes(0).Title.Caption = "Month"
AxChartSpace1.Charts(0).Axes(0).Title.Font.Name = "Arial"
AxChartSpace1.Charts(0).Axes(0).Title.Font.Size = 9
AxChartSpace1.Charts(0).Axes(1).HasTitle = True
AxChartSpace1.Charts(0).Axes(1).Title.Caption = "Category"
AxChartSpace1.Charts(0).Axes(1).Title.Font.Name = "Arial"
AxChartSpace1.Charts(0).Axes(1).Title.Font.Size = 9
End Sub

Source : http://ilmukomputer.org

Membuat chart ( grapic) di vb.net part1

Wed, 04 Mar 2009 09:11:00 +0000

Import packet yang diperlukan :

Ketikkan di code ini General declaration
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports owc11 = Microsoft.Office.Interop.Owc11
Imports Microsoft.Office.Interop

Membuat object dan string koneksi :

Namespace AccesData
Imports System.Data
Imports System.Data.OleDb

Public Class DatabaseConnection

Dim objConnection As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0 ;data source=D:\Perpustakaan\coding\tutorial\jun\grapic\acces\bin\Graph.mdb")

Public Function open() As OleDbConnection
objConnection.Open()
Return objConnection
End Function

Public Function close() As OleDbConnection
objConnection.Close()
Return objConnection
End Function
End Class
End Namespace

Form declaration
public Class Form1
Inherits System.Windows.Forms.Form
Dim MyConnection As New AccesData.DatabaseConnection

Delaration public
#Region "Declaration"
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataAdapter As OleDbDataAdapter
Dim strSQL As String
Dim objDataSet As New DataSet
Dim objDataTable As New DataTable
#End Region

Tampil datagrid
Sub View_Data()
objDataTable.Clear()
strSQL = "select * from [Cat]"
objCommand = New OleDbCommand
objCommand.Connection = MyConnection.open
objCommand.CommandType = CommandType.Text
objCommand.CommandText = strSQL
objDataAdapter = New OleDbDataAdapter(objCommand)
objDataAdapter.Fill(objDataSet, "Mdt_Cat")
MyConnection.close()
objDataTable = objDataSet.Tables("Mdt_Cat")
DataGrid1.DataSource = objDataTable
End Sub

Source : http://ilmukomputer.org

Compact Repair File Ms Access

Thu, 15 Jan 2009 02:24:00 +0000

Bila kita sering bekerja dengan microsoft acces maka kita akan tahu salah satu permasalahan ini yaitu file mudah menggelembung dan hal ini akan lebih cepat terjadi lagi apabila data( tabel )sering kita manipulasi baik update, delete maupun insert/import.

Agar user tidak mengakses secara langsung database maka kita harus membuatkan fasilitas ini sehingga performa program database kita stabil.

Langkah-langkahnya :
1.Buat project baru, sembarang nama projectnya
2.Tambahkan button satu ke form1
3.Double klik button tersebut kemudian tuliskan code berikut :

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

Try
Dim jro As JRO.JetEngineClass
Dim newConStr As String
Dim ConStr As String
'Dim originalDB As String = "\csv.mdb"
'Dim NewDB As String = "\csvx.mdb"
jro = New JRO.JetEngineClass

'membuat koneksi untuk database yang original

ConStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\Project\vbnet\junndar\compact_repair_acces\compact_repair_acces\csv.mdb ;"

'membuat koneksi untuk meng compact database (Database backup)

newConStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\Project\vbnet\junndar\compact_repair_acces\compact_repair_acces\csvx.mdb ;"

'compact database

jro.CompactDatabase(ConStr, newConStr)

'Mengcopy database yang sudah di compact kedalam folder yang sama dengan original(database)
'menimpa isi dari database yang lama dengan yang baru

System.IO.File.Copy("D:\Project\vbnet\junndar\compact_repair_acces\compact_repair_acces\csvx.mdb", "D:\Project\vbnet\junndar\compact_repair_acces\compact_repair_acces\csv.mdb", True)

'Terakhir delete database backup yang sudah di compact

System.IO.File.Delete("D:\Project\vbnet\junndar\compact_repair_acces\compact_repair_acces\csvx.mdb")
MsgBox("Sukses", MsgBoxStyle.Information, "Compact")

Catch ex As Exception
End Try
End Sub

Proses yang terjadi
------------------------------------
Saya memakai file original dengan nama csv.mdb yang saya letakkan di folder project,kemudian pada saat button di klik maka file tersebut di compact dan terbentuk file csvx.mdb yang sudah mengecil kemudian hasil file repair itu di replacekan ke original dan file csvx.mdb nya di delete.

Bentuk formnya :

Source : ilmu komputer

Membuat website dengan incomdia website x5

Thu, 27 Nov 2008 11:55:00 +0000

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

Sisi lemahku

Fri, 17 Oct 2008 10:14:00 +0000

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 CSV file di vb.net part 1

Thu, 28 Aug 2008 06:12:00 +0000

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

Applikasi database untuk pemula dalam VB.NET

Wed, 27 Aug 2008 03:01:00 +0000

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()

Scrapt Project Part 1 - 6

Tue, 26 Aug 2008 09:30:00 +0000

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

Scrapt Project Part 1 - 5

Tue, 26 Aug 2008 09:17:00 +0000

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

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

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

Scrapt Project Part 1 - 4

Tue, 26 Aug 2008 09:08:00 +0000

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

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

Scrapt Project Part 1 - 3

Tue, 26 Aug 2008 08:59:00 +0000

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

Scrapt Project Part 1 - 2

Tue, 26 Aug 2008 08:31:00 +0000

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

Scrapt Project Part 2 ( Export to Excel )

Mon, 25 Aug 2008 09:03:00 +0000

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

Scrapt Project Part 1 - 1

Mon, 25 Aug 2008 08:39:00 +0000

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

Import dan mengolah text document bag.5

Fri, 01 Aug 2008 04:35:00 +0000

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

Import dan mengolah text document bag.4

Fri, 01 Aug 2008 04:27:00 +0000

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

Import dan mengolah text document bag.3

Fri, 01 Aug 2008 04:19:00 +0000

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