Imports System.Data
Imports System.Data.SqlClient

Public Class CourseForm
    Private CourseTextBox(4) As TextBox        'we only query 5 columns from the Course table
    Private CourseID() As String               'create a dynamic CourseID string array

    Private Sub CourseForm_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("Ying Bai")
        ComboName.Items.Add("Satish Bhalla")
        ComboName.Items.Add("Black Anderson")
        ComboName.Items.Add("Steve Johnson")
        ComboName.Items.Add("Jenney King")
        ComboName.Items.Add("Alice Brown")
        ComboName.Items.Add("Debby Angles")
        ComboName.Items.Add("Jeff Henry")
        ComboName.SelectedIndex = 0

        ComboMethod.Items.Add("TableAdapter Method")
        ComboMethod.Items.Add("DataReader Method")
        ComboMethod.SelectedIndex = 0
    End Sub

    Private Sub cmdSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelect.Click
        Dim cmdString1 As String = "SELECT Course.course_id, Course.course FROM Course JOIN Faculty "
        Dim cmdString2 As String = "ON (Course.faculty_id LIKE Faculty.faculty_id) AND (Faculty.name LIKE @name)"
        Dim cmdString As String = cmdString1 & cmdString2
        Dim CourseTableAdapter As New SqlDataAdapter
        Dim sqlCommand As New SqlCommand
        Dim sqlDataReader As SqlDataReader
        Dim sqlDataTable As New DataTable

        sqlCommand.Connection = LogInForm.sqlConnection
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.CommandText = cmdString
        sqlCommand.Parameters.Add("@name", SqlDbType.Char).Value = ComboName.Text

        If ComboMethod.Text = "TableAdapter Method" Then
            CourseTableAdapter.SelectCommand = sqlCommand
            CourseTableAdapter.Fill(sqlDataTable)
            If sqlDataTable.Rows.Count > 0 Then
                Call FillCourseTable(sqlDataTable)
            Else
                MessageBox.Show("No matched course found!")
            End If
            sqlDataTable.Dispose()
            sqlDataTable = Nothing
            CourseTableAdapter.Dispose()
            CourseTableAdapter = Nothing
        Else
            sqlDataReader = sqlCommand.ExecuteReader
            If sqlDataReader.HasRows = True Then
                Call FillCourseReader(sqlDataReader)
            Else
                MessageBox.Show("No matched course found!")
            End If
            sqlDataReader.Close()
            sqlDataReader = Nothing
        End If
        sqlCommand.Dispose()
        sqlCommand = Nothing
        CourseList.SelectedIndex = 0
    End Sub

    Private Sub FillCourseTable(ByVal CourseTable As DataTable)
        Dim row As DataRow
        Dim pos As Integer
        ReDim CourseID(CourseTable.Rows.Count)      'redefine the CourseID array
        CourseList.Items.Clear()
        For Each row In CourseTable.Rows
            CourseList.Items.Add(row(1))            'the 2nd column is course - strCourseA
            CourseID(pos) = row(0)                  'the 1st column is course_id - strCourseA
            pos = pos + 1
        Next
    End Sub

    Private Sub FillCourseReader(ByVal CourseReader As SqlDataReader)
        Dim strCourse As String
        Dim pos As Integer
        ReDim CourseID(9)                   'redefine the CourseID string array

        CourseList.Items.Clear()
        While CourseReader.Read()
            strCourse = CourseReader.GetSqlString(1)        ' the 2nd column is course
            CourseList.Items.Add(strCourse)                             
            CourseID(pos) = Convert.ToString(CourseReader.GetSqlString(0)) ' the 1st column is course_id
            pos = pos + 1
        End While
    End Sub

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

    Private Sub CourseList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CourseList.SelectedIndexChanged
        Dim cmdString1 As String = "SELECT course_id, credit, classroom, schedule, enrollment FROM Course "
        Dim cmdString2 As String = "WHERE course_id LIKE @courseid"
        Dim cmdString As String = cmdString1 & cmdString2
        Dim CourseTableAdapter As New SqlDataAdapter
        Dim sqlCommand As New SqlCommand
        Dim sqlDataReader As SqlDataReader
        Dim sqlDataTable As New DataTable

        sqlCommand.Connection = LogInForm.sqlConnection
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.CommandText = cmdString
        sqlCommand.Parameters.Add("@courseid", SqlDbType.Char).Value = CourseID(CourseList.SelectedIndex)

        If ComboMethod.Text = "TableAdapter Method" Then
            CourseTableAdapter.SelectCommand = sqlCommand
            CourseTableAdapter.Fill(sqlDataTable)
            If sqlDataTable.Rows.Count > 0 Then
                Call FillCourseTextBox(sqlDataTable)
            Else
                MessageBox.Show("No matched course information found!")
            End If
            sqlDataTable.Dispose()
            sqlDataTable = Nothing
            CourseTableAdapter.Dispose()
            CourseTableAdapter = Nothing
        Else
            sqlDataReader = sqlCommand.ExecuteReader
            If sqlDataReader.HasRows = True Then
                Call FillCourseReaderTextBox(sqlDataReader)
            Else
                MessageBox.Show("No matched course information found!")
            End If
            sqlDataReader.Close()
            sqlDataReader = Nothing
        End If
        sqlCommand.Dispose()
        sqlCommand = Nothing
    End Sub

    Private Sub FillCourseTextBox(ByVal CourseTable As DataTable)
        Dim pos1 As Integer
        Dim row As DataRow
        Dim column As DataColumn

        For pos2 As Integer = 0 To 4                'Initialize the object array
            CourseTextBox(pos2) = New TextBox
        Next pos2
        Call MapCourseTable(CourseTextBox)
        For Each row In CourseTable.Rows
            For Each column In CourseTable.Columns
                CourseTextBox(pos1).Text = row(column)
                pos1 = pos1 + 1
            Next
        Next
    End Sub

    Private Sub FillCourseReaderTextBox(ByVal CourseReader As SqlDataReader)
        Dim intIndex As Integer

        For intIndex = 0 To 4                       'Initialize the object array
            CourseTextBox(intIndex) = New TextBox
        Next intIndex
        Call MapCourseTable(CourseTextBox)
        While CourseReader.Read()
            For intIndex = 0 To CourseReader.FieldCount - 1
                CourseTextBox(intIndex).Text = CourseReader.Item(intIndex).ToString
            Next intIndex
        End While
    End Sub

    Private Sub MapCourseTable(ByRef fCourse As Object)
        fCourse(0) = txtID           'The order must be identical
        fCourse(1) = txtCredits      'with the real order in the database
        fCourse(2) = txtClassRoom
        fCourse(3) = txtSchedule
        fCourse(4) = txtEnroll
    End Sub

End Class