Database Programming with Visual Basic 6

Tuesday, November 23, 2010 by: Andy Kurnia Prayoga Made

Description: The following program is an example of database programming with visual basic 6. Database using SQL Server 2000. This time I will take the example of the Master Doctor in a hospital. The program is very simple and easy to learn. The process that is in it is quite complex that contains the search, add, delete and update data. This program is perfect for learning your new database programming in visual basic. This program uses only one table. The components used were the standard components provided by Visual Basic 6. Before making this program you must first design the database in SQL Server 2000 query analyzer, the following Transact from the database program:

create database rumah_sakit

use rumah_sakit

create table dokter
(
 nip varchar(12),
 nama varchar(100),
 tgl varchar(50),
 ruang varchar(10),
 spesialisasi varchar(20),
 pendidikan varchar(5),
 indeks int,
 indekstgl int,
 indeksbulan int,
 indeksthn int
)

select * from dokter

Afterwards, we began designing the design and start coding in Visual Basic, the following source code of this program. Good luck!

Dim oConn As New ADODB.Connection
Dim Sql As String
Dim pendidikan As String
Private Sub Command1_Click()
Dim z As String
Dim oRs As New ADODB.Recordset
    Sql = " SELECT * " _
         & " FROM dokter " _
         & " WHERE nip = '" & Text1.Text & "'"
    oRs.Open Sql, oConn, adOpenStatic
    If Not oRs.BOF Then
        Text2.Text = oRs("nama")
        Combo2.ListIndex = oRs("indekstgl")
        Combo3.ListIndex = oRs("indeksbulan")
        Combo4.ListIndex = oRs("indeksthn")
        Text3.Text = oRs("ruang")
        Combo1.ListIndex = oRs("indeks")
        z = Right(oRs("pendidikan"), 1)
        Option1(Int(z) - 1).Value = True
    Else
        MsgBox ("DATA TIDAK DITEMUKAN")
    End If
    oRs.Close
End Sub

Private Sub Command2_Click()
    Sql = " INSERT INTO dokter " _
         & " VALUES " _
         & " ('" & Text1.Text & "','" & Text2.Text & "','" _
         & Combo2.Text & " " & Combo3.Text & " " & Combo4.Text & "','" & Text3.Text & "','" & Combo1.Text & "','" _
         & pendidikan & "'," & Combo1.ListIndex & "," & Combo1.ListIndex & "," & Combo3.ListIndex & "," & Combo4.ListIndex & ")"
    oConn.Execute Sql
    Call Bersih
    Call Isigrid
    MsgBox ("DATA SUDAH DIMASUKKAN")
End Sub

Private Sub Command3_Click()
 Sql = "delete from dokter " _
         & "where nip = '" & Text1.Text & "'"
    oConn.Execute Sql
    Call Bersih
    Call Isigrid
    MsgBox ("DATA SUDAH DIHAPUS")
End Sub

Private Sub Command4_Click()

    Sql = "update dokter " _
         & "set nip = '" & Text1.Text & "'," _
         & "nama = '" & Text2.Text & "'," _
         & "tgl ='" & Combo2.Text & " " & Combo3.Text & " " & Combo4.Text & "'," _
         & "ruang = '" & Text3.Text & "'," _
         & "spesialisasi = '" & Combo1.Text & "'," _
         & "pendidikan = '" & pendidikan & "'," _
         & "indeks = " & Combo1.ListIndex & ", " _
         & "indekstgl = " & Combo2.ListIndex & "," _
         & "indeksbulan = " & Combo3.ListIndex & "," _
         & "indeksthn = " & Combo4.ListIndex & "" _
         & "where nip = '" & Text1.Text & "'"
    oConn.Execute Sql
    Call Bersih
    Call Isigrid
    MsgBox ("DATA SUDAH DIUPDATE")
    
End Sub

Private Sub Command5_Click()
End
End Sub

Private Sub Form_Load()
    Combo1.AddItem ""
    Combo2.AddItem ""
    Combo3.AddItem ""
    Combo4.AddItem ""
    
    For i = 1 To 31
        Combo2.AddItem i
    Next i
    
    For j = 1945 To 2010
        Combo4.AddItem j
    Next j
    
    
    oConn.ConnectionString = "PROVIDER=SQLOLEDB.1; " _
            & "DATA SOURCE = (local); " _
            & "INITIAL CATALOG = rumah_sakit; " _
            & "USER ID = sa; " _
            & "PASSWORD = 123456"
    oConn.Open
    Call SetGrid
    Call Isigrid
End Sub

Private Sub Bersih()
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    Combo1.ListIndex = 7
    Combo2.ListIndex = 0
    Combo3.ListIndex = 12
    Combo4.ListIndex = 0
    For i = 0 To 2
        Option1(i).Value = False
    Next i
    Text1.SetFocus
End Sub
Private Sub SetGrid()
    MSFlexGrid1.Cols = 6
    MSFlexGrid1.Rows = 2
    
    MSFlexGrid1.TextMatrix(0, 0) = "NIP"
    MSFlexGrid1.TextMatrix(0, 1) = "NAMA DOKTER"
    MSFlexGrid1.TextMatrix(0, 2) = "TANGGAL LAHIR"
    MSFlexGrid1.TextMatrix(0, 3) = "RUANG TUGAS"
    MSFlexGrid1.TextMatrix(0, 4) = "SPESIALISASI"
    MSFlexGrid1.TextMatrix(0, 5) = "PENDIDIKAN"
    MSFlexGrid1.ColWidth(0) = 2000
    MSFlexGrid1.ColWidth(1) = 4000
    MSFlexGrid1.ColWidth(2) = 3000
    MSFlexGrid1.ColWidth(3) = 2000
    MSFlexGrid1.ColWidth(4) = 2000
    MSFlexGrid1.ColWidth(5) = 1500
    
End Sub
Private Sub Isigrid()
    Dim oRs As New ADODB.Recordset
    
    MSFlexGrid1.Rows = 1
    MSFlexGrid1.AddItem ""
    Sql = "SELECT * " _
         & "FROM dokter "
    oRs.Open Sql, oConn, adOpenStatic
    If Not oRs.BOF Then
        Do While Not oRs.EOF
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 0) = oRs("nip")
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 1) = oRs("nama")
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 2) = oRs("tgl")
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 3) = oRs("ruang")
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 4) = oRs("spesialisasi")
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 5) = oRs("pendidikan")
            oRs.MoveNext
            MSFlexGrid1.AddItem ""
        Loop
    End If
    oRs.Close
End Sub

Private Sub MSFlexGrid1_Click()
Dim z As String
Dim oRs As New ADODB.Recordset
    Sql = " SELECT * " _
         & " FROM dokter " _
         & " WHERE nip = '" & MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 0) & "'"
    oRs.Open Sql, oConn, adOpenStatic
        If Not oRs.BOF Then
        Text1.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 0)
        Text2.Text = oRs("nama")
        Combo2.ListIndex = oRs("indekstgl")
        Combo3.ListIndex = oRs("indeksbulan")
        Combo4.ListIndex = oRs("indeksthn")
        Text3.Text = oRs("ruang")
        Combo1.ListIndex = oRs("indeks")
        z = Right(oRs("pendidikan"), 1)
        Option1(Int(z) - 1).Value = True
    Else
        MsgBox ("DATA TIDAK DITEMUKAN")
    End If
    oRs.Close
End Sub

Private Sub Option1_Click(Index As Integer)
    If Option1(Index).Value = True Then
        pendidikan = "S" & (Index + 1)
    End If
End Sub


Private Sub Timer1_Timer()

Label7.Caption = Format(Date, "dd - mm - yyyy") & "     " & Format(Now, "hh:mm:ss")
End Sub