Update Page

Imports System.Data.SqlClient
Public Class Update
    Inherits System.Web.UI.Page

    Private conn As SqlConnection
    Private currentProgrammeID As Integer
    Private currentCourseID As Integer
    Private currentTable As String
    Private currentMode As String

    Protected Sub UpdatePage_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        currentProgrammeID = Session("programmeId")
        currentTable = Session("table")
        currentMode = Session("mode")

        If currentTable != "Programme" Then
            currentCourseID = Session("courseId")

        End If

        conn = New SqlConnection("(INSERT CONNECTION STRING)")
        conn.Open()

        ' UpdatePage_Load is the reentry-point whenever the 'Update' button is clicked.
        ' The following will check whether if Page_Load was called because of the button click (i.e. Page.IsPostBack will return TRUE)
        If (Not Page.IsPostBack) Then
            If currentTable = "Programme" Then
                If currentMode = "Edit" Then
                    PopulateProgrammeForm()

                ElseIf currentMode = "Create" Then
                    ClearForm()
                End If

                PriceLabel.Text = "Price :"

            ElseIf currentTable = "Course" Then
                If currentMode = "Edit" Then
                    PopulateCourseForm()

                ElseIf currentMode = "Create" Then
                    ClearForm()
                End If

                PriceLabel.Text = "Day :"

            ElseIf currentTable = "Lecturer" Then
                If currentMode = "Edit" Then
                    PopulateLecturerForm()

                ElseIf currentMode = "Create" Then
                    ClearForm()
                End If

                PriceLabel.Text = "Phone No. :"
                GenderLabel.Visible = True
                MaleRadioButton.Visible = True
                FemaleRadioButton.Visible = True
                DobLabel.Visible = True
                DobTextBox.Visible = True

            End If

            FunctionLabel.Text = currentMode
            TableNameLabel.Text = currentTable

        End If
    End Sub

    Private Sub UpdatePage_Unload(sender As Object, e As EventArgs) Handles Me.Unload
        conn.Close()
    End Sub

    Protected Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
        Dim sql As String
        Dim cmd As SqlCommand

        If currentTable = "Programme" Then
            If currentMode = "Edit" Then
                sql = "UPDATE Programme SET code=@c name=@n, price=@prda WHERE id=@id"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@id", currentProgrammeID)

            Else If currentMode = "Create" Then
                sql = "INSERT INTO Programme (code, name, price) VALUES (@c, @n, @prda)"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()

            End If

        ElseIf currentTable = "Course" Then
            If currentMode = "Edit" Then
                sql = "UPDATE Course SET code=@c name=@n, day=@prda WHERE id=@id"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@id", currentCourseID)

            ElseIf currentMode = "Create" Then
                sql = "INSERT INTO Course (code, name, day) VALUES (@c, @n, @prda)"
                cmd = New SqlCommand(sql, conn)
                cmd.Parameters.Clear()

            End If

        ElseIf currentTable = "Lecturer" Then
            sql = "INSERT INTO Course (name, gender, dateOfBirth, phoneNumber) VALUES (@n, @g, @dob, @prda)"
            cmd = New SqlCommand(sql, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@g", IIf(MaleRadioButton.Checked, "M", "F"))
            cmd.Parameters.AddWithValue("@dob", IIf(DobTextBox.Text.Length > 0, DobTextBox.Text, DBNull.Value))

        End If

        cmd.Parameters.AddWithValue("@c", IIf(CodeTextBox.Text.Length >0, CodeTextBox.Text, DBNull.Value))
        cmd.Parameters.AddWithValue("@n", NameTextBox.Text)
        cmd.Parameters.AddWithValue("@prda", IIf(PriceTextBox.Text.Length >0, PriceTextBox.Text, DBNull.Value))

        cmd.ExecuteNonQuery()

        If currentTable = "Course" AndAlso currentMode = "Create" Then
            Session("table") = "Lecturer"
            Response.Redirect("Update.aspx")

        End If

        Response.Redirect("Default.aspx")

    End Sub

    Sub PopulateProgrammeForm()
        'load the required data from the database
        Dim getProgrammeSQL As String = "SELECT Programme.id, Programme.code, Programme.name, Programme.price FROM Programme WHERE Programme.id=@id"
        Dim getProgrammeCmd As SqlCommand = New SqlCommand(getProgrammeSQL, conn)
        getProgrammeCmd.Parameters.AddWithValue("@id", currentProgrammeID)
        Dim reader As SqlDataReader = getProgrammeCmd.ExecuteReader()
        Do While reader.Read()
            If reader.IsDBNull(1) Then
                CodeTextBox.Text = ""
            Else
                CodeTextBox.Text = reader.GetString(1)
            End If

            NameTextBox.Text = reader.GetString(2)

            If reader.IsDBNull(3) Then
                PriceTextBox.Text = ""
            Else
                PriceTextBox.Text = reader.GetString(3)
            End If
            Exit Do
        Loop
        reader.Close()

    End Sub

    Sub PopulateCourseForm()
        'load the required data from the database
        Dim getCourseSQL As String = "SELECT Course.id, Course.code, Course.name, Course.day FROM Course WHERE Course.id=@id"
        Dim getCourseCmd As SqlCommand = New SqlCommand(getCourseSQL, conn)
        getCourseCmd.Parameters.AddWithValue("@id", currentCourseID)
        Dim reader As SqlDataReader = getCourseCmd.ExecuteReader()
        Do While reader.Read()
            If reader.IsDBNull(1) Then
                CodeTextBox.Text = ""
            Else
                CodeTextBox.Text = reader.GetString(1)
            End If

            NameTextBox.Text = reader.GetString(2)

            If reader.IsDBNull(3) Then
                PriceTextBox.Text = ""
            Else
                PriceTextBox.Text = reader.GetString(3)
            End If
            Exit Do
        Loop
        reader.Close()

    End Sub

    Sub PopulateLecturerForm()
        'load the required data from the database
        Dim getLecturerSQL As String = "SELECT Lecturer.id, Lecturer.name, Lecturer.dateOfBirth FROM Lecturer WHERE Lecturer.id= (SELECT LecturerId FROM Programme_Setup WHERE CourseId = @id)"
        Dim getLecturerCmd As SqlCommand = New SqlCommand(getLecturerSQL, conn)
        getLecturerCmd.Parameters.AddWithValue("@id", currentCourseID)
        Dim reader As SqlDataReader = getLecturerCmd.ExecuteReader()

        NameTextBox.Text = reader.GetString(1)

        Do While reader.Read()
            If reader.GetString(2) = "M" Then
                MaleRadioButton.Checked = True
            Else
                FemaleRadioButton.Checked = True
            End If

            If reader.IsDBNull(3) Then
                DobTextBox.Text = ""
            Else
                DobTextBox.Text = reader.GetDateTime(3).ToString("yyyy-MM-dd")
            End If

            If reader.IsDBNull(4) Then
                PhoneNoTextBox.Text = ""
            Else
                PhoneNoTextBox.Text = reader.GetString(4)
            End If

            Exit Do
        Loop
        reader.Close()

    End Sub

    Sub ClearForm()
        CodeTextBox.Text = ""
        NameTextBox.Text = ""
        PriceTextBox.Text = ""
        DOBTextBox.Text = ""
        PhoneNoTextBox.Text = ""
        MaleRadioButton.Checked = False
        FemaleRadioButton.Checked = False
    End Sub

End Class