Imports System.Data
Imports System.Data.SqlClient
Public Class SPForm
    Private StudentTextBox(5) As TextBox        'we only query 5 columns from the Course table

    Private Sub SPForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.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 strStudentCourse As String = "dbo.StudentCourseINTO"
        Dim sqlCmdStudentCourse As New SqlCommand
        Dim sqlDataReader As SqlDataReader
        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)

        sqlCmdStudentCourse.Connection = LogInForm.sqlConnection
        sqlCmdStudentCourse.CommandType = CommandType.StoredProcedure
        sqlCmdStudentCourse.CommandText = strStudentCourse
        sqlCmdStudentCourse.Parameters.Add("@StdtName", SqlDbType.Char).Value = ComboName.Text
        sqlDataReader = sqlCmdStudentCourse.ExecuteReader
        If sqlDataReader.HasRows = True Then
            Call FillCourseReader(sqlDataReader)
        Else
            MessageBox.Show("No matched course_id found!")
        End If
        sqlDataReader.Close()
        sqlDataReader = Nothing
        sqlCmdStudentCourse.Dispose()
        sqlCmdStudentCourse = Nothing
    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 FillCourseReader(ByVal CourseReader As SqlDataReader)
        Dim strCourse As String

        CourseList.Items.Clear()
        While CourseReader.Read()
            strCourse = CourseReader.GetSqlString(0)        ' the 1st column is course
            CourseList.Items.Add(strCourse)
        End While
    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