View difference between Paste ID: RHw9ampc and FViNnpVD

 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 
 
 Public Class Login
     Public Club As String
     Public AdminAccess As Boolean
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
 
     Private Sub Login_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'This sub creates the file and all of the tables when the program is first run. It also run time generates the whole login screen.
 
         Dim cat As Catalog = New Catalog()
         Dim SQLCommand As String
         Dim con As New OleDbConnection(ConnectionString)
         Dim cmd As New OleDbCommand
         Dim HashedAdminPWord As String = HashPassword("Password123")
 
 
         'This checks if the file already exists
         If File.Exists("N:\Whizzkids\WhizzkidsDB.accdb") = True Then
         Else
             cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source=N:\Whizzkids\WhizzkidsDB.accdb;")
 
             'This creates the table 'Staff' in the database
             SQLCommand = "CREATE TABLE Staff(StaffID AUTOINCREMENT PRIMARY KEY, StaffName VarChar(255), Username VarChar(255), PWord VarChar(255), Club VarChar(255), IsAdmin Bit);"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
 
             'This inserts the default information so you can log on the first time
             cmd.CommandText = "INSERT INTO Staff(StaffName, Username, PWord, Club, IsAdmin) VALUES ('AdminName', 'Admin', '" & HashedAdminPWord & "', '', True);"
             cmd.ExecuteNonQuery()
             con.Close()
 
             SQLCommand = "CREATE TABLE ParentLink(FamilyID VarChar(255) PRIMARY KEY, ChildNameOne VarChar(255), ChildNameTwo VarChar(255), ChildNameThree VarChar(255)," &
             "ChildNameFour VarChar(255));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE Register(ChildID VarChar(255) PRIMARY KEY, Present Bit, TimeIn Time, TimeOut Time, Club VarChar(255));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE PaymentLog(ParentID VarChar(255) PRIMARY KEY, ParentName VarChar(255), PaymentsOwed Decimal(5,2), AmountPaid Decimal(5,2), PaymentMethod VarChar(255));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE PaymentHistory(ParentID VarChar(255), AmountPaid Decimal(5,2), TransactionDate VarChar(255), PRIMARY KEY (ParentID, TransactionDate));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE ChildInformation(ChildID VarChar(255), ChildName VarChar(255), Age SmallInt, FamilyID VarChar(255), StaffID Integer, Club VarChar(255)," &
             "PRIMARY KEY (ChildName, FamilyID), FOREIGN KEY (StaffID) REFERENCES Staff(StaffID), FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID)," &
             "FOREIGN KEY (ChildID) REFERENCES Register(ChildID));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE ParentInformation(ParentName VarChar(255), ParentID VarChar(255), FamilyID VarChar(255), ContactNumber Float, Address VarChar(255)," &
             "PaymentsOwed Decimal(5,2), FamilyGP VarChar(255), PRIMARY KEY (ParentName, Address), FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID)," &
             "FOREIGN KEY (ParentID) REFERENCES PaymentLog(ParentID));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
 
         End If
 
 
 
 
         'Creation of title
         Dim lblTitle As New Label
         With lblTitle
             .Size = New Size(300, 50)
             .Location = New Size(80, 20)
             .Text = "Whizzkids Staff Login"
             .Font = New Font("Cambria", 22)
         End With
         'Creation of Username Box
         Dim txtUsername As New TextBox
         With txtUsername
             .Size = New Size(270, 20)
             .Location = New Size(81, 100)
             .Text = "Username"
             .ForeColor = Color.DimGray
             .Font = New Font("Calbri", 18)
             .Name = "UsernameBox"
         End With
         'Creation of Password Box
         Dim txtPassword As New TextBox
         With txtPassword
             .Size = New Size(270, 20)
             .Location = New Size(81, 157)
             .Text = "Password"
             .ForeColor = Color.DimGray
             .Font = New Font("Calbri", 18)
             .Name = "PasswordBox"
         End With
         'Creation of Login Button
         Dim btnLogin As New Button
         With btnLogin
             .Size = New Size(120, 50)
             .Location = New Point(160, 230)
             .Text = "Login"
             .Name = "LoginButton"
         End With
 
         'Adding all the runtime objects
         Me.Controls.Add(lblTitle)
         Me.Controls.Add(txtUsername)
         Me.Controls.Add(txtPassword)
         Me.Controls.Add(btnLogin)
 
         'Runs the subs when the boxes and buttons are clicked
         AddHandler btnLogin.Click, AddressOf LoginClick
         AddHandler txtUsername.Click, AddressOf ClickMouseUserName
         AddHandler txtPassword.Click, AddressOf ClickMousePassword
 
     End Sub
     Sub ClickMouseUserName()
         'This sub runs when the Username textbox is clicked.
 
         'This calls the class to change the password text to make it hidden
         Dim txtpassword As New StarText
         'If the Username textbox contains 'Username' when it is clicked, that is deleted and the text colour is set to black instead of grey
         If Me.Controls("UsernameBox").Text = "Username" Then
             Me.Controls("UsernameBox").Text = ""
             Me.Controls("UsernameBox").ForeColor = Color.Black
         End If
         'This places the 'Password' back in the Password textbox if it is empty when the Username box is clicked
         If Me.Controls("PasswordBox").Text = "" Then
             Me.Controls("PasswordBox").Text = "Password"
             Me.Controls("PasswordBox").ForeColor = Color.DimGray
             txtpassword.UnstarPassword(Me.Controls("PasswordBox"))
         End If
     End Sub
 
     Sub ClickMousePassword()
         'This sub does the same as the sub above, but for the other respective textboxes
 
         Dim txtpassword As New StarText
         If Me.Controls("PasswordBox").Text = "Password" Then
             Me.Controls("PasswordBox").Text = ""
             Me.Controls("PasswordBox").ForeColor = Color.Black
         End If
         If Me.Controls("UsernameBox").Text = "" Then
             Me.Controls("UsernameBox").Text = "Username"
             Me.Controls("UsernameBox").ForeColor = Color.DimGray
         End If
         txtpassword.StarPassword(Me.Controls("PasswordBox"))
     End Sub
     Sub LoginClick()
         'This sub validates the login with the Staff table
 
         Dim LoginUsername As String = ""
         Dim LoginPassword As String = ""
         Dim LoginCorrect As Boolean
         Dim ReaderAdmin As Boolean = False
         Dim ReaderClub As Boolean = False
         Dim cat As Catalog = New Catalog()
         Dim con As New OleDbConnection(ConnectionString)
         Dim cmd As New OleDbCommand
 
         'This searches the staff table for the correct Username and Password and gives the user, if an Admin, the option to select which club that they are at
         'Reading and writing from files - Grade B
         cmd.Connection = con
         cmd.CommandText = "SELECT Username, Pword, IsAdmin, Club FROM Staff WHERE Username = '" & Me.Controls("UsernameBox").Text & "'" &
         "AND PWord = '" & HashPassword(Me.Controls("PasswordBox").Text) & "'"
         con.Open()
         Dim reader As OleDbDataReader = cmd.ExecuteReader()
         Do While reader.Read()
             LoginUsername = reader("Username")
             LoginPassword = reader("PWord")
             ReaderAdmin = reader("IsAdmin")
         Loop
         If LoginUsername <> "" And LoginPassword <> "" Then
             LoginCorrect = True
             If ReaderAdmin = True Then
                 AdminAccess = True
                 Club_Selection.Show()
             Else
                 Main_Menu.Show()
             End If
             Me.Hide()
         Else
             MsgBox("Login details incorrect. Please try again.")
             LoginCorrect = False
         End If
 
 
         reader.Close()
         con.Close()
 
 
 
     End Sub
     Public Shared Function HashPassword(ByVal HPassword As String) As String
         'This sub is the hashing algorithm. It hashes the password when stored and hashes it when a login is attempted, so the true password is never visible anywhere
         'Hashing - Grade A
 
         Dim HashAlgorithm As New System.Security.Cryptography.MD5CryptoServiceProvider()
         Dim PasswordBytes() As Byte = System.Text.Encoding.ASCII.GetBytes(HPassword)
 
         PasswordBytes = HashAlgorithm.ComputeHash(PasswordBytes)
 
         Dim HashedPassword As String = ""
         Dim b As Byte
 
         For Each b In PasswordBytes
             HashedPassword += b.ToString("x2")
         Next
 
         Return HashedPassword
     End Function
 
 End Class
 Class StarText
     'These subs make the password textbox on the login visible and hidden when called
     Public Sub StarPassword(ByVal txtpassword As TextBox)
         txtpassword.UseSystemPasswordChar = True
     End Sub
     Public Sub UnstarPassword(ByVal txtpassword As TextBox)
         txtpassword.UseSystemPasswordChar = False
     End Sub
 End ClassImports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 
 
 Public Class Login
     Public Club As String
     Public AdminAccess As Boolean
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
 
     Private Sub Login_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'This sub creates the file and all of the tables when the program is first run. It also run time generates the whole login screen.
 
         Dim cat As Catalog = New Catalog()
         Dim SQLCommand As String
         Dim con As New OleDbConnection(ConnectionString)
         Dim cmd As New OleDbCommand
         Dim HashedAdminPWord As String = HashPassword("Password123")
 
 
         'This checks if the file already exists
         If File.Exists("N:\Whizzkids\WhizzkidsDB.accdb") = True Then
         Else
             cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source=N:\Whizzkids\WhizzkidsDB.accdb;")
 
             'This creates the table 'Staff' in the database
             SQLCommand = "CREATE TABLE Staff(StaffID AUTOINCREMENT PRIMARY KEY, StaffName VarChar(255), Username VarChar(255), PWord VarChar(255), Club VarChar(255), IsAdmin Bit);"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
 
             'This inserts the default information so you can log on the first time
             cmd.CommandText = "INSERT INTO Staff(StaffName, Username, PWord, Club, IsAdmin) VALUES ('AdminName', 'Admin', '" & HashedAdminPWord & "', '', True);"
             cmd.ExecuteNonQuery()
             con.Close()
 
             SQLCommand = "CREATE TABLE ParentLink(FamilyID VarChar(255) PRIMARY KEY, ChildNameOne VarChar(255), ChildNameTwo VarChar(255), ChildNameThree VarChar(255)," &
             "ChildNameFour VarChar(255));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE Register(ChildID VarChar(255) PRIMARY KEY, Present Bit, TimeIn Time, TimeOut Time, Club VarChar(255));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE PaymentLog(ParentID VarChar(255) PRIMARY KEY, ParentName VarChar(255), PaymentsOwed Decimal(5,2), AmountPaid Decimal(5,2), PaymentMethod VarChar(255));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE PaymentHistory(ParentID VarChar(255), AmountPaid Decimal(5,2), TransactionDate VarChar(255), PRIMARY KEY (ParentID, TransactionDate));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE ChildInformation(ChildID VarChar(255), ChildName VarChar(255), Age SmallInt, FamilyID VarChar(255), StaffID Integer, Club VarChar(255)," &
             "PRIMARY KEY (ChildName, FamilyID), FOREIGN KEY (StaffID) REFERENCES Staff(StaffID), FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID)," &
             "FOREIGN KEY (ChildID) REFERENCES Register(ChildID));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
             SQLCommand = "CREATE TABLE ParentInformation(ParentName VarChar(255), ParentID VarChar(255), FamilyID VarChar(255), ContactNumber Float, Address VarChar(255)," &
             "PaymentsOwed Decimal(5,2), FamilyGP VarChar(255), PRIMARY KEY (ParentName, Address), FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID)," &
             "FOREIGN KEY (ParentID) REFERENCES PaymentLog(ParentID));"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             Try
                 cmd.ExecuteNonQuery()
             Catch ex As Exception
             End Try
             con.Close()
 
 
         End If
 
 
 
 
         'Creation of title
         Dim lblTitle As New Label
         With lblTitle
             .Size = New Size(300, 50)
             .Location = New Size(80, 20)
             .Text = "Whizzkids Staff Login"
             .Font = New Font("Cambria", 22)
         End With
         'Creation of Username Box
         Dim txtUsername As New TextBox
         With txtUsername
             .Size = New Size(270, 20)
             .Location = New Size(81, 100)
             .Text = "Username"
             .ForeColor = Color.DimGray
             .Font = New Font("Calbri", 18)
             .Name = "UsernameBox"
         End With
         'Creation of Password Box
         Dim txtPassword As New TextBox
         With txtPassword
             .Size = New Size(270, 20)
             .Location = New Size(81, 157)
             .Text = "Password"
             .ForeColor = Color.DimGray
             .Font = New Font("Calbri", 18)
             .Name = "PasswordBox"
         End With
         'Creation of Login Button
         Dim btnLogin As New Button
         With btnLogin
             .Size = New Size(120, 50)
             .Location = New Point(160, 230)
             .Text = "Login"
             .Name = "LoginButton"
         End With
 
         'Adding all the runtime objects
         Me.Controls.Add(lblTitle)
         Me.Controls.Add(txtUsername)
         Me.Controls.Add(txtPassword)
         Me.Controls.Add(btnLogin)
 
         'Runs the subs when the boxes and buttons are clicked
         AddHandler btnLogin.Click, AddressOf LoginClick
         AddHandler txtUsername.Click, AddressOf ClickMouseUserName
         AddHandler txtPassword.Click, AddressOf ClickMousePassword
 
     End Sub
     Sub ClickMouseUserName()
         'This sub runs when the Username textbox is clicked.
 
         'This calls the class to change the password text to make it hidden
         Dim txtpassword As New StarText
         'If the Username textbox contains 'Username' when it is clicked, that is deleted and the text colour is set to black instead of grey
         If Me.Controls("UsernameBox").Text = "Username" Then
             Me.Controls("UsernameBox").Text = ""
             Me.Controls("UsernameBox").ForeColor = Color.Black
         End If
         'This places the 'Password' back in the Password textbox if it is empty when the Username box is clicked
         If Me.Controls("PasswordBox").Text = "" Then
             Me.Controls("PasswordBox").Text = "Password"
             Me.Controls("PasswordBox").ForeColor = Color.DimGray
             txtpassword.UnstarPassword(Me.Controls("PasswordBox"))
         End If
     End Sub
 
     Sub ClickMousePassword()
         'This sub does the same as the sub above, but for the other respective textboxes
 
         Dim txtpassword As New StarText
         If Me.Controls("PasswordBox").Text = "Password" Then
             Me.Controls("PasswordBox").Text = ""
             Me.Controls("PasswordBox").ForeColor = Color.Black
         End If
         If Me.Controls("UsernameBox").Text = "" Then
             Me.Controls("UsernameBox").Text = "Username"
             Me.Controls("UsernameBox").ForeColor = Color.DimGray
         End If
         txtpassword.StarPassword(Me.Controls("PasswordBox"))
     End Sub
     Sub LoginClick()
         'This sub validates the login with the Staff table
 
         Dim LoginUsername As String = ""
         Dim LoginPassword As String = ""
         Dim LoginCorrect As Boolean
         Dim ReaderAdmin As Boolean = False
         Dim ReaderClub As Boolean = False
         Dim cat As Catalog = New Catalog()
         Dim con As New OleDbConnection(ConnectionString)
         Dim cmd As New OleDbCommand
 
         'This searches the staff table for the correct Username and Password and gives the user, if an Admin, the option to select which club that they are at
         'Reading and writing from files - Grade B
         cmd.Connection = con
         cmd.CommandText = "SELECT Username, Pword, IsAdmin, Club FROM Staff WHERE Username = '" & Me.Controls("UsernameBox").Text & "'" &
         "AND PWord = '" & HashPassword(Me.Controls("PasswordBox").Text) & "'"
         con.Open()
         Dim reader As OleDbDataReader = cmd.ExecuteReader()
         Do While reader.Read()
             LoginUsername = reader("Username")
             LoginPassword = reader("PWord")
             ReaderAdmin = reader("IsAdmin")
         Loop
         If LoginUsername <> "" And LoginPassword <> "" Then
             LoginCorrect = True
             If ReaderAdmin = True Then
                 AdminAccess = True
                 Club_Selection.Show()
             Else
                 Main_Menu.Show()
             End If
             Me.Hide()
         Else
             MsgBox("Login details incorrect. Please try again.")
             LoginCorrect = False
         End If
 
 
         reader.Close()
         con.Close()
 
 
 
     End Sub
     Public Shared Function HashPassword(ByVal HPassword As String) As String
         'This sub is the hashing algorithm. It hashes the password when stored and hashes it when a login is attempted, so the true password is never visible anywhere
         'Hashing - Grade A
 
         Dim HashAlgorithm As New System.Security.Cryptography.MD5CryptoServiceProvider()
         Dim PasswordBytes() As Byte = System.Text.Encoding.ASCII.GetBytes(HPassword)
 
         PasswordBytes = HashAlgorithm.ComputeHash(PasswordBytes)
 
         Dim HashedPassword As String = ""
         Dim b As Byte
 
         For Each b In PasswordBytes
             HashedPassword += b.ToString("x2")
         Next
 
         Return HashedPassword
     End Function
 
 End Class
 Class StarText
     'These subs make the password textbox on the login visible and hidden when called
     Public Sub StarPassword(ByVal txtpassword As TextBox)
         txtpassword.UseSystemPasswordChar = True
     End Sub
     Public Sub UnstarPassword(ByVal txtpassword As TextBox)
         txtpassword.UseSystemPasswordChar = False
     End Sub
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Public Class Club_Selection
     'This form is only ever run if the user is an admin
 
     Dim cat As Catalog = New Catalog()
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim reader As OleDbDataReader
     Private Sub btnChooseCronton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseCronton.Click
         'When Cronton is selected the user's club is set to Cronton in the Staff table, so the system treats the user as a Cronton employee
 
         Main_Menu.Show()
         Me.Hide()
         Login.Club = "Cronton"
         'This is the updating of the users details in the Staff table
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "UPDATE Staff SET Club = '" & Login.Club & "' WHERE Username = '" & Login.Controls("UsernameBox").Text & "';"
         cmd.ExecuteNonQuery()
         con.Close()
     End Sub
 
     Private Sub btnChooseFarnworth_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseFarnworth.Click
         'When Farnworth is selected the user's club is set to Farnworth in the Staff table, so the system treats the user as a Farnworth employee
 
         Main_Menu.Show()
         Me.Hide()
         Login.Club = "Farnworth"
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "UPDATE Staff SET Club = '" & Login.Club & "' WHERE Username = '" & Login.Controls("UsernameBox").Text & "';"
         cmd.ExecuteNonQuery()
         con.Close()
     End Sub
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Public Class Main_Menu
     'This form loads the other forms when the respective button is clicked
 
     Dim cat As Catalog = New Catalog()
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim reader As OleDbDataReader
     Sub btnEditStaffInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditStaffInfo.Click
         'This form is only available to admins so it checks if the user is an admin before opening the form
 
         If Login.AdminAccess = True Then
             Edit_Staff_Info.Show()
         Else
             MsgBox("You do not have access to this page")
         End If
     End Sub
 
     Private Sub btnCurrentRegAndPay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCurrentRegAndPay.Click
         Current_Reg_And_Pay.Show()
     End Sub
 
     Private Sub btnParentInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParentInfo.Click
         Parent_Info.Show()
     End Sub
 
     Private Sub btnChildInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChildInfo.Click
         Child_Info.Show()
     End Sub
 
     Private Sub btnPaymentHistory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentHistory.Click
         Payment_History.Show()
     End Sub
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Imports System.Text.RegularExpressions
 Public Class Edit_Staff_Info
     'This form can be used by an administrator to add new staff, edit details about current staff or delete staff altogether
 
     Dim cat As Catalog = New Catalog()
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim SQLCommand As String = "SELECT * FROM Staff "
     Dim reader As OleDbDataReader
     Dim EditStaffID As Integer
     Dim EditStaffName, EditClub, EditPassword, EditUsername As String
     Dim EditAdmin As Boolean
     Dim PasswordCheckUCase As New Regex("(?=.*[A-Z])")
     Dim PasswordCheckNumber As New Regex("(?=.*[0-9])")
 
 
     Private Sub Edit_Staff_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'When the form loads, the refresh sub is called, which populates the table
 
         StaffInfoRefresh()
     End Sub
 
     Sub btnAddInformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddInformation.Click
         'This sub is called when the add information button is clicked. It checks if the information is in the correct form to be inserted into the database, using some regex, and also
         'checks if there is already a staff member with repeated attributes.
         'The staff ID is autoincremented when the data is inserted
 
         EditStaffName = txtStaffName.Text
         EditUsername = txtEditUserName.Text
         EditPassword = Login.HashPassword(txtEditPassword.Text)
         EditAdmin = chkAdmin.Checked
 
         'Checks if both Cronton and Farnworth are checked because the staff member cannot work at both clubs.
         If (chkCronton.Checked And chkFarnworth.Checked) = True Then
             EditClub = ""
         Else
             EditClub = ""
             If chkCronton.Checked = True Then
                 EditClub = "Cronton"
             End If
             If chkFarnworth.Checked = True Then
                 EditClub = "Farnworth"
             End If
         End If
 
         'This If statement validates that all the textboxes have been filled and that one of the clubs has been selected
         If EditStaffName.Length = 0 Or EditUsername.Length = 0 Or EditClub.Length = 0 Then
             MsgBox("Attempt failed. Please enter all the information correctly")
         Else
             If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
                 MsgBox("Your Password must contain at least one Capital letter and number")
             Else
                 If PasswordCheckNumber.IsMatch(txtEditPassword.Text) = False Then
                     MsgBox("Your Password must contain at least one Capital letter and number")
                 Else
                     If txtEditPassword.Text.Length < 6 Then
                         MsgBox("The password has to be 6 or more characters long")
                     Else
 
                         'Checks with the database if the username is already taken.
                         con.Open()
                         cmd.Connection = con
                         cmd.CommandText = "Select * FROM Staff WHERE Username = '" & EditUsername & "'"
                         cmd.ExecuteNonQuery()
                         reader = cmd.ExecuteReader
                         If reader.Read Then
                             MsgBox("That Username already exists")
                         Else
                             con.Close()
                             con.Open()
                             cmd.Connection = con
                             cmd.CommandText = "INSERT INTO Staff(StaffName, Username, PWord, Club, IsAdmin) VALUES ('" & EditStaffName & "','" & EditUsername & "'," &
                             "'" & EditPassword & "','" & EditClub & "', " & EditAdmin & ");"
                             cmd.ExecuteNonQuery()
                             con.Close()
                             MsgBox("Added")
                         End If
                     End If
                 End If
             End If
         End If
         con.Close()
         'Refreshes the table when the information has been added
         StaffInfoRefresh()
     End Sub
 
     Private Sub btnDeleteStaffMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteStaffMember.Click
         'This sub gives runs when the delete staff member button is clicked.
 
         'This reads from the staff table to check if the StaffID exists
         EditStaffID = txtStaffID.Text
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
         If reader.Read Then
             'This then deletes the the StaffID if it exists
             con.Close()
             con.Open()
             cmd.CommandText = "Delete FROM Staff WHERE StaffID = " & EditStaffID & ""
             cmd.ExecuteNonQuery()
             MsgBox("Deleted")
         Else
             MsgBox("StaffID doesn't exist")
         End If
         con.Close()
         StaffInfoRefresh()
     End Sub
     Private Sub btnSearchStaffMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchStaffMember.Click
         'This sub allows the user to search for a staff members record by using their name
 
         EditStaffName = txtStaffName.Text
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "SELECT * FROM Staff WHERE StaffName = '" & EditStaffName & "'"
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
         grdStaffInfo.Rows.Clear()
         If reader.Read Then
             'This empties the table and repopulates it with the matching staff name
             Dim n As Integer = grdStaffInfo.Rows.Add
             grdStaffInfo.Rows.Item(n).Cells(0).Value = (reader("StaffID"))
             grdStaffInfo.Rows.Item(n).Cells(1).Value = (reader("StaffName"))
             grdStaffInfo.Rows.Item(n).Cells(2).Value = (reader("Username"))
             grdStaffInfo.Rows.Item(n).Cells(3).Value = (reader("PWord"))
             grdStaffInfo.Rows.Item(n).Cells(4).Value = (reader("Club"))
             grdStaffInfo.Rows.Item(n).Cells(5).Value = (reader("IsAdmin"))
         Else
             MsgBox("Staff Name doesn't exist")
         End If
         con.Close()
     End Sub
     Sub StaffInfoRefresh()
         'This sub is called whenever the table needs to be updated to match the database
 
         grdStaffInfo.Rows.Clear()
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
         Do While reader.Read()
             Dim n As Integer = grdStaffInfo.Rows.Add
             grdStaffInfo.Rows.Item(n).Cells(0).Value = (reader("StaffID"))
             grdStaffInfo.Rows.Item(n).Cells(1).Value = (reader("StaffName"))
             grdStaffInfo.Rows.Item(n).Cells(2).Value = (reader("Username"))
             grdStaffInfo.Rows.Item(n).Cells(3).Value = (reader("PWord"))
             grdStaffInfo.Rows.Item(n).Cells(4).Value = (reader("Club"))
             grdStaffInfo.Rows.Item(n).Cells(5).Value = (reader("IsAdmin"))
         Loop
         con.Close()
     End Sub
 
     Private Sub btnEditExistingStaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingStaff.Click
         'This sub allows the user to edit all an existing staff members details, except the StaffID
 
         EditStaffID = txtStaffID.Text
         EditStaffName = txtStaffName.Text
         EditUsername = txtEditUserName.Text
         EditPassword = Login.HashPassword(txtEditPassword.Text)
         EditAdmin = chkAdmin.Checked
 
         If (chkCronton.Checked And chkFarnworth.Checked) = True Then
             EditClub = ""
         Else
             If chkCronton.Checked = True Then
                 EditClub = "Cronton"
             End If
             If chkFarnworth.Checked = True Then
                 EditClub = "Farnworth"
             End If
         End If
 
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
         If reader.Read Then
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "Select * FROM Staff WHERE Username = '" & EditUsername & "'"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
                 MsgBox("That Username already exists")
             Else
                 If EditStaffName.Length = 0 Or EditUsername.Length = 0 Or EditClub.Length = 0 Or EditStaffID = 0 Then
                     MsgBox("Attempt failed. Please enter all the information correctly")
                 Else
                     If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
                         MsgBox("Your Password must contain at least one Capital letter and number")
                     Else
                         If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
                             MsgBox("Your Password must contain at least one Capital letter and number")
                         Else
 
                             If txtEditPassword.Text.Length < 6 Then
                                 MsgBox("The password has to be 6 or more characters long")
                             Else
                                 con.Close()
                                 con.Open()
                                 cmd.Connection = con
                                 cmd.CommandText = "UPDATE Staff SET StaffName = '" & EditStaffName & "', Username = '" & EditUsername & "', Pword = '" & EditPassword & "'," &
                                 "Club = '" & EditClub & "', IsAdmin = " & EditAdmin & " WHERE StaffID = " & EditStaffID & ""
                                 cmd.ExecuteNonQuery()
                                 con.Close()
                                 MsgBox("Edited")
                             End If
                         End If
                     End If
                 End If
             End If
         Else
             MsgBox("That Staff ID doesn't exist")
         End If
 
         con.Close()
         StaffInfoRefresh()
     End Sub
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Public Class Child_Info
     Dim cat As Catalog = New Catalog()
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim reader As OleDbDataReader
     Dim SQLCommand As String
     Dim ChildID, ChildName, StaffID, FamilyID, Club, EditChildID, EditStaffID, EditFamilyID, EditClub, Age, EditAge As String
 
     Private Sub Child_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'This sub calls the refresh sub when the from is loaded
 
         ChildInfoRefresh()
     End Sub
 
     Sub ChildInfoRefresh()
         'This sub populates the Child Info table when called
 
         grdChildInfo.Rows.Clear()
         con.Open()
         cmd.Connection = con
         SQLCommand = "SELECT * FROM ChildInformation"
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         Do While reader.Read()
             Dim n As Integer = grdChildInfo.Rows.Add
             grdChildInfo.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
             grdChildInfo.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
             grdChildInfo.Rows.Item(n).Cells(2).Value = (reader("Age"))
             grdChildInfo.Rows.Item(n).Cells(3).Value = (reader("FamilyID"))
             grdChildInfo.Rows.Item(n).Cells(4).Value = (reader("StaffID"))
             grdChildInfo.Rows.Item(n).Cells(5).Value = (reader("Club"))
 
         Loop
         con.Close()
     End Sub
 
     Private Sub btnLinkAddNewChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLinkAddNewChild.Click
         'This sub shows the Add New Child form when the button is pressed. This is for if the child has no siblings currently attending.
 
         Add_New_Child.Show()
     End Sub
 
 
     Private Sub btnDeleteChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteChild.Click
         'This sub deletes the child from all the tables in the database and also checks if the child is the only child related to their parent
         'and if so, deletes the parents' records too.
 
         Dim FamilyID As String
         Dim ParentName As String
         ChildID = txtChildID.Text
         con.Close()
         con.Open()
         cmd.Connection = con
 
         cmd.CommandText = "Select * FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
         cmd.ExecuteNonQuery()
         con.Close()
         con.Open()
         reader = cmd.ExecuteReader
         If reader.Read Then
             con.Close()
             con.Open()
             cmd.CommandText = "Select ChildNameOne FROM ParentLink"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
                 con.Close()
                 con.Open()
                 cmd.CommandText = "Select FamilyID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
                 cmd.ExecuteNonQuery()
                 reader = cmd.ExecuteReader
                 reader.Read()
                 FamilyID = reader("FamilyID")
                 cmd.CommandText = "Select ParentName FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
                 con.Close()
                 con.Open()
                 cmd.ExecuteNonQuery()
                 reader = cmd.ExecuteReader
                 reader.Read()
                 ParentName = reader("ParentName")
                 cmd.CommandText = "Delete FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
                 con.Close()
                 con.Open()
                 cmd.ExecuteNonQuery()
                 cmd.CommandText = "Delete FROM PaymentLog WHERE ParentName = '" & ParentName & "'"
                 con.Close()
                 con.Open()
                 cmd.ExecuteNonQuery()
                 con.Close()
                 con.Open()
                 cmd.CommandText = "Delete FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
                 cmd.ExecuteNonQuery()
                 con.Close()
                 con.Open()
                 cmd.CommandText = "Delete FROM Register WHERE ChildID = '" & ChildID & "'"
                 cmd.ExecuteNonQuery()
                 MsgBox("Deleted")
                 cmd.CommandText = "Delete FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                 con.Close()
                 con.Open()
                 cmd.ExecuteNonQuery()
             Else
                 con.Close()
                 con.Open()
                 cmd.CommandText = "Delete FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
                 cmd.ExecuteNonQuery()
                 con.Close()
                 con.Open()
                 cmd.CommandText = "Delete FROM Register WHERE ChildID = '" & ChildID & "'"
                 cmd.ExecuteNonQuery()
                 MsgBox("Deleted")
             End If
         Else
             MsgBox("ChildID doesn't exist")
         End If
         con.Close()
         ChildInfoRefresh()
     End Sub
 
     Private Sub btnChildInfoSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChildInfoSearch.Click
         'This sub is run when the user searches for a childs' records by their child name and displays it in the grid
 
         ChildName = txtChildName.Text
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "SELECT * FROM ChildInformation WHERE ChildName = '" & ChildName & "'"
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
         grdChildInfo.Rows.Clear()
         If reader.Read Then
             Dim n As Integer = grdChildInfo.Rows.Add
             grdChildInfo.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
             grdChildInfo.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
             grdChildInfo.Rows.Item(n).Cells(2).Value = (reader("Age"))
             grdChildInfo.Rows.Item(n).Cells(3).Value = (reader("FamilyID"))
             grdChildInfo.Rows.Item(n).Cells(4).Value = (reader("StaffID"))
             grdChildInfo.Rows.Item(n).Cells(5).Value = (reader("Club"))
         Else
             MsgBox("Child Name doesn't exist")
         End If
         con.Close()
     End Sub
 
     Private Sub btnEditExistingChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingChild.Click
         'This sub allows the user to edit an already existing childs' information
 
         EditChildID = txtEditChildID.Text
         EditAge = txtEditAge.Text
         EditStaffID = txtEditStaffID.Text
         EditFamilyID = txtEditFamilyID.Text
 
         If (chkEditCrontonChild.Checked And chkEditFarnworthChild.Checked) = True Then
             EditClub = ""
         Else
             EditClub = ""
             If chkEditCrontonChild.Checked = True Then
                 EditClub = "Cronton"
             End If
             If chkEditFarnworthChild.Checked = True Then
                 EditClub = "Farnworth"
             End If
         End If
 
         If EditChildID.Length = 0 Or EditAge.ToString.Length = 0 Or EditStaffID.Length = 0 Or EditAge.ToString.Length > 2 Or EditFamilyID.Length = 0 Or EditClub.Length = 0 Then
             MsgBox("Attempt failed. Please enter all the information correctly")
         Else
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "Select * FROM ChildInformation WHERE ChildID = '" & EditChildID & "'"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
                 con.Close()
                 con.Open()
                 cmd.Connection = con
                 cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
                 cmd.ExecuteNonQuery()
                 reader = cmd.ExecuteReader
                 If reader.Read Then
                     con.Close()
                     con.Open()
                     cmd.Connection = con
                     cmd.CommandText = "Select * FROM ParentLink WHERE FamilyID = '" & EditFamilyID & "'"
                     cmd.ExecuteNonQuery()
                     reader = cmd.ExecuteReader
                     If reader.Read Then
                         con.Close()
                         con.Open()
                         cmd.Connection = con
                         cmd.CommandText = "UPDATE ChildInformation SET Age = '" & EditAge & "', Club = '" & EditClub & "', StaffID = '" & EditStaffID & "', FamilyID = '" & EditFamilyID & "'" &
                         "WHERE ChildID = '" & EditChildID & "'"
                         cmd.ExecuteNonQuery()
                         con.Close()
                         MsgBox("Edited")
                     Else
                         MsgBox("That Family ID doesn't exist")
                     End If
                 Else
                     MsgBox("That Staff ID doesn't exist")
                 End If
 
             Else
                 MsgBox("That Child ID doesn't exist")
             End If
         End If
         con.Close()
         ChildInfoRefresh()
     End Sub
 
     Private Sub btnLinkWithCurrentFamily_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLinkWithCurrentFamily.Click
         'This sub links a child to an already existing family
 
         ChildID = txtChildID.Text
         ChildName = txtChildName.Text
         Age = txtAge.Text
         StaffID = txtStaffID.Text
         FamilyID = txtFamilyID.Text
 
         If chkCrontonChild.Checked = True And chkFarnworthChild.Checked = True Then
             Club = ""
         Else
             Club = ""
             If chkCrontonChild.Checked = True Then
                 Club = "Cronton"
             End If
             If chkFarnworthChild.Checked = True Then
                 Club = "Farnworth"
             End If
         End If
 
         If ChildID.Length = 0 Or ChildName.Length = 0 Or Age.ToString.Length = 0 Or Age.ToString.Length > 2 Or StaffID.Length = 0 Or FamilyID.Length = 0 Or Club.Length = 0 Then
             MsgBox("Attempt failed. Please enter all the information correctly")
         Else
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & StaffID & ""
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
                 con.Close()
                 con.Open()
                 cmd.CommandText = "Select ChildNameOne FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                 cmd.ExecuteNonQuery()
                 reader = cmd.ExecuteReader
                 If reader.Read Then
                     con.Close()
                     con.Open()
                     cmd.CommandText = "INSERT INTO Register(ChildID, Club) VALUES ('" & ChildID & "', '" & Club & "');"
                     cmd.ExecuteNonQuery()
 
                     con.Close()
                     con.Open()
                     cmd.Connection = con
                     cmd.CommandText = "INSERT INTO ChildInformation(ChildID, ChildName, Age, FamilyID, StaffID, Club) VALUES ('" & ChildID & "','" & ChildName & "','" & Age & "','" & FamilyID & "'," &
                     "'" & StaffID & "','" & Club & "');"
                     cmd.ExecuteNonQuery()
 
                     con.Close()
                     con.Open()
                     cmd.CommandText = "Select ChildNameTwo FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                     cmd.ExecuteNonQuery()
                     reader = cmd.ExecuteReader
                     If reader.Read Then
                         con.Close()
                         con.Open()
                         cmd.CommandText = "Select ChildNameThree FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                         cmd.ExecuteNonQuery()
                         reader = cmd.ExecuteReader
                         If reader.Read Then
                             con.Close()
                             con.Open()
                             cmd.CommandText = "UPDATE ParentLink SET ChildNameFour = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
                             cmd.ExecuteNonQuery()
                         Else
                             con.Close()
                             con.Open()
                             cmd.CommandText = "UPDATE ParentLink SET ChildNameThree = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
                             cmd.ExecuteNonQuery()
                         End If
                     Else
                         con.Close()
                         con.Open()
                         cmd.CommandText = "UPDATE ParentLink SET ChildNameTwo = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
                         cmd.ExecuteNonQuery()
                     End If
                     con.Close()
                     ChildInfoRefresh()
                     MsgBox("Added")
                 Else
                     MsgBox("There is no current Family stored")
                 End If
             Else
                 MsgBox("That StaffID doesn't exist")
             End If
         End If
         con.Close()
     End Sub
 
     Private Sub btnStaffInfoRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStaffInfoRefresh.Click
         'This refreshes the table, for if you want the original table back after performing a search
         ChildInfoRefresh()
     End Sub
 
     Private Sub btnSortByName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByName.Click
         'This sub impliments a mergesort in order to sort the children into alphabetical order and display this in the grid
 
         Dim i As Integer = -1
         Dim n As Integer = 0
 
         SQLCommand = "SELECT ChildName FROM ChildInformation"
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         'This counts how many children are in the table
         Do While reader.Read()
             i = i + 1
         Loop
         con.Close()
 
         'Creates an array the size of the table
         Dim ChildArray(i) As String
 
         SQLCommand = "SELECT ChildName FROM ChildInformation"
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         'This adds the childrens names to the array
         Do While reader.Read()
             ChildArray(n) = (reader("ChildName"))
             n = n + 1
         Loop
         con.Close()
 
         'Mergesort - Grade A
         MergeSort(ChildArray)
 
         'This adds the sorted children back into the table
         For m = 0 To UBound(ChildArray)
             grdChildInfo.Rows.Item(m).Cells(1).Value = ChildArray(m)
         Next
 
         'Adds the rest of the informtion about the respective child back into the grid with the child name
         For j = 0 To UBound(ChildArray)
             SQLCommand = "SELECT ChildID, Age, FamilyID, StaffID, Club FROM ChildInformation WHERE ChildName = '" & ChildArray(j) & "'"
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             reader = cmd.ExecuteReader
             Do While reader.Read()
                 grdChildInfo.Rows.Item(j).Cells(0).Value = (reader("ChildID"))
                 grdChildInfo.Rows.Item(j).Cells(2).Value = (reader("Age"))
                 grdChildInfo.Rows.Item(j).Cells(3).Value = (reader("FamilyID"))
                 grdChildInfo.Rows.Item(j).Cells(4).Value = (reader("StaffID"))
                 grdChildInfo.Rows.Item(j).Cells(5).Value = (reader("Club"))
             Loop
             con.Close()
         Next
 
     End Sub
 
     Public Sub MergeSort(ByRef data As String())
         'This sub splits up the input data
 
         Dim currentSize As Integer
         Dim left As Integer
 
         currentSize = 1
         While currentSize <= data.Length - 1
             left = 0
             While left < data.Length - 1
                 Dim middle As Integer = left + currentSize - 1
                 Dim right As Integer = Math.Min(left + 2 * currentSize - 1, data.Length - 1)
 
                 Merge(data, left, middle, right)
                 left += 2 * currentSize
             End While
             currentSize = 2 * currentSize
         End While
     End Sub
 
     Private Sub Merge(ByRef data As String(), ByVal left As Integer, ByVal mid As Integer, ByVal right As Integer)
         'This sub compares and orders the values of the items
 
         Dim i As Integer
         Dim j As Integer
         Dim k As Integer
         Dim Number1 As String = mid - left + 1
         Dim Number2 As String = right - mid
         Dim LeftPointer As String() = New String(Number1 - 1) {}
         Dim RightPointer As String() = New String(Number2 - 1) {}
 
         For i = 0 To Number1 - 1
             LeftPointer(i) = data(left + i)
         Next
 
         For j = 0 To Number2 - 1
             RightPointer(j) = data(mid + 1 + j)
         Next
 
         i = 0
         j = 0
         k = left
 
         While i < Number1 AndAlso j < Number2
             If LeftPointer(i) <= RightPointer(j) Then
                 data(k) = LeftPointer(i)
                 i += 1
             Else
                 data(k) = RightPointer(j)
                 j += 1
             End If
 
             k += 1
         End While
 
         While i < Number1
             data(k) = LeftPointer(i)
             i += 1
             k += 1
         End While
 
         While j < Number2
             data(k) = RightPointer(j)
             j += 1
             k += 1
         End While
     End Sub
 
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Public Class Add_New_Child
     Dim cat As Catalog = New Catalog()
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim reader As OleDbDataReader
     Dim SQLCommand As String
     Dim AddFamilyID, AddClub, AddStaffID, AddChildName, AddParentName, AddAddress, AddFamilyGP, AddAge, AddContactNumber As String
 
 
     Sub btnAddNewChildToSystem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNewChildToSystem.Click
         'This sub adds a new child, along with their parent to the database
 
         Dim NewParentID As String
         Dim NewChildID As String
         AddChildName = txtChildName.Text
         AddAge = txtAge.Text
         AddStaffID = txtStaffID.Text
         AddFamilyID = txtFamilyID.Text
         AddParentName = txtParentName.Text
         AddAddress = txtAddress.Text
         AddFamilyGP = txtFamilyGP.Text
         AddContactNumber = txtContactNumber.Text
 
 
 
         If chkCrontonChild.Checked = True And chkFarnworthChild.Checked = True Then
             AddClub = ""
         Else
             AddClub = ""
             If chkCrontonChild.Checked = True Then
                 AddClub = "Cronton"
             End If
             If chkFarnworthChild.Checked = True Then
                 AddClub = "Farnworth"
             End If
         End If
 
         'Validation to check the user has inputted information correctly
         If AddChildName.Length = 0 Then
             MsgBox("Please enter the Child Name correctly")
         ElseIf AddAge.ToString.Length > 2 Then
             MsgBox("Please enter a correct age")
         ElseIf AddAge.ToString.Length = 0 Then
             MsgBox("Please enter a correct age")
         ElseIf AddStaffID.Length = 0 Then
             MsgBox("Please enter a valid Staff ID")
         ElseIf IsNumeric(AddStaffID) = False Then
             MsgBox("Please enter a valid Staff ID")
         ElseIf AddFamilyID.Length = 0 Then
             MsgBox("Please enter the Family ID correctly")
         ElseIf AddParentName.Length = 0 Then
             MsgBox("Please enter the Parent Name correctly")
         ElseIf AddAddress.Length = 0 Then
             MsgBox("Please enter the address correctly")
         ElseIf AddFamilyGP.Length = 0 Then
             MsgBox("Please enter the GP's Name correctly")
         ElseIf AddClub.Length = 0 Then
             MsgBox("Please tick one club")
         ElseIf IsNumeric(AddAge) = False Then
             MsgBox("Please enter a number for the age")
         ElseIf IsNumeric(AddContactNumber) = False Then
             MsgBox("Please eneter a correct contact number")
         ElseIf AddContactNumber.ToString.Length < 7 Then
             MsgBox("That contact number is too short")
         ElseIf AddContactNumber.ToString.Length > 11 Then
             MsgBox("That contact number is too long")
         Else
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & AddStaffID & ""
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
                 con.Close()
                 con.Open()
                 cmd.Connection = con
                 cmd.CommandText = "Select * FROM ParentLink WHERE FamilyID = '" & AddFamilyID & "'"
                 cmd.ExecuteNonQuery()
                 reader = cmd.ExecuteReader
                 If reader.Read Then
                     MsgBox("That Family ID already exists")
                 Else
                     con.Close()
                     con.Open()
                     cmd.Connection = con
                     NewChildID = GenerateChildID()
                     NewParentID = GenerateParentID()
                     cmd.CommandText = "INSERT INTO ParentLink(FamilyID, ChildNameOne) VALUES ('" & AddFamilyID & "','" & AddChildName & "');"
                     cmd.ExecuteNonQuery()
                     cmd.CommandText = "INSERT INTO Register(ChildID, Club) VALUES ('" & NewChildID & "','" & AddClub & "');"
                     cmd.ExecuteNonQuery()
                     cmd.CommandText = "INSERT INTO PaymentLog(ParentID, ParentName, PaymentsOwed) VALUES('" & NewParentID & "','" & AddParentName & "', '0');"
                     cmd.ExecuteNonQuery()
                     cmd.CommandText = "INSERT INTO ChildInformation(ChildID, ChildName, Age, FamilyID, StaffID, Club) VALUES ('" & NewChildID & "','" & AddChildName & "','" & AddAge & "'," &
                     "'" & AddFamilyID & "','" & AddStaffID & "','" & AddClub & "');"
                     cmd.ExecuteNonQuery()
                     cmd.CommandText = "INSERT INTO ParentInformation(ParentName, Address, ParentID, ContactNumber, FamilyGP, FamilyID, PaymentsOwed) VALUES ('" & AddParentName & "','" & AddAddress & "'," &
                     "'" & NewParentID & "','" & AddContactNumber & "','" & AddFamilyGP & "','" & AddFamilyID & "', '0');"
                     cmd.ExecuteNonQuery()
                     con.Close()
                     MsgBox("Added")
                 End If
             Else
                 MsgBox("That Staff ID doesn't exist")
             End If
 
         End If
 
         Child_Info.ChildInfoRefresh()
     End Sub
     Function GenerateParentID()
         'Generates a Unique parent ID using their name
 
         Dim NewParentID As String
         Dim ParentID As String
         Dim clash As Boolean = True
         Dim value As Integer
         con.Close()
         con.Open()
         ParentID = AddParentName
         value = 0
         NewParentID = ParentID & value
 
         'This adds a number onto the end of the parent name and checks if this is already in the database.
         'If so, it tries the next number up and so on.
 
         Do While clash = True
             con.Close()
             con.Open()
             value = value + 1
             NewParentID = ParentID & value
             cmd.CommandText = "Select ParentID FROM PaymentLog WHERE ParentID = '" & NewParentID & "';"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
             Else
                 clash = False
             End If
         Loop
         con.Close()
         con.Open()
         Return NewParentID
     End Function
 
     Function GenerateChildID()
         'Generates a unique child ID for the child
 
         Dim NewChildID As String
         Dim ChildID As String
         Dim clash As Boolean = True
         Dim value As Integer
         con.Close()
         con.Open()
         ChildID = AddChildName
         value = 0
         NewChildID = ChildID & value
         Do While clash = True
             con.Close()
             con.Open()
             value = value + 1
             NewChildID = ChildID & value
             cmd.CommandText = "Select ChildID FROM Register WHERE ChildID = '" & NewChildID & "';"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
             Else
                 clash = False
             End If
         Loop
         con.Close()
         con.Open()
         Return NewChildID
     End Function
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Public Class Parent_Info
     Dim cat As Catalog = New Catalog()
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim reader As OleDbDataReader
     Dim SQLCommand As String = "SELECT * FROM ParentInformation"
     Dim ParentName, Address, FamilyGP, FamilyID, PaymentsOwed, ContactNumber As String
 
     Private Sub Parent_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'This calls the sub to load the table into the grid
 
         'This sets the payments owed column to display as a currency
         grdParentInfo.Columns(4).DefaultCellStyle.Format = "c"
         ParentInfoRefresh()
     End Sub
 
     Sub ParentInfoRefresh()
         'This sub populates the grid
 
         grdParentInfo.Rows.Clear()
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         Do While reader.Read()
             Dim n As Integer = grdParentInfo.Rows.Add
             grdParentInfo.Rows.Item(n).Cells(0).Value = (reader("ParentName"))
             grdParentInfo.Rows.Item(n).Cells(1).Value = (reader("Address"))
             grdParentInfo.Rows.Item(n).Cells(2).Value = (reader("ParentID"))
             grdParentInfo.Rows.Item(n).Cells(3).Value = (reader("ContactNumber"))
             grdParentInfo.Rows.Item(n).Cells(4).Value = (reader("PaymentsOwed"))
             grdParentInfo.Rows.Item(n).Cells(5).Value = (reader("FamilyGP"))
             grdParentInfo.Rows.Item(n).Cells(6).Value = (reader("FamilyID"))
 
         Loop
         con.Close()
     End Sub
 
     Private Sub btnEditExistingParent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingParent.Click
         'This sub allows the user to edit an existing parents details
 
         ParentName = txtParentName.Text
         Address = txtAddress.Text
         ContactNumber = txtContactNumber.Text
         PaymentsOwed = txtPaymentsOwed.Text
         FamilyGP = txtFamilyGP.Text
 
 
         If ParentName.Length = 0 Then
             MsgBox("Please enter Parent Name correctly")
         ElseIf Address.Length = 0 Then
             MsgBox("Please enter a valid address")
         ElseIf IsNumeric(ContactNumber) = False Then
             MsgBox("Please enter a valid number for the contact number")
         ElseIf ContactNumber.Length > 11 Then
             MsgBox("That contact number is too long")
         ElseIf ContactNumber.Length < 7 Then
             MsgBox("That contact number is too short")
         ElseIf IsNumeric(PaymentsOwed) = False Then
             MsgBox("Please enter a number for the payments owed")
         ElseIf PaymentsOwed.Length = 0 Then
             MsgBox("That payment owed is not valid")
         ElseIf FamilyGP.Length = 0 Then
             MsgBox("Please enter the family GP correctly")
         Else
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "Select * FROM ParentInformation WHERE ParentName = '" & ParentName & "'"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             If reader.Read Then
                 con.Close()
                 con.Open()
                 cmd.Connection = con
                 cmd.CommandText = "UPDATE ParentInformation SET Address = '" & Address & "', ContactNumber = '" & ContactNumber & "', PaymentsOwed = '" & PaymentsOwed & "', FamilyGP = '" & FamilyGP & "'," &
                 "WHERE ParentName = '" & ParentName & "'"
                 cmd.ExecuteNonQuery()
                 cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & PaymentsOwed & "' WHERE ParentName = '" & ParentName & "'"
                 cmd.ExecuteNonQuery()
                 con.Close()
                 MsgBox("Edited")
             Else
                 MsgBox("That Parent Name doesn't exist")
             End If
 
         End If
 
             con.Close()
             ParentInfoRefresh()
             Current_Reg_And_Pay.CurrentRegAndPayRefresh()
     End Sub
 
     Private Sub btnSearchParent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchParent.Click
         'Allows the user to search for a parents records by their name
 
         ParentName = txtParentName.Text
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "SELECT * FROM ParentInformation WHERE ParentName = '" & ParentName & "'"
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
         grdParentInfo.Rows.Clear()
         If reader.Read Then
             Dim n As Integer = grdParentInfo.Rows.Add
             grdParentInfo.Rows.Item(n).Cells(0).Value = (reader("ParentName"))
             grdParentInfo.Rows.Item(n).Cells(1).Value = (reader("Address"))
             grdParentInfo.Rows.Item(n).Cells(2).Value = (reader("ParentID"))
             grdParentInfo.Rows.Item(n).Cells(3).Value = (reader("ContactNumber"))
             grdParentInfo.Rows.Item(n).Cells(4).Value = (reader("PaymentsOwed"))
             grdParentInfo.Rows.Item(n).Cells(5).Value = (reader("FamilyGP"))
             grdParentInfo.Rows.Item(n).Cells(6).Value = (reader("FamilyID"))
         Else
             con.Close()
             ParentInfoRefresh()
             MsgBox("Parent Name doesn't exist")
         End If
         con.Close()
     End Sub
 
 
     Private Sub btnParentInfoRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParentInfoRefresh.Click
         'Runs the refresh sub
 
         ParentInfoRefresh()
     End Sub
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Public Class Current_Reg_And_Pay
     Dim cat As Catalog = New Catalog()
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim reader As OleDbDataReader
     Dim SQLCommand As String
     Dim Club As String = Login.Club
     Dim ChildID, CurrentTime, ParentName, ChildName, AmountPaid, PaymentMethod, ParentID, FamilyID, CurrentPaymentsOwed, NewPaymentsOwed, TransactionDate As String
 
     Sub CurrentRegAndPayRefresh()
         'This sub fills the grid with the table information
 
         GrdRegister.Rows.Clear()
         'This command takes the child name from child information and put it into the register table, so that the user can search for the child by their name.
         'It also only populates the register with the children which go the the club that the admin chose in the club selection screen, or whichever club the user works at
         SQLCommand = "SELECT Register.*, ChildInformation.ChildName FROM Register INNER JOIN ChildInformation ON Register.ChildID = ChildInformation.ChildID WHERE Register.Club = '" & Club & "'"
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         Do While reader.Read()
             Dim n As Integer = GrdRegister.Rows.Add
             GrdRegister.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
             GrdRegister.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
             GrdRegister.Rows.Item(n).Cells(2).Value = (reader("TimeIn"))
             GrdRegister.Rows.Item(n).Cells(3).Value = (reader("TimeOut"))
             GrdRegister.Rows.Item(n).Cells(4).Value = (reader("Present"))
         Loop
         con.Close()
 
         GrdPaymentLog.Rows.Clear()
         SQLCommand = "SELECT * FROM PaymentLog "
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         Do While reader.Read()
             Dim n As Integer = GrdPaymentLog.Rows.Add
             GrdPaymentLog.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
             GrdPaymentLog.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
             GrdPaymentLog.Rows.Item(n).Cells(2).Value = (reader("PaymentsOwed"))
             GrdPaymentLog.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
             GrdPaymentLog.Rows.Item(n).Cells(4).Value = (reader("PaymentMethod"))
         Loop
         con.Close()
     End Sub
 
     Private Sub Current_Reg_And_Pay_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'This sub calls the refresh sub
 
         'Formats the data grids to show the time in the below format and also sets the money columns to display as a currency
         GrdRegister.Columns(2).DefaultCellStyle.Format = "HH:mm:ss"
         GrdRegister.Columns(3).DefaultCellStyle.Format = "HH:mm:ss"
         GrdPaymentLog.Columns(2).DefaultCellStyle.Format = "c"
         GrdPaymentLog.Columns(3).DefaultCellStyle.Format = "c"
         CurrentRegAndPayRefresh()
     End Sub
 
     Private Sub btnDeleteChildRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteChildRegister.Click
         'Gives the user a quick link to the child information table, if edits need to be made
 
         Child_Info.Show()
     End Sub
 
     Private Sub btnCurrentRegAndPayRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCurrentRegAndPayRefresh.Click
         'Button calls the refresh sub
 
         CurrentRegAndPayRefresh()
     End Sub
 
     Private Sub btnMarkIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMarkIn.Click
         'This sub will mark the child that is selected as present in the club and prints in the data base the time that he arrived
 
         ChildID = txtRegChildID.Text
         'This gets the current system time
         CurrentTime = Now.ToLongTimeString
 
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "SELECT ChildID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
 
         If reader.Read Then
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "UPDATE Register SET TimeIn = '" & CurrentTime & "', Present = True WHERE ChildID = '" & ChildID & "'"
             cmd.ExecuteNonQuery()
             con.Close()
 
             CurrentRegAndPayRefresh()
             MsgBox("Marked In")
         Else
             MsgBox("Child ID doesn't exist")
         End If
 
         con.Close()
     End Sub
 
     Private Sub btnMarkOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMarkOut.Click
         'This sub marks the selected child as no longer present and prints the time of depature in the table.
         'The Parent that is linked to the child has the set amount of £8.50 added to the amount owed to the club
 
         ChildID = txtRegChildID.Text
         CurrentTime = Now.ToLongTimeString
 
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "SELECT ChildID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
 
         If reader.Read Then
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "UPDATE Register SET TimeOut = '" & CurrentTime & "', Present = False WHERE ChildID = '" & ChildID & "'"
             cmd.ExecuteNonQuery()
             con.Close()
 
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "SELECT FamilyID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             reader.Read()
             FamilyID = reader("FamilyID")
             con.Close()
 
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "SELECT ParentID FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             reader.Read()
             ParentID = reader("ParentID")
             con.Close()
 
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "SELECT PaymentsOwed FROM PaymentLog WHERE ParentID = '" & ParentID & "'"
             cmd.ExecuteNonQuery()
             reader = cmd.ExecuteReader
             reader.Read()
             CurrentPaymentsOwed = reader("PaymentsOwed")
             con.Close()
 
             NewPaymentsOwed = CurrentPaymentsOwed + (8.5)
 
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
             cmd.ExecuteNonQuery()
             con.Close()
 
             con.Open()
             cmd.Connection = con
             cmd.CommandText = "UPDATE ParentInformation SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
             cmd.ExecuteNonQuery()
             con.Close()
 
             CurrentRegAndPayRefresh()
             MsgBox("Marked Out")
         Else
             MsgBox("Child ID doesn't exist")
         End If
         con.Close()
     End Sub
 
     Private Sub btnPaymentLogSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentLogSearch.Click
         'Allows the user to search for a parents information by their name
 
         ParentName = txtParentName.Text
 
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "SELECT * FROM PaymentLog WHERE ParentName = '" & ParentName & "'"
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
 
         GrdPaymentLog.Rows.Clear()
         If reader.Read Then
             Dim n As Integer = GrdPaymentLog.Rows.Add
             GrdPaymentLog.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
             GrdPaymentLog.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
             GrdPaymentLog.Rows.Item(n).Cells(2).Value = (reader("PaymentsOwed"))
             GrdPaymentLog.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
             GrdPaymentLog.Rows.Item(n).Cells(4).Value = (reader("PaymentMethod"))
         Else
             MsgBox("Parent Name doesn't exist")
         End If
         con.Close()
     End Sub
 
     Private Sub btnRegisterSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegisterSearch.Click
         'This allows the user to search for the child information by the child name
 
         ChildName = txtChildName.Text
 
         con.Open()
         cmd.Connection = con
         'Cross table SQL - Grade A
         SQLCommand = "SELECT Register.*, ChildInformation.ChildName FROM Register INNER JOIN ChildInformation ON Register.ChildID = ChildInformation.ChildID" &
         "WHERE Register.Club = '" & Club & "' AND ChildName = '" & ChildName & "'"
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         GrdRegister.Rows.Clear()
         If reader.Read() Then
             Dim n As Integer = GrdRegister.Rows.Add
             GrdRegister.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
             GrdRegister.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
             GrdRegister.Rows.Item(n).Cells(2).Value = (reader("TimeIn"))
             GrdRegister.Rows.Item(n).Cells(3).Value = (reader("TimeOut"))
             GrdRegister.Rows.Item(n).Cells(4).Value = (reader("Present"))
         Else
             MsgBox("Child Name doesn't exist")
         End If
         con.Close()
     End Sub
 
     Private Sub btnMakePayment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMakePayment.Click
         'This sub allows the parent to pay any amount of the money the owe to the club
         'It updates the payment log, writing the new amount owed, amount paid and the date of the transaction
         'It also adds the payment to the payment history table
 
         ParentID = txtParentID.Text
         AmountPaid = txtAmountPaid.Text
         'Gets the current system date
         TransactionDate = String.Format("{0:dd/MM/yyyy}", DateTime.Now)
 
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "SELECT * FROM ParentInformation WHERE ParentID = '" & ParentID & "'"
         cmd.ExecuteNonQuery()
         reader = cmd.ExecuteReader
         If reader.Read Then
 
             If IsNumeric(AmountPaid) = True Then
 
 
                 If (chkCard.Checked And chkCash.Checked) Or (chkCard.Checked And chkCheque.Checked) Or (chkCash.Checked And chkCheque.Checked) Or (chkCard.Checked And chkCash.Checked And chkCheque.Checked) = True Then
                     PaymentMethod = ""
                 Else
                     If chkCard.Checked Or chkCash.Checked Or chkCheque.Checked = True Then
                         If chkCard.Checked = True Then
                             PaymentMethod = "Card"
                         End If
                         If chkCash.Checked = True Then
                             PaymentMethod = "Cash"
                         End If
                         If chkCheque.Checked = True Then
                             PaymentMethod = "Cheque"
                         End If
                     Else
                         PaymentMethod = ""
                     End If
                 End If
 
                 con.Close()
                 con.Open()
                 cmd.Connection = con
                 cmd.CommandText = "SELECT * FROM PaymentHistory WHERE ParentID = '" & ParentID & "' AND TransactionDate = '" & TransactionDate & "'"
                 cmd.ExecuteNonQuery()
                 reader = cmd.ExecuteReader
                 If reader.Read Then
                     MsgBox("You have already made a payment today")
                 Else
 
                     If PaymentMethod.Length <> 0 Then
 
                         con.Close()
                         con.Open()
                         cmd.Connection = con
                         cmd.CommandText = "SELECT PaymentsOwed FROM PaymentLog WHERE ParentID = '" & ParentID & "'"
                         cmd.ExecuteNonQuery()
                         reader = cmd.ExecuteReader
                         reader.Read()
                         CurrentPaymentsOwed = reader("PaymentsOwed")
                         con.Close()
 
                         NewPaymentsOwed = CurrentPaymentsOwed - AmountPaid
 
                         con.Open()
                         cmd.Connection = con
                         cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & NewPaymentsOwed & "', AmountPaid = '" & AmountPaid & "', PaymentMethod = '" & PaymentMethod & "' WHERE ParentID = '" & ParentID & "'"
                         cmd.ExecuteNonQuery()
                         con.Close()
 
                         con.Open()
                         cmd.Connection = con
                         cmd.CommandText = "INSERT INTO PaymentHistory(ParentID, AmountPaid, TransactionDate) VALUES ('" & ParentID & "', '" & AmountPaid & "', '" & TransactionDate & "');"
                         cmd.ExecuteNonQuery()
                         con.Close()
 
                         con.Open()
                         cmd.Connection = con
                         cmd.CommandText = "UPDATE ParentInformation SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
                         cmd.ExecuteNonQuery()
                         con.Close()
 
                         CurrentRegAndPayRefresh()
                         MsgBox("Payment Made")
                     Else
                         MsgBox("Please tick only one of the boxes")
                     End If
 
                 End If
             Else
                 MsgBox("Please enter a number in 'Amount Paid'")
             End If
         Else
             MsgBox("Parent ID doesn't exist")
         End If
         con.Close()
     End Sub
 
     Private Sub bntClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntClear.Click
         'This button clears the register and the payment log, but leaves in the people, so it can be used again the next day
 
         TransactionDate = String.Format("{0:dd/MM/yyyy}", DateTime.Now)
 
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "UPDATE PaymentLog SET AmountPaid = NULL, PaymentMethod = NULL"
         cmd.ExecuteNonQuery()
         con.Close()
 
         con.Open()
         cmd.Connection = con
         cmd.CommandText = "UPDATE Register SET TimeIn = NULL, TimeOut = NULL"
         cmd.ExecuteNonQuery()
         con.Close()
 
         CurrentRegAndPayRefresh()
     End Sub
 End Class
 
 Imports ADOX
 Imports System.IO
 Imports System.Data.OleDb
 Public Class Payment_History
     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
     Dim cat As Catalog = New Catalog()
     Dim SQLCommand As String
     Dim con As New OleDbConnection(ConnectionString)
     Dim cmd As New OleDbCommand
     Dim reader As OleDbDataReader
     Dim ParentName As String
     Dim ParentID As String
     Dim TotalPaid As Decimal
 
     Private Sub Payment_History_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'Formats the columns and calls the refresh sub
 
         grdPaymentHistory.Columns(2).DefaultCellStyle.Format = "dd/MM/yyyy"
         grdPaymentHistory.Columns(3).DefaultCellStyle.Format = "c"
         PaymentHistoryRefresh()
     End Sub
 
     Sub PaymentHistoryRefresh()
         'Refreshes the grid
 
         grdPaymentHistory.Rows.Clear()
         SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID"
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         Do While reader.Read()
             Dim n As Integer = grdPaymentHistory.Rows.Add
             grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
             grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
             grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
             grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
         Loop
         con.Close()
     End Sub
 
     Private Sub btnPaymentLogSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentLogSearch.Click
         'Allows the user to search for all the payments made by the parent name
 
         ParentName = txtParentName.Text
         grdPaymentHistory.Rows.Clear()
         SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID WHERE PaymentLog.ParentName = '" & ParentName & "'"
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         If reader.Read() Then
             Dim n As Integer = grdPaymentHistory.Rows.Add
             grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
             grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
             grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
             grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
         Else
             con.Close()
             PaymentHistoryRefresh()
             MsgBox("No transactions made by that Parent Name")
         End If
         con.Close()
     End Sub
 
     Private Sub btnTransactionDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByTransactionDate.Click
         'This sub orders the transactions by date, so the user can see all of the transactions made on a particlar day
 
         grdPaymentHistory.Rows.Clear()
 
         'Aggregate SQL function - Grade A
         SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID ORDER BY PaymentHistory.TransactionDate ASC"
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         Do While reader.Read()
             Dim n As Integer = grdPaymentHistory.Rows.Add
             grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
             grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
             grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
             grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
         Loop
         con.Close()
     End Sub
 
     Private Sub btnSortByCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByCustomer.Click
         'Calls the refresh sub
 
         PaymentHistoryRefresh()
     End Sub
 
     Private Sub btnTotalPaid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotalPaid.Click
         'This sub totals all the payments made by a parent ID
 
         ParentID = txtParentID.Text
 
         SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID WHERE PaymentLog.ParentID = '" & ParentID & "'"
         con.Close()
         con.Open()
         cmd.Connection = con
         cmd.CommandText = (SQLCommand)
         reader = cmd.ExecuteReader
 
         If reader.Read() Then
 
             'Aggregate SQL function - Grade A
             SQLCommand = "SELECT SUM(AmountPaid) FROM PaymentHistory WHERE ParentID = '" & ParentID & "'"
             con.Close()
             con.Open()
             cmd.Connection = con
             cmd.CommandText = (SQLCommand)
             reader = cmd.ExecuteReader
             reader.Read()
             'Sets the textbox to currency format
             txtTotalPaid.Text = FormatCurrency(reader.Item(0))
             con.Close()
 
         Else
             MsgBox("No transactions made by that Parent ID")
         End If
         con.Close()
     End Sub
 End Class