Imports System.Data.SqlClient
Public Class Form3

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lock()
        searchshow()
        txt1.Enabled = False

        isearch.Enabled = True
        inew.Enabled = True
        isave.Enabled = False
        icancel.Enabled = False
        iupdate.Enabled = False
        idel.Enabled = False
    End Sub
    Private Sub autonum()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String
        Dim sum As Integer
        Dim no As Integer

        sql = "select max(patient_id) from Patient_table"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            no = 1

            txt1.Text = myreader(0)
            sum = txt1.Text + no
            txt1.Text = sum
        Else
            txt1.Text = "12001"

            'clear()


        End If


        Con.Close()
    End Sub
    Private Sub clear()
        txt1.Text = ""
        txt2.Text = ""
        txt3.Text = ""
        txt4.Text = ""
        txt5.Text = ""
        txt6.Text = ""
        txt7.Text = ""
        txt8.Text = ""
        txt9.Text = ""
        txt10.Text = ""
        txt11.Text = ""
        txt12.Text = ""
        txt13.Text = ""
    End Sub
    Private Sub lock()
        'txt1.Enabled = False
        txt2.Enabled = False
        txt3.Enabled = False
        txt4.Enabled = False
        txt5.Enabled = False
        txt6.Enabled = False
        txt7.Enabled = False
        txt8.Enabled = False
        txt9.Enabled = False
        txt10.Enabled = False
        txt11.Enabled = False
        txt12.Enabled = False
        txt13.Enabled = False
    End Sub
    Private Sub unlock()

        'txt1.Enabled = True
        txt2.Enabled = True
        txt3.Enabled = True
        txt4.Enabled = True
        txt5.Enabled = True
        txt6.Enabled = True
        txt7.Enabled = True
        txt8.Enabled = True
        txt9.Enabled = True
        txt10.Enabled = True
        txt11.Enabled = True
        txt12.Enabled = True
        txt13.Enabled = True
    End Sub
    Private Sub save()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String
        sql = "insert into Patient_table (patient_id,lname,fname,mname,bday,brgy,addres,reason,docname,age,gender,status,orient) values('" & txt1.Text & "','" & txt2.Text & "', '" & txt3.Text & "', '" & txt4.Text & "', '" & txt5.Text & "', '" & txt6.Text & "', '" & txt7.Text & "', '" & txt8.Text & "', '" & txt9.Text & "', '" & txt10.Text & "', '" & txt11.Text & "', '" & txt12.Text & "', '" & txt13.Text & "')"
        Try

            adapter.InsertCommand = New SqlCommand(sql, Con)
            adapter.InsertCommand.ExecuteNonQuery()
            MsgBox("Successful To save this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub


    Private Sub updatesave()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "update Patient_table set patient_id='" & txt1.Text & "',lname='" & txt2.Text & "',fname='" & txt3.Text & "',mname='" & txt4.Text & "',bday='" & txt5.Text & "',brgy='" & txt6.Text & "',addres='" & txt7.Text & "',reason='" & txt8.Text & "',docname='" & txt9.Text & "',age='" & txt10.Text & "',gender='" & txt11.Text & "',status='" & txt12.Text & "',orient='" & txt13.Text & "' where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.UpdateCommand = Con.CreateCommand
            adapter.UpdateCommand.CommandText = sql
            adapter.UpdateCommand.ExecuteNonQuery()
            MsgBox("Successful To Update this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub


    Private Sub deleterecord()
        Call main()
        Con.Open()


        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "delete Patient_table where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.DeleteCommand = Con.CreateCommand
            adapter.DeleteCommand.CommandText = sql
            adapter.DeleteCommand.ExecuteNonQuery()
            MsgBox("Successful To Delete this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub

    '********auto complete Search*********************
    Private Sub searchshow()
        Call main()
        Con.Open()

        Dim cmd As New SqlCommand("SELECT patient_id FROM Patient_table", Con)
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds, "My List") 'list can be any name u want

        Dim col As New AutoCompleteStringCollection
        Dim i As Integer
        For i = 0 To ds.Tables(0).Rows.Count - 1
            col.Add(ds.Tables(0).Rows(i)("patient_id").ToString())
        Next
        txtsearch.AutoCompleteSource = AutoCompleteSource.CustomSource
        txtsearch.AutoCompleteCustomSource = col
        txtsearch.AutoCompleteMode = AutoCompleteMode.Suggest
        Con.Close()
    End Sub
    Private Sub showsearch()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String

        sql = "select * from Patient_table where patient_id like '" & (txtsearch.Text) & "%'"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            If txtsearch.Text = myreader(0) Then
                unlock()
                inew.Enabled = False
                icancel.Enabled = True
                iupdate.Enabled = True
                idel.Enabled = True

                txt1.Text = myreader(0)
                txt2.Text = myreader(1)
                txt3.Text = myreader(2)
                txt4.Text = myreader(3)
                txt5.Text = myreader(4)
                txt6.Text = myreader(5)
                txt7.Text = myreader(6)
                txt8.Text = myreader(7)
                txt9.Text = myreader(8)
                txt10.Text = myreader(9)
                txt11.Text = myreader(10)
                txt12.Text = myreader(11)
                txt13.Text = myreader(12)

            End If
        Else
            lock()
            MsgBox("No record Found on Database", MsgBoxStyle.Critical, "Error")
            inew.Enabled = True
            icancel.Enabled = False
            iupdate.Enabled = False
            idel.Enabled = False

            clear()
        End If

        Con.Close()
    End Sub

    Private Sub inew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles inew.Click
        isearch.Enabled = False
        inew.Enabled = False
        isave.Enabled = True
        icancel.Enabled = True
        iupdate.Enabled = False
        idel.Enabled = False
        txtsearch.Enabled = False


        clear()
        unlock()
        txtsearch.Text = ""

        autonum()

        txt2.Focus()
    End Sub

    Private Sub isave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles isave.Click
        save()


        clear()
        lock()
        searchshow()

        isearch.Enabled = True
        inew.Enabled = True
        isave.Enabled = False
        icancel.Enabled = False
        iupdate.Enabled = False
        idel.Enabled = False
        txtsearch.Enabled = True
    End Sub

    Private Sub icancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles icancel.Click
        lock()
        clear()
        txtsearch.Text = ""

        isearch.Enabled = True
        inew.Enabled = True
        isave.Enabled = False
        icancel.Enabled = False
        iupdate.Enabled = False
        idel.Enabled = False
        txtsearch.Enabled = True

    End Sub

    Private Sub iupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles iupdate.Click
        On Error GoTo err


        If txtsearch.Text = "" Then
            MsgBox("Sorry you have no record to update!", MsgBoxStyle.Critical, "Error Update")

        Else
            Select Case MsgBox("Are you sure, you want to Update this Record?", MsgBoxStyle.YesNo)
                Case MsgBoxResult.Yes
                    updatesave()
                    'update to another table if record is exist
                    Verifypatientcertificate()
                    VerifypatientPharmacy()
                    Verifypatientroom()
                    VerifypatientRadiological()
                    Verifypatientlaboratory()
                    Verifypatientconsultation()


                    clear()
                    txtsearch.Text = ""
                    lock()

                    searchshow()


                    isearch.Enabled = True
                    inew.Enabled = True
                    isave.Enabled = False
                    icancel.Enabled = False
                    iupdate.Enabled = False
                    idel.Enabled = False
                Case MsgBoxResult.No
                    GoTo err
            End Select


        End If
err:
        Exit Sub
    End Sub

    Private Sub idel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles idel.Click
        On Error GoTo err


        If txtsearch.Text = "" Then
            MsgBox("Sorry you have no record to Delete!", MsgBoxStyle.Critical, "Error Delete")


        Else
            Select Case MsgBox("Are you sure, you want to Delete this Record?", MsgBoxStyle.YesNo)
                Case MsgBoxResult.Yes
                    deleterecord()

                    clear()
                    txtsearch.Text = ""
                    lock()

                    searchshow()


                    isearch.Enabled = True
                    inew.Enabled = True
                    isave.Enabled = False
                    icancel.Enabled = False
                    iupdate.Enabled = False
                    idel.Enabled = False

                Case MsgBoxResult.No
                    GoTo err
            End Select
        End If


err:
        Exit Sub
    End Sub

    Private Sub txtsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.Click
        If txtsearch.Text = "" Then
            clear()
        End If
    End Sub

    Private Sub txtsearch_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtsearch.KeyUp
        If e.KeyCode = Keys.Enter Then
            showsearch()
        End If
    End Sub

    Private Sub isearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles isearch.Click
        If txtsearch.Text = "" Then
            MsgBox("Search Patient Id is Null ", MsgBoxStyle.Critical, "Error Search")
        Else
            showsearch()
        End If
    End Sub
    'update the sa ibang table kpg may record
    Private Sub Verifypatientcertificate()

        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String

        sql = "select * from  patientcertificate where patient_id like '" & (txt1.Text) & "%'"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            updatepatientcertificate()
        Else
            GoTo exx
        End If

        Con.Close()
exx:
        Exit Sub
    End Sub
    Private Sub updatepatientcertificate()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "update patientcertificate set patient_id='" & txt1.Text & "',lname='" & txt2.Text & "',fname='" & txt3.Text & "',mname='" & txt4.Text & "' where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.UpdateCommand = Con.CreateCommand
            adapter.UpdateCommand.CommandText = sql
            adapter.UpdateCommand.ExecuteNonQuery()
            'MsgBox("Successful To Update this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub
    Private Sub VerifypatientPharmacy()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String

        sql = "select * from  Pharmacy where patient_id like '" & (txt1.Text) & "%'"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            updatepatientPharmacy()
        Else
            GoTo exx
        End If

        Con.Close()
exx:
        Exit Sub
    End Sub
    Private Sub updatepatientPharmacy()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "update Pharmacy set patient_id='" & txt1.Text & "',lname='" & txt2.Text & "',fname='" & txt3.Text & "',mname='" & txt4.Text & "' where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.UpdateCommand = Con.CreateCommand
            adapter.UpdateCommand.CommandText = sql
            adapter.UpdateCommand.ExecuteNonQuery()
            'MsgBox("Successful To Update this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub
    Private Sub Verifypatientroom()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String

        sql = "select * from  room where patient_id like '" & (txt1.Text) & "%'"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            updatepatientroom()
        Else
            GoTo exx
        End If

        Con.Close()
exx:
        Exit Sub
    End Sub
    Private Sub updatepatientroom()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "update room set patient_id='" & txt1.Text & "',lname='" & txt2.Text & "',fname='" & txt3.Text & "',mname='" & txt4.Text & "' where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.UpdateCommand = Con.CreateCommand
            adapter.UpdateCommand.CommandText = sql
            adapter.UpdateCommand.ExecuteNonQuery()
            'MsgBox("Successful To Update this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub
    Private Sub VerifypatientRadiological()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String

        sql = "select * from  Radiological where patient_id like '" & (txt1.Text) & "%'"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            updatepatientRadiological()
        Else
            GoTo exx
        End If

        Con.Close()
exx:
        Exit Sub
    End Sub
    Private Sub updatepatientRadiological()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "update Radiological set patient_id='" & txt1.Text & "',lname='" & txt2.Text & "',fname='" & txt3.Text & "',mname='" & txt4.Text & "' where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.UpdateCommand = Con.CreateCommand
            adapter.UpdateCommand.CommandText = sql
            adapter.UpdateCommand.ExecuteNonQuery()
            'MsgBox("Successful To Update this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub
    Private Sub Verifypatientlaboratory()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String

        sql = "select * from  laboratory where patient_id like '" & (txt1.Text) & "%'"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            updatepatientlaboratory()
        Else
            GoTo exx
        End If

        Con.Close()
exx:
        Exit Sub
    End Sub
    Private Sub updatepatientlaboratory()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "update laboratory set patient_id='" & txt1.Text & "',lname='" & txt2.Text & "',fname='" & txt3.Text & "',mname='" & txt4.Text & "' where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.UpdateCommand = Con.CreateCommand
            adapter.UpdateCommand.CommandText = sql
            adapter.UpdateCommand.ExecuteNonQuery()
            'MsgBox("Successful To Update this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub
    Private Sub Verifypatientconsultation()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String

        sql = "select * from  consultation where patient_id like '" & (txt1.Text) & "%'"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            updatepatientconsultation()
        Else
            GoTo exx
        End If

        Con.Close()
exx:
        Exit Sub
    End Sub
    Private Sub updatepatientconsultation()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
        Dim sql As String

        sql = "update consultation set patient_id='" & txt1.Text & "',lname='" & txt2.Text & "',fname='" & txt3.Text & "',mname='" & txt4.Text & "' where patient_id ='" & txtsearch.Text & "'"
        Try
            adapter.UpdateCommand = Con.CreateCommand
            adapter.UpdateCommand.CommandText = sql
            adapter.UpdateCommand.ExecuteNonQuery()
            'MsgBox("Successful To Update this record on database", MsgBoxStyle.Information, "Information On Database")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Con.Close()
    End Sub
End Class