Public Class form_Login

    Dim ConnectedDBLogin As New OleDb.OleDbConnection
    Dim Username As String
    Dim FirstCharacter As String
    Dim SecondCharacter As String
    Dim ThirdCharacter As String
    Dim ThreeCharacters As String
    Dim Age As Integer
    Dim AutoNumber As Integer
    Dim Password As String

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        label_LoginInvalid.Text = ""
        label_RegisterInvalid.Text = ""

        Try
            ConnectedDBLogin = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Brad\Documents\Visual Studio 2017\Projects\NEA Quiz\NEA Quiz Database.accdb;Persist Security Info=False;")
            Try
                Call ConnectedDBLogin.Open()
            Catch ex As Exception
                label_LoginInvalid.Text = "Cannot connect to database."
            End Try

            If ConnectedDBLogin.State = ConnectionState.Open Then
                Dim SQL As New OleDb.OleDbCommand("SELECT * FROM table_Logins", ConnectedDBLogin)
                Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                Dim DataTable As New DataTable("Login")
                DataAdapter.Fill(DataTable)
                dataGridView_Login.DataSource = DataTable
            End If

        Catch ex As Exception
            label_LoginInvalid.Text = "Cannot connect to database."
        End Try
    End Sub

    Private Sub button_Login_Click(sender As Object, e As EventArgs) Handles button_Login.Click

        Try
            ConnectedDBLogin = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Brad\Documents\Visual Studio 2017\Projects\NEA Quiz\NEA Quiz Database.accdb;Persist Security Info=False;")
            Try
                Call ConnectedDBLogin.Open()
            Catch ex As Exception
                label_LoginInvalid.Text = "Cannot connect to database."
            End Try

            If ConnectedDBLogin.State = ConnectionState.Open Then
                Dim SQL As New OleDb.OleDbCommand("SELECT * FROM table_Logins WHERE Username = '" & textBox_Username.Text & "'", ConnectedDBLogin)
                Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                Dim DataTable As New DataTable("Login")
                DataAdapter.Fill(DataTable)
                dataGridView_Login.DataSource = DataTable
            End If

        Catch ex As Exception
            label_LoginInvalid.Text = "No usernames found."
        End Try

        Me.dataGridView_Login.CurrentCell = Me.dataGridView_Login(1, 0)
        textBox_UsernameFromDB.Text = Me.dataGridView_Login.CurrentCell.FormattedValue

        Me.dataGridView_Login.CurrentCell = Me.dataGridView_Login(2, 0)
        textBox_PasswordFromDB.Text = Me.dataGridView_Login.CurrentCell.FormattedValue

        If textBox_Username.Text = textBox_UsernameFromDB.Text And textBox_Password.Text = textBox_PasswordFromDB.Text Then
            Me.Hide()

        Else
            label_LoginInvalid.Text = "Invalid username and/or password."
        End If

    End Sub

    Private Sub button_Register_Click(sender As Object, e As EventArgs) Handles button_Register.Click

        If textBox_Forename.Text = "" Then
            label_RegisterInvalid.Text = "Please complete all fields."
        Else
            If textBox_CreatePassword.Text = "" Then
                label_RegisterInvalid.Text = "Please complete all fields."
            Else
                If textBox_Age.Text = "" Then
                    label_RegisterInvalid.Text = "Please complete all fields."
                Else
                    If textBox_Year.Text = "" Then
                        label_RegisterInvalid.Text = "Please complete all fields."
                    Else

                        FirstCharacter = textBox_Forename.Text.Substring(0, 1)
                        SecondCharacter = textBox_Forename.Text.Substring(0, 2)
                        ThirdCharacter = textBox_Forename.Text.Substring(0, 3)

                        ThreeCharacters = FirstCharacter & SecondCharacter & ThirdCharacter

                        Username = ThreeCharacters & Age

                        Me.dataGridView_Login.CurrentCell = Me.dataGridView_Login(0, 0)
                        AutoNumber = Me.dataGridView_Login.CurrentCell.FormattedValue + 1

                        Try
                            ConnectedDBLogin = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Brad\Documents\Visual Studio 2017\Projects\NEA Quiz\NEA Quiz Database.accdb;Persist Security Info=False;")
                            Try
                                Call ConnectedDBLogin.Open()
                            Catch ex As Exception
                                label_RegisterInvalid.Text = "Cannot connect to database."
                            End Try

                            If ConnectedDBLogin.State = ConnectionState.Open Then
                                Dim SQL As New OleDb.OleDbCommand("INSERT INTO table_Logins (AutoNumber, Username, Password) VALUES (" & [AutoNumber] & ", '" & [Username] & "', '" & [Password] & "',)", ConnectedDBLogin)
                                Dim DataAdapter As New OleDb.OleDbDataAdapter(SQL)
                                Dim DataTable As New DataTable("Login")
                                DataAdapter.Fill(DataTable)
                                dataGridView_Login.DataSource = DataTable
                            End If

                        Catch ex As Exception
                            label_RegisterInvalid.Text = "Cannot insert into database."
                            MessageBox.Show(ex.ToString)
                        End Try

                    End If
                End If
            End If
        End If

    End Sub
End Class