Imports System.Windows.Forms
Imports System.Data.OleDb
Imports System.Exception

Public Class frmStudent
    Inherits Form

    Friend WithEvents studconn As OleDbConnection
    Friend WithEvents studadap As OleDbDataAdapter
    Friend WithEvents studdataset As DataSet
    Friend WithEvents studcmd As OleDbCommand
    'wekken geen events op
    Dim studrdr As OleDbDataReader
    Dim strconn As String
    Dim teller As Integer = 0


#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()
        'connectie maken met de database
        UpdateConnectie()
        txtpaswoord.Hide()
        lblpaswoord.Hide()

        ' Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Panel1 As System.Windows.Forms.Panel
    Friend WithEvents btnnew As System.Windows.Forms.Button
    Friend WithEvents btnedit As System.Windows.Forms.Button
    Friend WithEvents btndelete As System.Windows.Forms.Button
    Friend WithEvents Panel2 As System.Windows.Forms.Panel
    Friend WithEvents lblstudnummer As System.Windows.Forms.Label
    Friend WithEvents lblnaam As System.Windows.Forms.Label
    Friend WithEvents lbladres As System.Windows.Forms.Label
    Friend WithEvents lblpostcode As System.Windows.Forms.Label
    Friend WithEvents lblemail As System.Windows.Forms.Label
    Friend WithEvents grpbgeslacht As System.Windows.Forms.GroupBox
    Friend WithEvents rbtnvrouw As System.Windows.Forms.RadioButton
    Friend WithEvents rbtnman As System.Windows.Forms.RadioButton
    Friend WithEvents lblvoornaam As System.Windows.Forms.Label
    Friend WithEvents lblgemeente As System.Windows.Forms.Label
    Friend WithEvents txtemail As System.Windows.Forms.TextBox
    Friend WithEvents txtpostcode As System.Windows.Forms.TextBox
    Friend WithEvents txtadres As System.Windows.Forms.TextBox
    Friend WithEvents txtnaam As System.Windows.Forms.TextBox
    Friend WithEvents txtstudnum As System.Windows.Forms.TextBox
    Friend WithEvents txtvoornaam As System.Windows.Forms.TextBox
    Friend WithEvents txtgemeente As System.Windows.Forms.TextBox
    Friend WithEvents chbbetaald As System.Windows.Forms.CheckBox
    Friend WithEvents btnsave As System.Windows.Forms.Button
    Friend WithEvents btncancel As System.Windows.Forms.Button
    Friend WithEvents lstnaamstudent As System.Windows.Forms.ListBox
    Friend WithEvents txtpaswoord As System.Windows.Forms.TextBox
    Friend WithEvents lblpaswoord As System.Windows.Forms.Label

    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Panel1 = New System.Windows.Forms.Panel()
        Me.btndelete = New System.Windows.Forms.Button()
        Me.btnedit = New System.Windows.Forms.Button()
        Me.btnnew = New System.Windows.Forms.Button()
        Me.lstnaamstudent = New System.Windows.Forms.ListBox()
        Me.Panel2 = New System.Windows.Forms.Panel()
        Me.lblpaswoord = New System.Windows.Forms.Label()
        Me.txtpaswoord = New System.Windows.Forms.TextBox()
        Me.btncancel = New System.Windows.Forms.Button()
        Me.btnsave = New System.Windows.Forms.Button()
        Me.chbbetaald = New System.Windows.Forms.CheckBox()
        Me.txtgemeente = New System.Windows.Forms.TextBox()
        Me.txtvoornaam = New System.Windows.Forms.TextBox()
        Me.lblgemeente = New System.Windows.Forms.Label()
        Me.lblvoornaam = New System.Windows.Forms.Label()
        Me.grpbgeslacht = New System.Windows.Forms.GroupBox()
        Me.rbtnman = New System.Windows.Forms.RadioButton()
        Me.rbtnvrouw = New System.Windows.Forms.RadioButton()
        Me.txtemail = New System.Windows.Forms.TextBox()
        Me.txtpostcode = New System.Windows.Forms.TextBox()
        Me.txtadres = New System.Windows.Forms.TextBox()
        Me.txtnaam = New System.Windows.Forms.TextBox()
        Me.txtstudnum = New System.Windows.Forms.TextBox()
        Me.lblemail = New System.Windows.Forms.Label()
        Me.lblpostcode = New System.Windows.Forms.Label()
        Me.lbladres = New System.Windows.Forms.Label()
        Me.lblnaam = New System.Windows.Forms.Label()
        Me.lblstudnummer = New System.Windows.Forms.Label()
        Me.Panel1.SuspendLayout()
        Me.Panel2.SuspendLayout()
        Me.grpbgeslacht.SuspendLayout()
        Me.SuspendLayout()
        '
        'Panel1
        '
        Me.Panel1.Controls.AddRange(New System.Windows.Forms.Control() {Me.btndelete, Me.btnedit, Me.btnnew, Me.lstnaamstudent})
        Me.Panel1.Location = New System.Drawing.Point(0, 8)
        Me.Panel1.Name = "Panel1"
        Me.Panel1.Size = New System.Drawing.Size(224, 352)
        Me.Panel1.TabIndex = 0
        '
        'btndelete
        '
        Me.btndelete.Location = New System.Drawing.Point(152, 320)
        Me.btndelete.Name = "btndelete"
        Me.btndelete.Size = New System.Drawing.Size(64, 24)
        Me.btndelete.TabIndex = 3
        Me.btndelete.Text = "Delete"
        '
        'btnedit
        '
        Me.btnedit.Location = New System.Drawing.Point(80, 320)
        Me.btnedit.Name = "btnedit"
        Me.btnedit.Size = New System.Drawing.Size(64, 24)
        Me.btnedit.TabIndex = 2
        Me.btnedit.Text = "Edit"
        '
        'btnnew
        '
        Me.btnnew.Location = New System.Drawing.Point(8, 320)
        Me.btnnew.Name = "btnnew"
        Me.btnnew.Size = New System.Drawing.Size(64, 24)
        Me.btnnew.TabIndex = 1
        Me.btnnew.Text = "New"
        '
        'lstnaamstudent
        '
        Me.lstnaamstudent.Location = New System.Drawing.Point(8, 8)
        Me.lstnaamstudent.Name = "lstnaamstudent"
        Me.lstnaamstudent.Size = New System.Drawing.Size(208, 290)
        Me.lstnaamstudent.TabIndex = 0
        '
        'Panel2
        '
        Me.Panel2.Controls.AddRange(New System.Windows.Forms.Control() {Me.lblpaswoord, Me.txtpaswoord, Me.btncancel, Me.btnsave, Me.chbbetaald, Me.txtgemeente, Me.txtvoornaam, Me.lblgemeente, Me.lblvoornaam, Me.grpbgeslacht, Me.txtemail, Me.txtpostcode, Me.txtadres, Me.txtnaam, Me.txtstudnum, Me.lblemail, Me.lblpostcode, Me.lbladres, Me.lblnaam, Me.lblstudnummer})
        Me.Panel2.Location = New System.Drawing.Point(232, 8)
        Me.Panel2.Name = "Panel2"
        Me.Panel2.Size = New System.Drawing.Size(592, 352)
        Me.Panel2.TabIndex = 2
        '
        'lblpaswoord
        '
        Me.lblpaswoord.Location = New System.Drawing.Point(424, 224)
        Me.lblpaswoord.Name = "lblpaswoord"
        Me.lblpaswoord.Size = New System.Drawing.Size(32, 24)
        Me.lblpaswoord.TabIndex = 19
        Me.lblpaswoord.Text = "Pasw"
        '
        'txtpaswoord
        '
        Me.txtpaswoord.Location = New System.Drawing.Point(472, 224)
        Me.txtpaswoord.MaxLength = 14
        Me.txtpaswoord.Name = "txtpaswoord"
        Me.txtpaswoord.PasswordChar = Microsoft.VisualBasic.ChrW(42)
        Me.txtpaswoord.Size = New System.Drawing.Size(96, 20)
        Me.txtpaswoord.TabIndex = 18
        Me.txtpaswoord.Text = ""
        '
        'btncancel
        '
        Me.btncancel.Location = New System.Drawing.Point(312, 288)
        Me.btncancel.Name = "btncancel"
        Me.btncancel.Size = New System.Drawing.Size(75, 24)
        Me.btncancel.TabIndex = 17
        Me.btncancel.Text = "Cancel"
        '
        'btnsave
        '
        Me.btnsave.Location = New System.Drawing.Point(184, 288)
        Me.btnsave.Name = "btnsave"
        Me.btnsave.Size = New System.Drawing.Size(80, 24)
        Me.btnsave.TabIndex = 16
        Me.btnsave.Text = "Save"
        '
        'chbbetaald
        '
        Me.chbbetaald.Location = New System.Drawing.Point(320, 224)
        Me.chbbetaald.Name = "chbbetaald"
        Me.chbbetaald.Size = New System.Drawing.Size(64, 24)
        Me.chbbetaald.TabIndex = 15
        Me.chbbetaald.Text = "Betaald"
        '
        'txtgemeente
        '
        Me.txtgemeente.Location = New System.Drawing.Point(424, 176)
        Me.txtgemeente.MaxLength = 50
        Me.txtgemeente.Name = "txtgemeente"
        Me.txtgemeente.Size = New System.Drawing.Size(120, 20)
        Me.txtgemeente.TabIndex = 14
        Me.txtgemeente.Text = ""
        '
        'txtvoornaam
        '
        Me.txtvoornaam.Location = New System.Drawing.Point(424, 96)
        Me.txtvoornaam.MaxLength = 50
        Me.txtvoornaam.Name = "txtvoornaam"
        Me.txtvoornaam.Size = New System.Drawing.Size(120, 20)
        Me.txtvoornaam.TabIndex = 13
        Me.txtvoornaam.Text = ""
        '
        'lblgemeente
        '
        Me.lblgemeente.Location = New System.Drawing.Point(304, 176)
        Me.lblgemeente.Name = "lblgemeente"
        Me.lblgemeente.Size = New System.Drawing.Size(88, 24)
        Me.lblgemeente.TabIndex = 12
        Me.lblgemeente.Text = "Gemeente"
        '
        'lblvoornaam
        '
        Me.lblvoornaam.Location = New System.Drawing.Point(304, 96)
        Me.lblvoornaam.Name = "lblvoornaam"
        Me.lblvoornaam.Size = New System.Drawing.Size(96, 24)
        Me.lblvoornaam.TabIndex = 11
        Me.lblvoornaam.Text = "Voornaam :"
        '
        'grpbgeslacht
        '
        Me.grpbgeslacht.Controls.AddRange(New System.Windows.Forms.Control() {Me.rbtnman, Me.rbtnvrouw})
        Me.grpbgeslacht.Location = New System.Drawing.Point(376, 24)
        Me.grpbgeslacht.Name = "grpbgeslacht"
        Me.grpbgeslacht.Size = New System.Drawing.Size(200, 56)
        Me.grpbgeslacht.TabIndex = 10
        Me.grpbgeslacht.TabStop = False
        Me.grpbgeslacht.Text = "Geslacht"
        '
        'rbtnman
        '
        Me.rbtnman.Location = New System.Drawing.Point(8, 16)
        Me.rbtnman.Name = "rbtnman"
        Me.rbtnman.Size = New System.Drawing.Size(48, 24)
        Me.rbtnman.TabIndex = 2
        Me.rbtnman.Text = "Man"
        '
        'rbtnvrouw
        '
        Me.rbtnvrouw.Location = New System.Drawing.Point(96, 16)
        Me.rbtnvrouw.Name = "rbtnvrouw"
        Me.rbtnvrouw.Size = New System.Drawing.Size(64, 24)
        Me.rbtnvrouw.TabIndex = 1
        Me.rbtnvrouw.Text = "Vrouw"
        '
        'txtemail
        '
        Me.txtemail.Location = New System.Drawing.Point(144, 224)
        Me.txtemail.MaxLength = 50
        Me.txtemail.Name = "txtemail"
        Me.txtemail.Size = New System.Drawing.Size(136, 20)
        Me.txtemail.TabIndex = 9
        Me.txtemail.Text = ""
        '
        'txtpostcode
        '
        Me.txtpostcode.Location = New System.Drawing.Point(144, 184)
        Me.txtpostcode.MaxLength = 4
        Me.txtpostcode.Name = "txtpostcode"
        Me.txtpostcode.Size = New System.Drawing.Size(64, 20)
        Me.txtpostcode.TabIndex = 8
        Me.txtpostcode.Text = ""
        '
        'txtadres
        '
        Me.txtadres.Location = New System.Drawing.Point(144, 144)
        Me.txtadres.MaxLength = 50
        Me.txtadres.Name = "txtadres"
        Me.txtadres.Size = New System.Drawing.Size(136, 20)
        Me.txtadres.TabIndex = 7
        Me.txtadres.Text = ""
        '
        'txtnaam
        '
        Me.txtnaam.Location = New System.Drawing.Point(144, 96)
        Me.txtnaam.MaxLength = 50
        Me.txtnaam.Name = "txtnaam"
        Me.txtnaam.Size = New System.Drawing.Size(136, 20)
        Me.txtnaam.TabIndex = 6
        Me.txtnaam.Text = ""
        '
        'txtstudnum
        '
        Me.txtstudnum.Location = New System.Drawing.Point(144, 40)
        Me.txtstudnum.MaxLength = 7
        Me.txtstudnum.Name = "txtstudnum"
        Me.txtstudnum.Size = New System.Drawing.Size(136, 20)
        Me.txtstudnum.TabIndex = 5
        Me.txtstudnum.Text = ""
        '
        'lblemail
        '
        Me.lblemail.Location = New System.Drawing.Point(80, 224)
        Me.lblemail.Name = "lblemail"
        Me.lblemail.Size = New System.Drawing.Size(48, 24)
        Me.lblemail.TabIndex = 4
        Me.lblemail.Text = "Email : "
        Me.lblemail.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'lblpostcode
        '
        Me.lblpostcode.Location = New System.Drawing.Point(64, 184)
        Me.lblpostcode.Name = "lblpostcode"
        Me.lblpostcode.Size = New System.Drawing.Size(64, 24)
        Me.lblpostcode.TabIndex = 3
        Me.lblpostcode.Text = "Postcode : "
        Me.lblpostcode.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'lbladres
        '
        Me.lbladres.Location = New System.Drawing.Point(80, 152)
        Me.lbladres.Name = "lbladres"
        Me.lbladres.Size = New System.Drawing.Size(48, 16)
        Me.lbladres.TabIndex = 2
        Me.lbladres.Text = "Adres : "
        Me.lbladres.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'lblnaam
        '
        Me.lblnaam.Location = New System.Drawing.Point(72, 104)
        Me.lblnaam.Name = "lblnaam"
        Me.lblnaam.Size = New System.Drawing.Size(56, 16)
        Me.lblnaam.TabIndex = 1
        Me.lblnaam.Text = "Naam : "
        Me.lblnaam.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'lblstudnummer
        '
        Me.lblstudnummer.Location = New System.Drawing.Point(24, 32)
        Me.lblstudnummer.Name = "lblstudnummer"
        Me.lblstudnummer.Size = New System.Drawing.Size(104, 24)
        Me.lblstudnummer.TabIndex = 0
        Me.lblstudnummer.Text = "Studentennummer :"
        Me.lblstudnummer.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'frmStudent
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(832, 365)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Panel2, Me.Panel1})
        Me.Name = "frmStudent"
        Me.Text = "Student"
        Me.Panel1.ResumeLayout(False)
        Me.Panel2.ResumeLayout(False)
        Me.grpbgeslacht.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region



    Public Sub btnnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnew.Click
        Panel1.Enabled() = False
        Panel2.Enabled() = True
        clearFields()
        txtstudnum.Focus()
        teller = 0
        txtpaswoord.Show()
        lblpaswoord.Show()
    End Sub

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        Try
            studconn.Open()
            'het ophalen van onze naam voor verwijdering
            Dim intindex As Integer
            Dim strindex As String
            intindex = lstnaamstudent.SelectedValue
            strindex = lstnaamstudent.SelectedItems(intindex)
            studcmd = New OleDbCommand("SELECT userid FROM gebruikers WHERE userid = '" & strindex & "' ", studconn)
            studrdr = studcmd.ExecuteReader
            While studrdr.Read()
                strindex = studrdr("userid")
            End While
            studrdr.Close()
            studcmd = New OleDbCommand("DELETE FROM gebruikers WHERE userid = '" & strindex & "' ", studconn)
            studcmd.ExecuteNonQuery()
            studconn.Close()
            UpdateConnectie()

        Catch ex As OleDbException
            MessageBox.Show(" Er is een fout opgetreden met de database", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)

        End Try


    End Sub

    Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
        Panel1.Enabled() = False
        Panel2.Enabled() = True
        teller = 1
        Dim intindex As Integer
        Dim strindex, strindex1 As String
        intindex = lstnaamstudent.SelectedValue
        strindex = lstnaamstudent.SelectedItems(intindex)
        strindex1 = strindex

        Try
            studconn.Open()

            studcmd = New OleDbCommand("SELECT userid,naam,voornaam,adres,postcode,gemeente,email,geslacht,betaald FROM gebruikers WHERE userid = '" & strindex1 & "' ", studconn)
            studrdr = studcmd.ExecuteReader
            While studrdr.Read()
                txtstudnum.Text() = studrdr("userid")
                txtnaam.Text() = studrdr("naam")
                txtvoornaam.Text() = studrdr("voornaam")
                txtadres.Text() = studrdr("adres")
                txtpostcode.Text() = studrdr("postcode")
                txtgemeente.Text() = studrdr("gemeente")
                txtemail.Text() = studrdr("email")
                If studrdr("geslacht") = "man" Then
                    rbtnman.Checked() = True
                Else : rbtnvrouw.Checked() = True

                End If
                If studrdr("betaald") = "Ja" Then
                    chbbetaald.Checked() = True
                Else : chbbetaald.Checked() = False

                End If

            End While



            studrdr.Close()
            studconn.Close()
        Catch ex As OleDbException
            MessageBox.Show(" Er is een fout opgetreden met de database", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End Try
    End Sub


    'panel2 buttons

    Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        
        clearFields()
        Panel2.Enabled() = False
        Panel1.Enabled() = True
        UpdateConnectie()
        txtpaswoord.Hide()
        lblpaswoord.Hide()

    End Sub

    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

        Panel1.Enabled() = True
        Panel2.Enabled() = False
        Dim strgeslacht, strbetaald, testemail As String
        Dim i As Integer
        Dim test As Char()
        Dim emailok1, emailok2 As Boolean

        If rbtnman.Checked() = True Then
            strgeslacht = "man"
        Else : strgeslacht = "vrouw"

        End If

        If chbbetaald.Checked() = True Then
            strbetaald = "Ja"
        Else : strbetaald = "Nee"

        End If


        If txtemail.Text <> "" Then
            test = txtemail.Text().ToCharArray
            For i = 0 To test.GetUpperBound(0)
                If test(i) = "@" Then

                    emailok1 = True
                End If
                If test(i) = "." Then
                    emailok2 = True
                End If
            Next

            If emailok1 And emailok2 = False Then
                MessageBox.Show("Ongeldig email adres", "Fout", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                txtemail.Clear()
                txtemail.Focus()
            End If

        Else : txtemail.Text() = "voorbeeld@voorbeeld.com"

        End If
        Try
            studconn.Open()
            If teller = 0 Then
                studcmd = New OleDbCommand("INSERT INTO gebruikers(userid, naam, voornaam,adres, postcode, gemeente,email,geslacht,betaald, paswoord) values (' " & txtstudnum.Text & "','" & txtnaam.Text & "','" & txtvoornaam.Text & "','" & txtadres.Text & "','" & txtpostcode.Text & "','" & txtgemeente.Text & "', '" & txtemail.Text & "','" & strgeslacht & "' ,'" & strbetaald & "','" & txtpaswoord.Text & "' )", studconn)
                studcmd.ExecuteNonQuery()
                studconn.Close()
            Else
                studcmd = New OleDbCommand("UPDATE gebruikers SET naam = '" & txtnaam.Text & "', voornaam = '" & txtvoornaam.Text & "', adres = '" & txtadres.Text & "', postcode= '" & txtpostcode.Text & "', gemeente = '" & txtgemeente.Text & "', email= '" & txtemail.Text & "', geslacht ='" & strgeslacht & "' ,betaald= '" & strbetaald & "' WHERE userid = '" & txtstudnum.Text & " ' ", studconn)
                studcmd.ExecuteNonQuery()
                studconn.Close()

            End If

            UpdateConnectie()

        Catch ex As OleDbException
            MessageBox.Show(" Er is een fout opgetreden met de database", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)

        End Try
        clearFields()
        txtpaswoord.Hide()
        lblpaswoord.Hide()
    End Sub

    Public Sub UpdateConnectie()
        Try

            strconn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source = c:\vb\paswoord.mdb"
            studconn = New OleDbConnection(strconn)
            studconn.Open()
            studcmd = New OleDbCommand("select userid from gebruikers", studconn)
            studrdr = studcmd.ExecuteReader()
            lstnaamstudent.Items.Clear()
            While studrdr.Read()
                Dim strstud = studrdr.Item("userid")
                lstnaamstudent.Items.Add(strstud)
            End While
            studrdr.Close()
            studconn.Close()
        Catch ex As OleDbException
            MessageBox.Show(" Er is een fout opgetreden bij het initieren van de connectie:" & vbCrLf & ex.StackTrace, "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
        End Try
        Panel2.Enabled() = False
    End Sub
    Public Sub clearFields()
        txtstudnum.Clear()
        txtvoornaam.Clear()
        txtnaam.Clear()
        txtgemeente.Clear()
        txtpostcode.Clear()
        txtadres.Clear()
        txtemail.Clear()
        chbbetaald.Checked() = False
        rbtnman.Checked() = False
        rbtnvrouw.Checked() = False
    End Sub
    


End Class
