Oleh: admin | 25/05/2012

Source Code Pencarian Data pada Database

Kali ini saya memposting bagaimana cara melakukan pencarian data pada database access 2003/2007 dengan menggunakan vb6. Berikut screen form pencarian data yang dirancang :

Berikut source codenya :

Private Sub cmdControl1_Click()
Koneksi.Close
End
End Sub

Private Sub cmdControl2_Click()
FrmInfoLengkap.Show 1
End Sub

Private Sub Form_Load()
bukadatabase
opt1 = True
MengaturKolomListViewR
xp.InitSubClassing
 stb.Panels(1).Text = "http://primasoftware.weebly.com"
 stb.Panels(1).Width = 3000
 stb.Panels(2).Text = Format(Date, "Dddd, dd Mmmm yyyy")
 stb.Panels(2).Alignment = sbrCenter
 stb.Panels(2).Width = 2700
 stb.Panels(3).Text = "Aplikasi Alumni v1.0"
 stb.Panels(3).Width = 6000
 Combo1.AddItem "---kriteria pencarian---"
 Combo1.AddItem "Nama Lengkap"
 Combo1.AddItem "Alamat Asal"
 Combo1.ListIndex = 0
End Sub
Private Sub MengaturKolomListViewR()
With ListView1.ColumnHeaders
.Clear
.Add , , "No.", 500
.Add , , "No.Reg.", 1200, 2
.Add , , "Nama Siswa", 5000
.Add , , "Tempat Lahir", 1500
.Add , , "Tgl.Lahir", 1500
.Add , , "L/P", 500, 2
.Add , , "Alamat Asal", 1500
.Add , , "Alamat Sekarang", 1500
.Add , , "Tgl.Masuk", 1500, 2
.Add , , "Jam Masuk", 1250, 2
.Add , , "Paket", 2500
.Add , , "Status", 1500
End With
End Sub
Private Sub MengaturKolomListViewM()
With ListView1.ColumnHeaders
.Clear
.Add , , "No.", 500
.Add , , "NIS.", 1200, 2
.Add , , "Nama Madyasiswa", 5000
.Add , , "Tempat Lahir", 1500
.Add , , "Tgl.Lahir", 1500
.Add , , "L/P", 500, 2
.Add , , "Jurusan", 1500
.Add , , "No.Sertifikat", 1500
.Add , , "Tahun Masuk", 1500, 2
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Koneksi.Close
End Sub
Private Sub tampildatareguler()
   
    ListView1.ListItems.Clear
    Set Rstampil = New ADODB.Recordset
    Rstampil.Open "Select noreg,namasiswa,t4,tgllahir,jk,alamatasal,alamatsek,tglmulai,jammasuk,paket,status From tblsiswa where status= 'Selesai' order by noreg asc", Koneksi
    If Rstampil.RecordCount <> 0 Then
    ListView1.ListItems.Clear
    Rstampil.MoveFirst
    Do Until Rstampil.EOF
    i = i + 1
    With ListView1.ListItems.Add
    .Text = i
    .SubItems(1) = Rstampil.Fields(0)
    .SubItems(2) = Rstampil.Fields(1)
    .SubItems(3) = Rstampil.Fields(2)
    .SubItems(4) = Rstampil.Fields(3)
    .SubItems(5) = Rstampil.Fields(4)
    .SubItems(6) = Rstampil.Fields(5)
    .SubItems(7) = Rstampil.Fields(6)
    .SubItems(8) = Rstampil.Fields(7)
    .SubItems(9) = Rstampil.Fields(8)
    .SubItems(10) = Rstampil.Fields(9)
    .SubItems(11) = Rstampil.Fields(10)
    End With
    Rstampil.MoveNext
    Loop
    ceksiswaselesai
    End If
End Sub
Private Sub ceksiswaselesai()
Set rscari2 = New ADODB.Recordset
rscari2.Open "Select count(*) as jml2 From tblsiswa where status='Selesai'", Koneksi
Label1.Caption = "Total Alumni : " + Format(rscari2!jml2)
End Sub
Private Sub cekmadyasiswaselesai()
Set rscari1 = New ADODB.Recordset
rscari1.Open "Select count(*) as jml1 From tblalumni ", Koneksi
Label1.Caption = "Total Alumni : " + Format(rscari1!jml1)
End Sub
Private Sub opt1_Click()
MengaturKolomListViewR
tampildatareguler
End Sub
Private Sub tampildatamadyasiswa()
   
    ListView1.ListItems.Clear
    Set Rstampil = New ADODB.Recordset
    Rstampil.Open "Select tblalumni.nis,tblmadyasiswa.nama,tblmadyasiswa.t4_lahir,tblmadyasiswa.tgl_lahir,tblmadyasiswa.jns_kel,tbljurusan.nama,tblalumni.no_sertifikat,tblalumni.ta From tblmadyasiswa,tblalumni,tbljurusan where tblalumni.nis=tblmadyasiswa.nis and tblmadyasiswa.kode_jur= tbljurusan.kode_jur order by tblmadyasiswa.nis asc", Koneksi
    If Rstampil.RecordCount <> 0 Then
    ListView1.ListItems.Clear
    Rstampil.MoveFirst
    Do Until Rstampil.EOF
    i = i + 1
    With ListView1.ListItems.Add
    .Text = i
    .SubItems(1) = Rstampil.Fields(0)
    .SubItems(2) = Rstampil.Fields(1)
    .SubItems(3) = Rstampil.Fields(2)
    .SubItems(4) = Rstampil.Fields(3)
    .SubItems(5) = Rstampil.Fields(4)
    .SubItems(6) = Rstampil.Fields(5)
    .SubItems(7) = Rstampil.Fields(6)
    .SubItems(8) = Rstampil.Fields(7)
 
    End With
    Rstampil.MoveNext
    Loop
    cekmadyasiswaselesai
    End If
End Sub

Private Sub opt2_Click()
MengaturKolomListViewM
tampildatamadyasiswa
End Sub
Private Sub carimadyasiswa()
   
If Combo1.ListIndex = 1 Then
    ListView1.ListItems.Clear
    Set Rstampil = New ADODB.Recordset
    Rstampil.Open "Select tblalumni.nis,tblmadyasiswa.nama,tblmadyasiswa.t4_lahir,tblmadyasiswa.tgl_lahir,tblmadyasiswa.jns_kel,tbljurusan.nama,tblalumni.no_sertifikat,tblalumni.ta From tblmadyasiswa,tblalumni,tbljurusan where tblalumni.nis=tblmadyasiswa.nis and tblmadyasiswa.kode_jur= tbljurusan.kode_jur and tblmadyasiswa.nama like '" & Text1.Text & "%' order by tblmadyasiswa.nis asc", Koneksi
    If Rstampil.RecordCount <> 0 Then
    ListView1.ListItems.Clear
    Rstampil.MoveFirst
    Do Until Rstampil.EOF
    i = i + 1
    With ListView1.ListItems.Add
    .Text = i
    .SubItems(1) = Rstampil.Fields(0)
    .SubItems(2) = Rstampil.Fields(1)
    .SubItems(3) = Rstampil.Fields(2)
    .SubItems(4) = Rstampil.Fields(3)
    .SubItems(5) = Rstampil.Fields(4)
    .SubItems(6) = Rstampil.Fields(5)
    .SubItems(7) = Rstampil.Fields(6)
    .SubItems(8) = Rstampil.Fields(7)
 
    End With
    Rstampil.MoveNext
    Loop
    cekmadyasiswaselesai
    End If
End If
'-----------
If Combo1.ListIndex = 2 Then
    ListView1.ListItems.Clear
    Set Rstampil = New ADODB.Recordset
    Rstampil.Open "Select tblalumni.nis,tblmadyasiswa.nama,tblmadyasiswa.t4_lahir,tblmadyasiswa.tgl_lahir,tblmadyasiswa.jns_kel,tbljurusan.nama,tblalumni.no_sertifikat,tblalumni.ta From tblmadyasiswa,tblalumni,tbljurusan where tblalumni.nis=tblmadyasiswa.nis and tblmadyasiswa.kode_jur= tbljurusan.kode_jur and tblmadyasiswa.alamat_asal like '" & Text1.Text & "%' order by tblmadyasiswa.nis asc", Koneksi
    If Rstampil.RecordCount <> 0 Then
    ListView1.ListItems.Clear
    Rstampil.MoveFirst
    Do Until Rstampil.EOF
    i = i + 1
    With ListView1.ListItems.Add
    .Text = i
    .SubItems(1) = Rstampil.Fields(0)
    .SubItems(2) = Rstampil.Fields(1)
    .SubItems(3) = Rstampil.Fields(2)
    .SubItems(4) = Rstampil.Fields(3)
    .SubItems(5) = Rstampil.Fields(4)
    .SubItems(6) = Rstampil.Fields(5)
    .SubItems(7) = Rstampil.Fields(6)
    .SubItems(8) = Rstampil.Fields(7)
 
    End With
    Rstampil.MoveNext
    Loop
    cekmadyasiswaselesai
    End If
End If
End Sub
Private Sub carireguler()

If Combo1.ListIndex = 1 Then
    ListView1.ListItems.Clear
    Set Rstampil = New ADODB.Recordset
    Rstampil.Open "Select noreg,namasiswa,t4,tgllahir,jk,alamatasal,alamatsek,tglmulai,jammasuk,paket,status From tblsiswa where status= 'Selesai' and namasiswa like '" & Text1.Text & "%' order by noreg asc", Koneksi
    If Rstampil.RecordCount <> 0 Then
    ListView1.ListItems.Clear
    Rstampil.MoveFirst
    Do Until Rstampil.EOF
    i = i + 1
    With ListView1.ListItems.Add
    .Text = i
    .SubItems(1) = Rstampil.Fields(0)
    .SubItems(2) = Rstampil.Fields(1)
    .SubItems(3) = Rstampil.Fields(2)
    .SubItems(4) = Rstampil.Fields(3)
    .SubItems(5) = Rstampil.Fields(4)
    .SubItems(6) = Rstampil.Fields(5)
    .SubItems(7) = Rstampil.Fields(6)
    .SubItems(8) = Rstampil.Fields(7)
    .SubItems(9) = Rstampil.Fields(8)
    .SubItems(10) = Rstampil.Fields(9)
    .SubItems(11) = Rstampil.Fields(10)
    End With
    Rstampil.MoveNext
    Loop
    ceksiswaselesai
    End If
End If
'--------------
If Combo1.ListIndex = 2 Then
    ListView1.ListItems.Clear
    Set Rstampil = New ADODB.Recordset
    Rstampil.Open "Select noreg,namasiswa,t4,tgllahir,jk,alamatasal,alamatsek,tglmulai,jammasuk,paket,status From tblsiswa where status= 'Selesai' and alamatasal like '" & Text1.Text & "%' order by noreg asc", Koneksi
    If Rstampil.RecordCount <> 0 Then
    ListView1.ListItems.Clear
    Rstampil.MoveFirst
    Do Until Rstampil.EOF
    i = i + 1
    With ListView1.ListItems.Add
    .Text = i
    .SubItems(1) = Rstampil.Fields(0)
    .SubItems(2) = Rstampil.Fields(1)
    .SubItems(3) = Rstampil.Fields(2)
    .SubItems(4) = Rstampil.Fields(3)
    .SubItems(5) = Rstampil.Fields(4)
    .SubItems(6) = Rstampil.Fields(5)
    .SubItems(7) = Rstampil.Fields(6)
    .SubItems(8) = Rstampil.Fields(7)
    .SubItems(9) = Rstampil.Fields(8)
    .SubItems(10) = Rstampil.Fields(9)
    .SubItems(11) = Rstampil.Fields(10)
    End With
    Rstampil.MoveNext
    Loop
    ceksiswaselesai
    End If
End If
End Sub
Private Sub Text1_Change()
On Error Resume Next
If opt1 = True Then
   carireguler
End If
If opt2 = True Then
   carimadyasiswa
End If
End Sub


Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

Kategori

%d blogger menyukai ini: