MENGKONEKSIKAN VB.NET DENGAN SQL SERVER

• Halaman menu utama

Public Class Form6

   Private Sub ExiToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExiToolStripMenuItem.Click

        Me.Close()

    End Sub

    Private Sub LogInToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LogInToolStripMenuItem.Click

        Form1.Show()

    End Sub

    Private Sub DataObatToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataObatToolStripMenuItem.Click

        Form3.Show()

    End Sub

    Private Sub DataSupplierToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataSupplierToolStripMenuItem.Click

        Form4.Show()

    End Sub

End Class

• Login 

Public Class Form1

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

        Call koneksi()

        Try

            Tampil.Connection = Database

            Tampil.CommandType = CommandType.Text

Tampil.CommandText = "select * from tbl_user where User_Id='" & Trim(TextBox1.Text) & "'"

            Tampilkan = Tampil.ExecuteReader

            If Tampilkan.HasRows = True Then

                Form2.Show()

                Me.Visible = False

            Else

                MsgBox("User Id dan Password anda salah")

            End If

        Catch ex As Exception

            MsgBox(ex.ToString())

        End Try

    End Sub

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

        Me.Close()

    End Sub

    Sub bersih()

        TextBox1.Text = ""

        TextBox2.Text = ""

    End Sub

End Class

• Supplier 

Public Class Form2

   

    Sub Bersih()

        TextBox1.Text = ""

        TextBox2.Text = ""

        TextBox3.Text = ""

        ComboBox1.Text = "admin"

    End Sub

    Sub Normal()

        Button1.Text = "Tambah"

        Button2.Text = "Edit"

        Button3.Text = "Hapus"

        Button4.Text = "Keluar"

        Button5.Text = "Cari"

        Button1.Enabled = True

        Button2.Enabled = True

        Button3.Enabled = True

        Button5.Enabled = True

        TextBox1.Enabled = True

    End Sub

    Sub CekRecord()

        Call koneksi()

        Tampil.Connection = Database

        Tampil.CommandType = CommandType.Text

Tampil.CommandText = "Select * from tbl_user where User_Id='" & Trim(TextBox1.Text) & "'"

        Tampilkan = Tampil.ExecuteReader

        If Tampilkan.HasRows = True Then

            hasilcek = True

        Else

            hasilcek = False

        End If

    End Sub

  

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

        Call simpan()

    End Sub

    Sub simpan()

        If Button1.Text = "Tambah" Then

            TextBox1.Text = ""

            Button1.Text = "Simpan"

            Button2.Enabled = False

            Button3.Enabled = False

            Button5.Enabled = False

            Button4.Text = "Batal"

            'TextBox1.Enabled = False

            TextBox1.Focus()

        Else

            If TextBox1.Text = "" Then Exit Sub

            If TextBox2.Text = "" Then Exit Sub

            If TextBox3.Text = "" Then Exit Sub

            If ComboBox1.Text = "" Then Exit Sub

           

            Call CekRecord()

            Try

                If hasilcek = "False" Then

                    Call koneksi()

                    Tampil.Connection = Database

                    Tampil.CommandType = CommandType.Text

Tampil.CommandText = "Insert into tbl_user (User_Id,Password,Nama,Bagian,Tgl)values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & ComboBox1.Text & "','" & DateTimePicker1.Value & "')"

                    Tampil.ExecuteNonQuery()

                    Call Bersih()

                    TextBox1.Focus()

                Else

MsgBox("Data sudah ada, salah cek data yang di input", MsgBoxStyle.Critical, "Input data")

                    TextBox1.Focus()

                End If

            Catch ex As Exception

                MsgBox(ex.ToString())

            End Try

        End If

    End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        If Button4.Text = "Keluar" Then

            Me.Close()

        Else

            Call Bersih()

            Call Normal()

        End If

    End Sub

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

        Call ubah()

    End Sub

    Sub ubah()

        If Button2.Text = "Edit" Then

            TextBox1.Text = ""

            Button2.Text = "Simpan"

            Button1.Enabled = False

            Button3.Enabled = False

            Button5.Enabled = False

            Button4.Text = "Batal"

            TextBox1.Focus()

        Else

            If TextBox1.Text = "" Then Exit Sub

            If TextBox2.Text = "" Then Exit Sub

            If TextBox3.Text = "" Then Exit Sub

            If ComboBox1.Text = "" Then Exit Sub

           

            Try

                Call koneksi()

                  Tampil.Connection = Database

                  Tampil.CommandType = CommandType.Text

Tampil.CommandText = "update tbl_user set Nama ='" & Trim(TextBox3.Text) & "',Password ='" & Trim(TextBox2.Text) & "',Bagian ='" & Trim(ComboBox1.Text) & "',Tgl ='" & Trim(DateTimePicker1.Value) & "' where User_Id ='" & Trim(TextBox1.Text) & "'"

                  Tampil.ExecuteNonQuery()

                Call Bersih()

                TextBox1.Focus()

            Catch ex As Exception

                MsgBox(ex.ToString())

            End Try

        End If

    End Sub

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

        Call Hapus()

    End Sub

    Sub Hapus()

        If Button3.Text = "Hapus" Then

            TextBox1.Text = ""

            Button3.Text = "OK"

            Button1.Enabled = False

            Button2.Enabled = False

            Button5.Enabled = False

            Button4.Text = "Batal"

            TextBox1.Focus()

        Else

            If TextBox1.Text = "" Then Exit Sub

            If TextBox2.Text = "" Then Exit Sub

            If TextBox3.Text = "" Then Exit Sub

            If ComboBox1.Text = "" Then Exit Sub

           

            Try

                Call koneksi()

     

                  Tampil.Connection = Database

                  Tampil.CommandType = CommandType.Text

Tampil.CommandText = "Delete from tbl_user where user_id='" & Trim(TextBox1.Text) & "'"

                  Tampil.ExecuteNonQuery()

                Call Bersih()

                TextBox1.Focus()

            Catch ex As Exception

                MsgBox(ex.ToString())

            End Try

        End If

    End Sub

   

  

Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress

        If e.KeyChar = Chr(Keys.Enter) Then

            If Button1.Text = "Simpan" Then

                Call CekRecord()

                If hasilcek = False Then

                    TextBox2.Focus()

                Else

MsgBox("Kode sudah ada, silahkan ganti kode lain", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Infromasi")

                  TextBox1.Focus()

                End If

            Else

                Call koneksi()

                Tampil.Connection = Database

                Tampil.CommandType = CommandType.Text

                Tampil.CommandText = "select * from tbl_user where User_Id='"                 & Trim(TextBox1.Text) & "'"

                Tampilkan = Tampil.ExecuteReader

                If Tampilkan.HasRows = True Then

                    While Tampilkan.Read()

                        If IsDBNull(Tampilkan("User_Id")) Then

                            TextBox1.Focus()

                        Else

                            TextBox2.Text = Tampilkan("Password")

                            TextBox3.Text = Tampilkan("Nama")

                            ComboBox1.Text = Tampilkan("Bagian")

                            TextBox2.Focus()

                        End If

                    End While

                Else

MsgBox("Data tidak diketemukan", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Tambah Data")

                End If

            End If

        End If

    End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Call User()

    End Sub

    Sub User()

        Call koneksi()

        Tampil2.Connection = Database

        Tampil2.CommandType = CommandType.Text

Tampil2.CommandText = "select * from tbl_user where User_Id ='"             & Trim(TextBox4.Text) & "'"

        Tampilkan = Tampil2.ExecuteReader

        If Tampilkan.HasRows = True Then

            While Tampilkan.Read()

                If IsDBNull(Tampilkan("User_Id")) Then

                    TextBox4.Text = ""

                Else

                    ListView1.Items.Add(Tampilkan("Password") & " " & (Tampilkan("Nama") & " " & (Tampilkan("Bagian") & " " & (Tampilkan("Tgl")))))

                End If

            End While

        End If

    End Sub

End Class

• Module 

Imports System.Data

Imports System.Data.Sql

Module Module1

    Public Database As New OleDb.OleDbConnection

    Public Tampil As New OleDb.OleDbCommand

    Public Tampil2 As New OleDb.OleDbCommand

    Public Tampilkan As OleDb.OleDbDataReader

    Public hasilcek As String

    Public Sub koneksi()

        Try

            Database.Close()

            'local

Database.ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=rumah_sakit; Data Source=."

            Database.Open()

            MsgBox("Koneksi Berhasil")

        Catch ex As Exception

            MsgBox(ex.ToString())

        End Try

    End Sub

End Module