Imports System.Data
Imports System.Data.SqlClient
Public Class StudentForm
    Private StudentTextBox(5) As TextBox        'We query 6 columns from the Student table

    Private Sub StudentForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If LogInForm.sqlConnection.State <> ConnectionState.Open Then
            MessageBox.Show("Database has not been opened!")
            Exit Sub
        End If
        ComboName.Items.Add("Erica Johnson")
        ComboName.Items.Add("Ashly Jade")
        ComboName.Items.Add("Holes Smith")
        ComboName.Items.Add("Andrew Woods")
        ComboName.Items.Add("Blue Valley")
        ComboName.SelectedIndex = 0
    End Sub

    Private Sub cmdSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelect.Click
        Dim strStudent As String = "dbo.StudentInfo"
        Dim strStudentCourse As String = "dbo.StudentCourseInfo"
        Dim StudentTableAdapter As New SqlDataAdapter
        Dim StudentCourseTableAdapter As New SqlDataAdapter
        Dim sqlCmdStudent, sqlCmdStudentCourse As New SqlCommand
        Dim sqlStudentTable, sqlStudentCourseTable As New DataTable
        Dim strName As String

        strName = FindName(ComboName.Text)
        If strName = "No Match" Then
            MessageBox.Show("No Matched Student Found!")
            Exit Sub
        End If
        PhotoBox.SizeMode = PictureBoxSizeMode.StretchImage
        PhotoBox.Image = System.Drawing.Image.FromFile(strName)

        Call BuildCommand(sqlCmdStudent, strStudent)
        sqlCmdStudent.Parameters.Add("@StudentName", SqlDbType.Char).Value = ComboName.Text
        StudentTableAdapter.SelectCommand = sqlCmdStudent
        StudentTableAdapter.Fill(sqlStudentTable)
        If sqlStudentTable.Rows.Count > 0 Then
            Call FillStudentTextBox(sqlStudentTable)
        Else
            MessageBox.Show("No matched student found!")
        End If
        Call BuildCommand(sqlCmdStudentCourse, strStudentCourse)
        sqlCmdStudentCourse.Parameters.Add("@StudentID", SqlDbType.Char).Value = txtID.Text
        StudentCourseTableAdapter.SelectCommand = sqlCmdStudentCourse
        StudentCourseTableAdapter.Fill(sqlStudentCourseTable)
        If sqlStudentCourseTable.Rows.Count > 0 Then
            Call FillCourseList(sqlStudentCourseTable)
        Else
            MessageBox.Show("No matched course_id found!")
        End If
        sqlStudentTable.Dispose()
        sqlStudentTable = Nothing
        sqlStudentCourseTable.Dispose()
        sqlStudentCourseTable = Nothing
        StudentTableAdapter.Dispose()
        StudentTableAdapter = Nothing
        StudentCourseTableAdapter.Dispose()
        StudentCourseTableAdapter = Nothing
        sqlCmdStudent.Dispose()
        sqlCmdStudent = Nothing
        sqlCmdStudentCourse.Dispose()
        sqlCmdStudentCourse = Nothing
    End Sub

    Private Sub BuildCommand(ByRef cmdObj As SqlCommand, ByVal cmdString As String)
        cmdObj.Connection = LogInForm.sqlConnection
        cmdObj.CommandType = CommandType.StoredProcedure
        cmdObj.CommandText = cmdString
    End Sub
    Private Sub FillCourseList(ByVal StudentCourseTable As DataTable)
        Dim row As DataRow

        CourseList.Items.Clear()
        For Each row In StudentCourseTable.Rows
            CourseList.Items.Add(row(0))        ' the 1st column is course_id - strStudentCourse
        Next
    End Sub

    Private Sub FillStudentTextBox(ByVal StudentTable As DataTable)
        Dim pos1 As Integer
        Dim row As DataRow
        Dim column As DataColumn

        For pos2 As Integer = 0 To 5                 'Initialize the textbox array
            StudentTextBox(pos2) = New TextBox
        Next pos2
        Call MapStudentTextBox(StudentTextBox)
        For Each row In StudentTable.Rows
            For Each column In StudentTable.Columns
                StudentTextBox(pos1).Text = row(column)
                pos1 = pos1 + 1
            Next
        Next
    End Sub

    Private Sub MapStudentTextBox(ByRef sTextBox As Object)
        sTextBox(0) = txtID              'The order must be identical with the
        sTextBox(1) = txtGPA             'order in the query string - strStudent
        sTextBox(2) = txtCredits
        sTextBox(3) = txtMajor
        sTextBox(4) = txtSchoolYear
        sTextBox(5) = txtEmail
    End Sub

    Private Function FindName(ByVal sName As String) As String
        Dim strName As String

        Select Case sName
            Case "Erica Johnson"
                strName = "Erica.jpg"
            Case "Ashly Jade"
                strName = "Ashly.jpg"
            Case "Holes Smith"
                strName = "Holes.jpg"
            Case Is = "Andrew Woods"
                strName = "Andrew.jpg"
            Case Is = "Blue Valley"
                strName = "Blue.jpg"
            Case Else
                strName = "No Match"
        End Select
        Return strName
    End Function

    Private Sub cmdBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBack.Click
        Me.Close()
    End Sub
End Class