Program Login SQL Server Design form seperti gambar dibawah ini
tuliskan printah berikut pada tombol Login Set conn = New ADODB.Connection On Error GoTo ErrHandler 'Cek status koneksi If conn.State = adStateOpen Then conn.Close 'Tutup Dulu koneksi End If 'Membuka Koneksi baru conn.ConnectionString = "Provider=SQLOLEDB.1; " & _ "User ID = " & Text3.Text "Password = " & Text4.Text "Initial Catalog= " & Text2.Text "Data Source = " & Text1.Text "Persist Security Info=True; "
& & & &
" " " "
; ; ; ;
" " " "
& & & &
_ _ _ _
conn.ConnectionTimeout = 30 conn.Open 'Cek keberhasilan koneksi tersebut If conn.State = adStateOpen Then MsgBox "Connection Success", vbInformation + vbOKOnly, "Information" End If ErrHandler: If Err.Number <> 0 Then MsgBox Err.Description, vbCritical + vbOKOnly, "Warning" End If End Sub tuliskan printah berikut pada tombol Close Private Sub Command2_Click() On Error GoTo ErrHandler 'Cek status koneksi If conn.State = adStateOpen Then 'Tutup Dulu koneksi sebelumnya conn.Close End If ErrHandler: If Err.Number <> 0 Then MsgBox Err.Description, vbCritical + vbOKOnly, "Warning" End If End Sub
Abdrohim/VB&SQLServer/ScriptLogin&MaintenaceData
1/5
Program Login SQL Server dengan DataModule Buka Project Baru buat form seperti gambar berikut
Sebelum mengisi perintah pada login buat lah module terlebih dahulu. Klik Menu Project | Add Module Pada module1 tuliskan perintah berikut: Public Conn As New ADODB.Connection Public rs As New ADODB.Recordset Public ServerName As String Public DBName As String Public UserId As String Public Pass As String Kembali pada Form1 Tuliskan perintah berikut untuk tombol Login Private Sub Command1_Click() ServerName = Text1.Text DBName = Text2.Text UserId = Text3.Text Pass = Text4.Text Conn.ConnectionString = "Provider=SQLOLEDB.1; " & _ "User ID = " & UserId & " ; " "Password = " & Pass & " ; " & "Initial Catalog= " & DBName & " ; " "Data Source = " & ServerName & " "Persist Security Info=True; " conn.ConnectionTimeout = 30 conn.Open 'Cek keberhasilan koneksi tersebut If conn.State = adStateOpen Then 'komentar form2 dibuka jika form2 sudah dibuat ‘Form2.Show End If ErrHandler: If Err.Number <> 0 Then MsgBox Err.Description, vbCritical + vbOKOnly, "Warning" End If End Sub
Abdrohim/VB&SQLServer/ScriptLogin&MaintenaceData
& _ _ & _ ; " & _
2/5
Program Pengembangan dari Program Login Buka form ke dua, Design form seperti berikut ini (bagian bawah komponen StatusBar)
Berikan nama-nama untuk Command sesuai dengan Cation yang tertulis definisiskan dahulu variabel general: Dim vBookMark As Variant Tuliskan prosedur prosedur berikut Private Sub AturTombol(status As Boolean) cmdInsert.Enabled = status cmdUpdate.Enabled = status cmdSave.Enabled = Not status cmdcancel.Enabled = Not status End Sub Private Sub CallData() Text1.Text = rs(0).Value Text2.Text = rs(1).Value Text3.Text = rs(2).Value Text4.Text = rs(3).Value
'id 'name 'address 'city
'Tampilkan posisi record aktif Text5.Text = rs.AbsolutePosition & " / " & rs.RecordCount End Sub Private Sub ResetData() Text1.Text = "" Text2.Text = "" Text3.Text = "" Text4.Text = "" End Sub Tuliskan script berikut pada event loaddata Form2 Private Sub Form_Load() Dim qryAuthor As String 'Menampilkan database pada status bar StatusBar1.Panels(1).Text = "Server Data: " & ServerName StatusBar1.Panels(2).Text = "Database: " & DBName
Abdrohim/VB&SQLServer/ScriptLogin&MaintenaceData
3/5
StatusBar1.Panels(3).Text = "User ID : " & UserId 'membuka tabel Stores pada database Pubs Set rs = New ADODB.Recordset qryAuthor = "SELECT * FROM Stores" rs.Open qryAuthor, Conn, adOpenStatic, adLockOptimistic rs.MoveFirst 'record pertama CallData AturTombol (True) End Sub Tuliskan script berikut pada tombol First Private Sub cmdFirst_Click() rs.MoveFirst CallData Text1.SetFocus End Sub Tuliskan script berikut pada tombol Last Private Sub cmdLast_Click() rs.MoveLast CallData End Sub Tuliskan script berikut pada tombol Next Private Sub cmdNext_Click() rs.MoveNext If rs.EOF Then rs.MoveLast End If CallData End Sub Tuliskan script berikut pada tombol Previous Private Sub cmdPrev_Click() rs.MovePrevious If rs.BOF Then rs.MoveFirst End If CallData End Sub Tuliskan script berikut pada tombol Insert Data Private Sub cmdInsert_Click() ResetData 'kosongkan data AturTombol (False) 'hidupkan tombol save, cancel vBookMark = rs.Bookmark End Sub Tuliskan script berikut pada tombol Save Data Private Sub cmdSave_Click() rs.AddNew 'siapkan record kosong 'isikan data entry pada field data rs!stor_id = Text1.Text rs!stor_name = Text3.Text rs!stor_address = Text2.Text
Abdrohim/VB&SQLServer/ScriptLogin&MaintenaceData
4/5
rs!city = Text4.Text 'cek keadaan data If Text1.Text = "" Then rs.CancelUpdate ' membatalkan perintah AddNew MsgBox "Data belum lengkap", vbCritical, "Peringatan" Text1.SetFocus Else rs.Update ' menyimpan data secata permanen MsgBox "Data telah tersimpan", vbInformation, "Information" End If AturTombol (True) End Sub Tuliskan script berikut pada tombol cancel Data Private Sub cmdcancel_Click() rs.Bookmark = vBookMark CallData AturTombol (True) End Sub Tuliskan script berikut pada tombol update Data Private Sub cmdUpdate_Click() rs!stor_id = Text1.Text rs!stor_name = Text3.Text rs!stor_address = Text2.Text rs!city = Text4.Text rs.Update ' menyimpan data secata permanen MsgBox "Data perubahan telah tersimpan", vbInformation, "Information" End Sub
Abdrohim/VB&SQLServer/ScriptLogin&MaintenaceData
5/5
