Option Explicit On

Imports System.Data.OleDb

Public Class Form1
    Dim DbConnect As OleDbConnection
    Dim FileName As String

    Private Sub LoadToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadToolStripMenuItem.Click
        'This is basically the same process as in the SQL Tester, however the command is specified in the code
        'This is just to fill the DGV. If you got the SQL Tester done and understood it, this should be
        'relatively straightforward
        Dim ofd As OpenFileDialog
        ofd = New OpenFileDialog
        ofd.Filter = "Database Files|*.accdb"
        ofd.ShowDialog()
        FileName = ofd.FileName
        Try
            DbConnect = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" & FileName)
        Catch ex As Exception
            MsgBox("Error: " & ex.Message)
            Application.Exit()
        End Try
        Dim DBCommand As OleDbCommand = DbConnect.CreateCommand
        DBCommand.CommandText = "SELECT * FROM Students"
        DbConnect.Open()
        Dim DBReader As OleDb.OleDbDataReader = DBCommand.ExecuteReader()
        Dim numCols As Int16 = DBReader.FieldCount - 1
        Dim strs(numCols) As String
        Dim i As Int16
        DGV.ColumnCount = numCols + 1
        For i = 0 To numCols
            strs(i) = New String("")
            DGV.Columns(i).Name = DBReader.GetName(i)
        Next
        If DBReader.HasRows Then
            While DBReader.Read()
                For i = 0 To numCols
                    strs(i) = DBReader.GetValue(i).ToString()
                Next
                DGV.Rows.Add(strs)
            End While
        End If
        DBReader.Close()
        DbConnect.Close()
        DBCommand.CommandText = "SELECT * FROM Majors"
        DbConnect.Open()
        DBReader = DBCommand.ExecuteReader()
        If DBReader.HasRows Then
            While DBReader.Read()
                cbMajor.Items.Add(DBReader.GetValue(2))
            End While
        End If
    End Sub

    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Application.Exit()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        'First we start of by establishing our connection with the databse
        Try
            DbConnect = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" & FileName)
        Catch ex As Exception
            MsgBox("Error: " & ex.Message)
            Application.Exit()
        End Try
        'Next we state the command we wish to use. In order to append to the database, we have
        'to use INSERT INTO [Table] ([value1],...) VALUES ('[value1]',...)
        'So Students (ID,...) tells it that we want to insert into student, and we should be
        'sending the 6 values we have, and then we organize Values(...) the same way we did
        'Students(...), but replacing the names with our values. It looks messy as hell in code
        Dim DBCommand As OleDbCommand = DbConnect.CreateCommand
        DBCommand.CommandText = "INSERT INTO Students (ID,First_Name,Last_Name,Major,GPA,Email)" _
                                & " VALUES ('" & txtID.Text & "','" & txtFname.Text & "','" _
                                & txtLname.Text & "','" & cbMajor.SelectedIndex + 3 & "','" _
                                & txtGPA.Text & "','" & txtFname.Text & "." & txtLname.Text _
                                & "@school.edu');"
        'Open the connection, execute the command(inserting the data into the db), and then close
        'the connection
        DbConnect.Open()
        Try
            MsgBox(DBCommand.ExecuteNonQuery())
        Catch ex As Exception
            MsgBox("Error: " & ex.Message)
        End Try
        DbConnect.Close()
        'Add the same information to the DGV that we sent to the database
        If cbMajor.SelectedIndex > -1 Then
            DGV.Rows.Add(txtID.Text, txtFname.Text, txtLname.Text, cbMajor.SelectedIndex, txtGPA.Text, _
                                 txtFname.Text(1) + txtLname.Text.ToLower + "@school.edu")
        End If
    End Sub
End Class