Imports System.Data
Imports System.Data.OracleClient

Partial Class Course
    Inherits System.Web.UI.Page
    Private CourseTextBox(4) As TextBox        'we only query 5 columns from the Course table
    Private CourseID() As String               'create a dynamic CourseID string array

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Application("oraConnection").State <> ConnectionState.Open Then
            Application("oraConnection").Open()
        End If
        If Not IsPostBack Then
            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")
        End If
    End Sub

    Protected Sub cmdBack_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdBack.Click
        Response.Redirect("Selection.aspx")
    End Sub

    Protected Sub cmdSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSelect.Click
        Dim cmdString As String = "SELECT Course.course_id, Course.course FROM Course, Faculty " & _
                                  "WHERE (Course.faculty_id=Faculty.faculty_id) AND (Faculty.name=:name)"
        Dim oraCommand As New OracleCommand
        Dim oraDataReader As OracleDataReader

        oraCommand.Connection = Application("oraConnection")
        oraCommand.CommandType = CommandType.Text
        oraCommand.CommandText = cmdString
        oraCommand.Parameters.Add("name", OracleType.Char).Value = ComboName.Text

        oraDataReader = oraCommand.ExecuteReader
        If oraDataReader.HasRows = True Then
            Call FillCourseReader(oraDataReader)
        Else
            Response.Write("<script>alert('No matched course found!')</script>")
        End If
        oraDataReader.Close()
        oraDataReader = Nothing
        oraCommand.Dispose()
        oraCommand = Nothing
    End Sub

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

        CourseList.Items.Clear()
        While CourseReader.Read()
            strCourse = CourseReader.GetOracleString(0)    'the 1st column is course_id
            CourseList.Items.Add(strCourse)
            CourseID(pos) = Convert.ToString(CourseReader.GetOracleString(0))
            pos = pos + 1
        End While
        Application("CourseID") = CourseID      'make the CourseID as a global array
    End Sub

    Protected Sub CourseList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CourseList.SelectedIndexChanged
        Dim cmdString As String = "SELECT course, credit, classroom, schedule, enrollment FROM Course " & _
                                  "WHERE course_id =: courseid"
        Dim oraCommand As New OracleCommand
        Dim oraDataReader As OracleDataReader
        Dim index As Integer

        index = CourseList.SelectedIndex
        oraCommand.Connection = Application("oraConnection")
        oraCommand.CommandType = CommandType.Text
        oraCommand.CommandText = cmdString
        oraCommand.Parameters.Add("courseid", OracleType.Char).Value = Application("CourseID")(index)
        oraDataReader = oraCommand.ExecuteReader
        If oraDataReader.HasRows = True Then
            Call FillCourseReaderTextBox(oraDataReader)
        Else
            Response.Write("<script>alert('No matched course information found!')</script>")
        End If
        oraDataReader.Close()
        oraDataReader = Nothing
        oraCommand.Dispose()
        oraCommand = Nothing
    End Sub

    Private Sub FillCourseReaderTextBox(ByVal CourseReader As OracleDataReader)
        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) = txtCourse             'The order must be identical
        fCourse(1) = txtCredit             'with the real order in the query string
        fCourse(2) = txtClassRoom
        fCourse(3) = txtSchedule
        fCourse(4) = txtEnrollment
    End Sub
End Class
